Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009

    Unanswered: Help with Constraint : Check two Columns NOT NULL [SOLVED]

    Hi Forum,

    I am trying to create a check constraint that checks that both EntityIDPerson and EntityIDCorp are not null.
    There are three conditions that can be met as per the code below. ie.
    1) Both EntityIDPerson and EntityIDCorp have a value.
    2) EntityIDPerson has a value while EntityIDCorp is NULL.
    3) EntityIDPerson is NULL while EntityIDCorp has a value.

    However, if EntityIDPerson is NULL AND EntityIDCorp is Null then it should fail.

    The check constraint below works. However, is there a simpler, cleaner way to write the constraint?

    CREATE TABLE Contact (
    	IDContact		        INT IDENTITY(1,1)	NOT NULL,
    	EntityIDPerson		INT	NULL,
    	EntityIDCorp		INT	NULL,
    	ContactTypeID		INT	NOT NULL,	
    	CONSTRAINT pk_Contact    PRIMARY KEY (IDContact),
            CONSTRAINT ck_Contact_EntityID	CHECK 
            (EntityIDPerson IS NOT NULL AND EntityIDCorp IS NOT NULL 
            OR EntityIDPerson IS NOT NULL AND EntityIDCorp IS NULL
            OR EntityIDPerson IS NULL AND EntityIDCorp IS NOT NULL)
    Last edited by Wedgetail; 04-10-10 at 06:43.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    how about...

    CHECK ( NOT ( EntityIDPerson IS NULL AND EntityIDCorp IS NULL ) )

    by the way, IDENTITY(1,1) is not ANSI SQL | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Thanks r937. I knew there must be a simple way but couldnt think of it.

    And I know IDENTITY(1,1) is not ANSI SQL, but the actual question was ANSI SQL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts