Solved

complex check constraint

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now