Solved

complex check constraint

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 24
SQL USE DATABASE VARIABLE 5 27
First Max value 3 27
Are triggers slow? 7 10
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

839 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