TSQL Check Constraint Example

Theoretical table columns:
  1. hasSister BIT NOT NULL
  2. SisterName varchar(250) NULL
  3. SisterAge int NULL
Check constraints:

Fill up SisterName if hasSister is true
ALTER TABLE TableName WITH CHECK ADD CONSTRAINT CK_TableName_SisterName CHECK ((hasSister=1 AND SisterName IS NOT NULL) OR (hasSister=0 AND SisterName IS NULL))
GO

ALTER TABLE TableName CHECK CONSTRAINT CK_TableName_SisterName
GO
Fill up SisterAge with positive int if hasSister is true
ALTER TABLE TableName WITH CHECK ADD CONSTRAINT CK_TableName_SisterAge CHECK ((hasSister=1 AND SisterAge IS NOT NULL AND SisterAge > 0) OR (hasSister=0 AND SisterAge IS NULL))
GO

ALTER TABLE TableName CHECK CONSTRAINT CK_TableName_SisterAge
GO

No comments: