Defining a Check Constraint

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Tutorials > "ZooDB" Database > Defining Constraints >

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 different animals are entered into the Mother and Father columns of tblAnimalBirths. Since this constraint spans more than one column, it will be displayed in the Check Constraints section of the table design, and in the Constraints sub-folder of the Online Browser, respectively.

app_display-check-constraint

 

Defining the check constraint

1.In the Online Browser, right-click table tblAnimalBirths and select Design Editor | Show in new Design Editor from the context menu.
2.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.
3.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.
4.In the Expression column, double-click the "[expression required]" entry, enter "Mother <> Father", and press Enter.
5.In the Database Structure Change Script window, click the Execute Change Script ic_execute-sql 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. If both columns contain the same value in a row, DatabaseSpy displays an error in the Message tab.

app_output-check-constraint-failed


© 2019 Altova GmbH