Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    1

    Unanswered: Check Constrain Based on Another Column

    Hi i have Table A - Column A,Column B

    Column B can not be a null value if Coulmn A = "xxx", how can i do this.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    A Constraint..I'll have to test tomorrow

    But

    (ColB IS NOT NULL AND ColA <> 'XXX')

    You could write a trigger if I can't get this to work

    Look up Constraints, or Create Table in Books Online

    Do you know what that is?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should work.
    Code:
    CREATE TABLE DaTable(
      ...
      ColumnA   CHAR(20),
      ColumnB   INT,
      ...
      CONSTRAINT MyConstraintName CHECK (ColumnA <> 'xxx' OR 
                                      (ColumnA = 'xxx' AND ColumnB IS NOT NULL)),
      ...
    )
    For constrains within the same record I prefer CHECK CONSTRAINTs over triggers.
    With a CHECK CONSTRAINT, the rule is enforced on all records, present or future.
    With a trigger, you have to "touch" (UPDATE) all present records or they won't get validated.
    Last edited by Wim; 05-04-12 at 07:56.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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