Post reply

Check constraint

  • May 1, 2007 at 7:36 am

    #68619

    How do I create a check constraint or restriction on an existing table with data.

    I have a table testDate (Col1 DATETIME, Col2 DATETIME), I want to insert only the data where Col1 is greater than Col2.

    insert into testDate (Col1, Col2) values ('01/01/2000', '12/31/1999') -- Fine.

    insert into testDate (Col1, Col2) values ('01/01/2000', '01/02/2000') -- Should fail

    Thanks.

  • May 1, 2007 at 9:54 am

    #703582

    alter table testdate with check

    add constraint datecheck check (col1 > col2)

    Greg

    Greg

  • May 1, 2007 at 10:21 am

    #703595

    Got the following error,

    Server: Msg 547, Level 16, State 1, Line 1

    ALTER TABLE statement conflicted with TABLE CHECK constraint 'datacheck'.

  • May 1, 2007 at 10:31 am

    #703601

    I forgot to ask if there is already data in the table that violates the check constraint.  If there is, you'll either need to clean it up before adding the constraint or add the constraint with 'nocheck'.  In the latter case, the constraint will be created and will only check data added to the table after the constraint is enabled.

    Greg

    Greg

  • May 2, 2007 at 8:57 am

    #703837

    Is there a corresponding way to do this same example using SSMS?

     


    Student of SQL and Golf, Master of Neither

  • May 2, 2007 at 9:53 am

    #703866

    Yep.  Expand the table in Object Explorer, right-click Constraints, choose New Constraint.  Fill in the expression, designate the column, etc., save the constraint.

    Greg

    Greg

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply