Rule Vs. CHECK

Question

Post reply

Rule Vs. CHECK

Vijai-703291

SSC Eights!

Points: 959
More actions
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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Vijai-703291 SSC Eights! Points: 959 More actions · Answer 2

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.

dragos_sv Default port Points: 1478 More actions · Answer 3

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));

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

Vijai-703291 SSC Eights! Points: 959 More actions · Answer 5

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.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

You could use a UDF.

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