Solved

complex check constraint

Posted on 2004-08-02
4
503 Views
Last Modified: 2008-03-17
i have a table with several columns.  there is a pair of columns in the table for which i want to define a check constraint.  i want this constraint to force that only one of the two columns can be not null in one record, or they can both be null.  so, the following situations would be allowed:

col1      col2
====      ====
NULL      NULL
VALUE      NULL
NULL      VALUE

and this situation wouldn't:

col1      col2
====      ====
VALUE      VALUE

is there a way to create this restraint?  if so, how?

Thanks,

~ Justin
0
Comment
Question by:newstandard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 2

Assisted Solution

by:nexusSam
nexusSam earned 25 total points
ID: 11695404
Justin, you can write an Insert/Update trigger, but what are you trying to do if you don't mind me asking? There may be an easier way..


Sam
0
 

Author Comment

by:newstandard
ID: 11695497
it is pretty involved for me to explain what it is for, but here goes.  there is a table in the database of states, where the states for US and Canada are standardized to avoid typos and stuff.  a contact address table has a stateID column that is an FK to this state table.  however, if the contact is outside of the US or Canada, they still may have a state or province.  for this reason a state column is provided in the same contact address table which is a VARCHAR, to allow for a non-US, non-Canada value.  but i definitely don't want both to have a value at the same time.

i have since thought about making a User-defined Function called AssertOneValue(val1,val2) which returns true if 0 or 1 of the values is not null, or returns false if both are a value

then, i can just do

CONSTRAINT myConstraint CHECK AssertOneValue(stateID,state)

in the CREATE TABLE statement for the contactAddress table

how would i do it with an INSERT/UPDATE trigger?

~ Justin
0
 
LVL 5

Accepted Solution

by:
hkamal earned 100 total points
ID: 11695903
By way of example, here is an INSERT trigger that polices a customer table:

CREATE TABLE Customers (VerifiedState CHAR(2) NULL, FreeTextState VARCHAR(30) NULL)
GO
                 
CREATE TRIGGER cust_state_insert
ON Customers
FOR INSERT
AS
UPDATE Customers SET FreeTextState=NULL
WHERE VerifiedState IS NOT NULL AND FreeTextState IS NOT NULL
GO

INSERT Customers SELECT "CA", "California" UNION
                 SELECT "NY",  "New York" UNION
                 SELECT "NC",  "North Carolina" UNION
                 SELECT "MA",  NULL UNION
                 SELECT "WA",  "Washington" UNION
                 SELECT "VA",  NULL UNION
                 SELECT NULL,  "Georgia" UNION
                 SELECT NULL,  "Florida"

SELECT * FROM Customers

You may also be able to do this in the user-interface. I know PowerBuilder/C++/Java allows you to hide or display entry boxes based on other values (so entering US or CA in Country box hides the free-text State box).

HTH
0
 

Author Comment

by:newstandard
ID: 11695936
figured it out:

CONSTRAINT check_mailingAddress_oneStateVal CHECK (NOT ((stateID IS NOT NULL) AND (state IS NOT NULL)))

but thanks for the help guys, here's some points :-)
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question