11.3 Creating Constraints (Revision #51)

Aqua Data Studio provides visual editors for creating database table Constraint objects. Constraints can be visually edited through the Create Table and Alter Table property windows, launched from a right click in the Schema Browser. Within the Alter and Create Table property windows, the Constraints Tab allows selection and editing of Table Constraints for Primary Key Constraints, Foreign Key Constraints, Unique Constraints and Check Constraints. After constraints have been set in the Constraints Tab, the Preview SQL tab displays the DDL used to generate them. When ER Models are generated from databases in the Schema Browser, constraints are visible within table entity objects.

Primary Key Constraints

A Primary Key is a candidate key to uniquely identify each row in a table. A table is allowed one Primary Key constraint, and a column that is part of the Primary Key constraint cannot take null values. Because Primary Key constraints ensure unique data, they are most often defined on an identity column. To create a Primary Key Constraint, enter a Primary Key Name in the name column of the Constraints table, then select Primary Key from the Type Column dropdown. In the Constraint Definition pane, select the table columns from the table that are part of the Primary key. A Primary Key Constraint is represented by a table icon with gold node when viewed in the Constraints container in the Schema Browser.

Foreign Key Constraints

A Foreign Key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. To create a Foreign Key Constraint, enter a Foreign Key Name in the name column of the Constraints table, then select Foreign Key from the Type Column dropdown. Select the Reference Schema and Reference Table. In the Constraint Definition pane, select the columns from the tables that are set in the Reference Table of the Foreign Key. A Foreign Key Constraint is represented by a table icon with gold node and black square when viewed in the Constraints container in the Schema Browser.

Unique Constraints

A Unique Constraint uniquely identifies all possible rows that exist in a table. Unique Constraints make sure that no identically copied values are entered in specific columns that do not participate in a primary key. Multiple Unique Constraints can exist on a single table. To create a Unique Constraint, enter a Unique Constraint name in the name column of the Constraints table, then select Unique Constraint from the Type Column dropdown. In the Constraint Definition pane, select the columns from the columns needed. A Unique Key Constraint is represented by a table icon with crossed node when viewed in the Constraints container in the Schema Browser.

Check Constraints

A Check Constraint (also known as table check constraint) is a condition that defines valid data when adding or updating an entry in a table of a relational database. Within the Constraints Tab of Create or Alter Table, the Check Condition column is where the condition is set, determining what the table considers valid data for the selected column. To create a Check Constraint, enter a Check Constraint Name in the name column of the Constraints table, then select Check Constraint from the Type Column dropdown. Enter the formula for the check in the Check Condition field. A Check Constraint is represented by a table icon with gold node and green check when viewed in the Constraints container in the Schema Browser.

Sample Table with Constraint Types

The screenshot labeled "Table with constraint types" shows the Alter Table dialog for a table from the AdventureWorks database in Microsoft SQL Server 2008.