Altova DatabaseSpy 2024 Professional Edition

As with other database objects, there are several ways to create check constraints:

 

From the Online Browser

From the Design Editor

By generating SQL from an existing check constraint object

 

Adding check constraints is not supported for the following databases: MariaDB, MySQL, SQLite.

 

When adding a check constraint, make sure that the following prerequisites are met:

 

The check constraint must have a valid expression in the SQL grammar of the currently connected database. For example, if column names are used in the expression, they must exist in the table.

When adding the check constraint to a column that already has data, the column's data must satisfy the expression of the check constraint. Otherwise, the structure change script fails with an error (for example, The ALTER TABLE statement conflicted with the CHECK constraint).

 

While a check constraint is created but not committed to the database yet, its name may appear with the label <generated> in Online Browser and Design Editor. This means that the name will be automatically generated by the database when you commit the change script. For details about support for generated names across various databases, see Generated Names.

 

Adding check constraints from the Online Browser

The following instructions for adding a check constraint use a SQL Server database as model. The instructions are similar in other database kinds that support check constraints.

 

1.Right-click an existing column or table and select Create Check Constraint from the context menu. The new constraint appears in the "Check Constraints" folder of the table, and a structure change script is generated in the Database Structure Change Script Window.

dbs_check_constraint_01

2.Optionally, select the <generated> name and press F2 to rename it. For details about support for generated names across various databases, see Generated Names.

3.Making sure that the check constraint is selected in the Online Browser, edit the Expression property in the Properties window. Optionally, edit the Description property. In this example specific to a SQL Server database, the expression ensures that the column value is always greater than  or equal to 0.

dbs_check_constraint_02

4.Click the Execute Change Script dbs_ic_execute_script button in the Database Structure Change Script window.

 

You may need to Refresh dbs_ic_refresh the data source in order to view the latest changes in the Online Browser.

 

Adding check constraints from the Design Editor

To define a check constraint in the Design Editor:

 

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 ic_add-column icon in the Check Constraint column.

 

A new check constraint is added to the "Check Constraints" section and highlighted for editing.

 

2.In the "Expression" column, enter the expression that is to be checked when data is committed to the database and press Enter.

dbs_check_constraint_03

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.

 

3.Click the Execute Change Script dbs_ic_execute_script button in the Database Structure Change Script window.

 

Adding check constraints using SQL

If check constraints already exist in a table, you can add new constraints by generating a statement that uses the existing check constraint as template, as follows:

 

1.Right-click an existing constraint in the "Constraints" folder.

2.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 context menu. A Data Definition Language (DDL) statement is generated in the SQL Editor window.

3.Edit the generated statement, for example:

 

a)The ALTER TABLE part of the statement must point to the correct table;

b)The ADD CONSTRAINT part of the statement must specify a valid constraint name;

c)The CHECK (Expression) section must contain a valid constraint expression in the SQL grammar of the currently connected database kind.

 

4.Click the Execute dbs_ic_execute_query button to execute the statement.

© 2018-2024 Altova GmbH