Post reply

Rule Vs. CHECK

  • April 7, 2008 at 11:06 pm

    #379696

    As you know we can apply a single RULE to multiple columns which have the same condition to check in SQL 2005. But it in the SQL online help it is advised not use RULE in the new project as the new SQL server release doesn't support this.

    Hence is it possible to apply a single CHECK CONSTRAINT to multiple columns? Please share your thoughts.

  • April 7, 2008 at 11:57 pm

    #799545

    Sure.

    CREATE TABLE TestingDates (

    ID INT IDENTITY PRIMARY KEY,

    Starts DATETIME,

    Ends DATETIME

    )

    ALTER TABLE TestingDates ADD CONSTRAINT ck_DateCheck CHECK (Starts <= Ends)

    INSERT INTO TestingDates (Starts, Ends)

    VALUES (GETDATE(), GETDATE()+1) -- succeeds

    INSERT INTO TestingDates (Starts, Ends)

    VALUES (GETDATE(), GETDATE()-1) -- fails

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 8, 2008 at 12:42 am

    #799554

    Sorry I will give explain further.

    I have more than 8 eight columns all should have only value IN(0,1,2,3,4,5). I dont want include seperate CHECK CONSTRAINT for each columns as the check is same. Is it possible to include single CHECK CONSTRAINT

    ALTER TABLE CheckTbl

    ADD CONSTRAINT chkRowCount CHECK (col1, col2, col3.. col8 IN(0,1,2,3,4,5));

    Is the above code is possible?

    Using RULE it is possible to bind single rule to multiple columns. Shall I use RULE itself.

  • April 8, 2008 at 1:10 am

    #799556

    i guess you can write something like

    ADD CONSTRAINT chkRowCount CHECK (col1 IN(0,1,2,3,4,5) and col2 IN(0,1,2,3,4,5) and ... and col8 IN(0,1,2,3,4,5));

  • April 8, 2008 at 1:14 am

    #799557

    Might be possible, but you would have to express it as a logical expression

    (Col1 between 0 and 5 and Col2 between 0 and 5 and Col3 between ...)

    Rather don't use Bind Rule. It is deprecated in SQL 2005.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 8, 2008 at 3:02 am

    #799580

    ALTER table newtab add constraint chk CHECK (col1 IN('0','1','2','3') and col2 IN('0','1','2','3') .... )

    The above code works. But in future if i want to change this, again i have to write the entire code for all columns.

    In case of RULE I will update the RULE and again bind it with all columns in more than one table. So the consistency and data integrity is maintained. Is there any alternative method to apply same RULE facility in SQL 2005.

  • April 8, 2008 at 6:28 am

    #799676

    You could use a UDF.

    Then you can modify the function if you need to make a change.

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

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