Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

complex check constraint

Posted on 2004-08-02
4
Medium Priority
?
509 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 100 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 400 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

721 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