October 31, 2006 at 9:19 am
I have a table that has 3 values
Sitefk int
ContentGroupID int default(0)
ContentTypeFk int default(0)
I am trying to write a table wide check constraint that does the following.
There must be a value that is > 0 in either ContentGroupID or ContentTypeFk but not both at the same time.
Also both ContentGroupID and ContentTypeFk cant be 0 at the same time.
So
ContentGroupID = 7, ContentTypeFk = 0 OK
ContentGroupID = 0, ContentTypeFk = 7 OK
ContentGroupID = 7, ContentTypeFk = 7 NOT ALLOWED
ContentGroupID = 0, ContentTypeFk = 0 NOT ALLOWED
Everyone I try when entering test data in Enterprise manager, it pops up the check constaint error warning but then when I reset the values to those that should be ok it won't let me update, saying the rows have been changed.
Can anyone give me a hand. Any help would be much appreciated. Thanks
October 31, 2006 at 9:32 am
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE XType = 'U' AND Name = 'tblDemo')
DROP TABLE dbo.tblDemo
GO
CREATE TABLE dbo.tblDemo
(
Sitefk INT NOT NULL
, ContentGroupID INT NOT NULL CONSTRAINT DF_tblDemo_ContentGroupID DEFAULT(0)
, ContentTypeFk INT NOT NULL CONSTRAINT DF_tblDemo_ContentTypeFk DEFAULT(0)
, CONSTRAINT CK_tblDemo_ContentGroupID_ContentTypeFk_EitherIsSet CHECK ((ContentTypeFk > 0 OR ContentGroupID > 0) AND ContentTypeFk * ContentGroupID = 0)
)
INSERT INTO dbo.tblDemo (SiteFK, ContentGroupID, ContentTypeFk) VALUES (1, 0, 7)
--OK
INSERT INTO dbo.tblDemo (SiteFK, ContentGroupID, ContentTypeFk) VALUES (1, 7, 0)
--OK
INSERT INTO dbo.tblDemo (SiteFK, ContentGroupID, ContentTypeFk) VALUES (1, 7, 7)
--FAILS
DROP TABLE dbo.tblDemo
October 31, 2006 at 9:34 am
On a side note. The advantage of using a trigger for such validation is that you can return a custom error message. Something you couldn't do with the check constraint I posted. However both would do the job just fine.
November 1, 2006 at 9:51 am
CHECK ((ContentTypeFk > 0 OR ContentGroupID > 0) AND ContentTypeFk * ContentGroupID = 0)
I like the multiplies to 0 check, very clever. You could also write the check constraint like this:
CHECK (ContentTypeFk + ContentGroupID > 0 AND ContentTypeFk * ContentGroupID = 0)
November 1, 2006 at 10:17 am
Thanx for the appreciation. I had also considered you version, but I didn't like the fact that I couldn't reverse engineer the constraint to understand the business rule. And since I can't put a comment in a check constraint (AFAIK), I decided to make it just a little easier to understand.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply