Table constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table constraint

Author

Topic

shawnafisher
Starting Member

5 Posts

Posted - 2009-04-24 : 17:50:59

This is boggeling my mind. I am trying to add a check constraint to an existing table using:


Alter table [tMyTable] with nocheck
Add constraint CK_DataDuplicates
CHECK (dbo.fn_CheckForDuplicates_Data(Field1,Field2,Field3) != 1)

The function returns either a 0 if it is a dup, and 1 if it is not. I need the constraint to complain if the function returns a 0. The problem is I run the function with values I am trying to insert into tMyTable, and it is working correctly, but the constraint is allowing the records to be inserted, and i don't know why...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-24 : 19:32:19

Acording to BOL: CHECK constraints reject values that evaluate to FALSE.

Your function is returning 0 for a duplicate and 0 != 1 is TRUE. Thus duplicates are getting in. Try changing to:

CHECK (dbo.fn_CheckForDuplicates_Data(Field1,Field2,Field3) != 0)
-- Or
CHECK (dbo.fn_CheckForDuplicates_Data(Field1,Field2,Field3) = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources