Check constraints can be defined both on table and on column level and make sure that only valid data is entered into a specific column of a table. If the condition is not met, the respective column cannot be updated and new rows cannot be added (see screenshot below). DatabaseSpy outputs a corresponding message in the Message tab.
|Note:||Adding check constraints is not supported for the following databases: MariaDB, MySQL, SQLite.|
1.Do one of the following:
•Right-click into the table and select Insert new | Check Constraint from the context menu.
•Expand the Check Constraints section by clicking the triangle at the right edge of the section header and click the Create new Check Constraint icon in the Check Constraint column.
A new check constraint is added to the "Check Constraints" section and highlighted for editing. By default, the name of the foreign key is <generated>, which means its name will be automatically generated by the database. This ensures uniqueness of the name in the database. If necessary, you can override the generated name. To do this, enter a descriptive name in place of <generated>, and press Enter.
If you override the generated name with a custom name, the new name must be unique; otherwise, errors will occur when the database change script is executed.
2.In the "Expression" column, enter the expression that is to be checked when data is committed to the database and press Enter.
As always when making changes to the database structure, a change script is generated which must be executed in order to actually create the check constraint in the database. DatabaseSpy performs a validation of the expression that will be used for the check constraint. While you are defining a check constraint in the Design Editor, the Database Structure Change Script window displays comments if the expression is invalid.
The following issues are validated and corresponding messages are displayed in the change script:
•Textual or numeric constant not matching column data type
•Column data types not matching
•"IS NOT NULL" clause applied to a nullable column
Viewing check constraints
In the Design Editor, check constraints on table level are displayed in a separate section where they can be edited and where you can add additional or delete unneeded check constraints.
Columns that have a check constraint assigned are marked with the check constraint icon in the Design Editor and the Online Browser. Check constraints on column level can be edited in the Properties window of the column to which they are applied. Please note that they do not show up in the Check Constraints section of the table. To see the name and definition of this column's constraint, you have to display the column properties in the Properties window.
In the Online Browser, check constraints appear in the constraints sub-folder of the table or column, depending on whether the constraint has been defined on table or column level.
Check constraints on table level appear in the Constraints folder of the table they are assigned to.
Check constraints on column level are displayed under the Constraints folder of the column. Note that no Constraints folder on table level is available in this case.
Modifying check constraints
You can rename, delete or change the expression of a check constraint. Note that the check constraint is always deleted first and then recreated with the new definition. The respective SQL statement is generated automatically in the Database Structure Change Script window if you modify the check definition in the Design Editor.
When you rename a column that is used in a check constraint expression, DatabaseSpy automatically updates the check constraint in the change script that is used to rename that column.
If a column is deleted, any check constraints that reference this column are dropped as well.
Adding check constraints using SQL
If check constraints already exist in a table, you can add new constraints to the table also by right-clicking an existing check constraint and generating a statement that uses the properties of the existing item as default. After editing the properties as required and executing the SQL, a new check constraint is added to the database.
To add a check constraint to a table using SQL:
1.Connect to the database and show the respective data source in the Online Browser.
2.Expand the table to which you want to add a check constraint and right-click an existing constraint in the Constraints folder.
3.Select Show in new SQL Editor | Add from the context menu or, holding down the right mouse button, drag the constraint into an open SQL Editor window and choose Add from the popup. An ALTER TABLE statement is generated in the SQL Editor window.
4.Edit the statement as follows:
a)Enter the path of the table that is to be checked after the ALTER TABLE part of the statement.
b)Type the name of the new constraint after the ADD CONSTRAINT part of the statement.
c)Edit the CHECK (Expression) section; enter the expression that is to be checked when the table is updated.
5. Click the Execute button to execute the statement and to add the new check constraint to the database.
6.In the Online Browser, right-click the database and choose Refresh from the context menu.