Check Constraints

[Previous] [Next]

The database has a check constraint that makes sure that the sex of every horse inserted or updated is valid. Let's try to update Lambada Hot, setting its sex to the invalid value 'p':

 UPDATE Horses SET Sex = 'p' WHERE HorseId = 525 

Naturally, SQL Server doesn't oblige. Instead it returns the following message:

 Server: Msg 547, Level 16, State 1, Line 1 UPDATE statement conflicted with TABLE CHECK constraint  'CK_Horses_Sex'. The conflict occurred in database  'MSPress_ADBRacing', table 'Horses'. The statement has been terminated. 

Interestingly, SQL Server returns an error message different from the ones returned in the previous examples, but the message number is the same for this check constraint error as for the previous foreign key constraint errors. So when your data-modifying components check for message 547 from SQL Server, SQL Server captures check constraint violations as well as foreign key constraint violations.