Solved

complex check constraint

Posted on 2004-08-02
4
502 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

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 …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

679 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