CODEWITHSUNDEEP
sqlsql-servert-sql
War
War

Reputation: 8628

Complex SQL constraint that relies on a FK

Basically the logic I want in my constraint goes something like this ... 

IF([AssetTypeId] = 1) 
THEN
    [FileId] IS NOT NULL AND [Url] IS NULL 
END

IF([AssetTypeId] = 0) 
THEN
   [FileId] IS NULL AND [Url] IS NOT NULL)
END

AssetTypeId is a FK reference / constraint on the current table. The error I get suggests syntax error no mater how I word this with one exception, when I do this ...

([AssetTypeId] = 1) AND [FileId] IS NOT NULL AND [Url] IS NULL 
OR
([AssetTypeId] = 0)  AND [FileId] IS NULL AND [Url] IS NOT NULL

It gives me this error:

'Asset (dbo)' table
- Unable to add constraint 'CK_Asset_FileIdOrUrlRequiredNotBoth'.  
The ALTER TABLE statement conflicted with the CHECK constraint "CK_Asset_FileIdOrUrlRequiredNotBoth". The conflict occurred in database "MyDb", table

"dbo.Asset".

I can't seem to figure out why SQL wont let me do this. Any ideas guys?

Upvotes: 0

Views: 96

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

When the message, during creation of a constraint, indicates that that same constraint is being violated:

'Asset (dbo)' table
- Unable to add constraint 'CK_Asset_FileIdOrUrlRequiredNotBoth'.  
The ALTER TABLE statement conflicted with the CHECK constraint "CK_Asset_FileIdOrUrlRequiredNotBoth". The conflict occurred in database "MyDb", table

It means that there is existing data in that table that doesn't conform to your desired constraint. The constraint is syntactically valid, or it wouldn't have gotten this far.

You need to find the bad data and correct it before you can implement your constraint.

E.g.

SELECT * from Asset where
([AssetTypeId] = 1) AND ([FileId] IS NULL OR [Url] IS NOT NULL )
OR
([AssetTypeId] = 0)  AND ([FileId] IS NOT NULL OR [Url] IS NULL)

Should find the bad data

Upvotes: 2

Related Questions