Defining a Check Constraint
When a check constraint is defined, data will be validated according to the expression specified in the check constraint, before it is added to a table. Data that fails the validation will not be added.
In this section of the tutorial, you will create a check constraint that makes sure that the Mother and Father columns of tblAnimalBirths are not the same.
To define the check constraint:
1.Open the "ZooDB" project created previously and double-click the 
ZooDBConnect data source to connect to it.
2.In the Online Browser, right-click table tblAnimalBirths and select Design Editor | Show in new Design Editor from the context menu.
3.Right-click the table design and select Insert new | Check Constraint from the context menu. A check constraint is created in the design, having the name <generated>, and the entry is selected for editing.
4.Optionally, change the constraint name from <generated> to "CK_CheckParents" and press Enter. If you leave the name as <generated>, the key name will be automatically generated by the database.
5.In the Expression column, double-click the "[expression required]" entry, enter "Mother <> Father", and press Enter.
6.In the Database Structure Change Script window, click the Execute Change Script 
button to execute the change script and to commit the new check constraint to the database.
Testing the check constraint
After the check constraint has been committed to the database, you cannot enter the same animal ID into the "Mother" and "Father" columns anymore. For example, a SQL statement like the one below will result in an error because it attempts to add a record where the "Mother" and "Father" values are the same:
INSERT INTO [ZooDB].[dbo].[tblAnimalBirths] ([BirthDate], [Mother], [Father], [Veterinarian], [NumberInLitter]) VALUES ('2019-04-24 00:00:00', 5055, 5055, 4, 3) ; |