Constraints

Primary Key Constraints

A primary key is a type of index that will most likely be used as the primary index when a query is made on the table (assuming of course, that one of the conditional fields in the query pertains to the column on which the index exists).

1. A primary key constraint is an object under a table, so to create a primary key constraint, first expand the database then the table you wish to create the primary key constraint under (click the + beside the database and table names).

2. Right-click on Constraints and select ADD TABLE CONSTRAINT -> PRIMARY KEY.

3. The first field is the name for the constraint. For this example, type the name: customer_id_pkey

4. In the Available Columns list, select customer_id and click Add to move that column to the PRIMARY KEY Columns.

   

   Figure 4-16. The Add PRIMARY KEY Dialog

5. Click OK to create the primary key constraint.

In order to create a primary key constraint on a table, the column selected must be NOT NULL.

Unique Constraints

Unique constraints may be placed on multiple columns. They constrain the UPDATE/INSERTS on the table so that the values being updated or inserted do not match any other row in the table for the corresponding values.

1. A unique constraint is an object under a table, so to create a unique constraint, first expand the database then the table you wish to create the unique constraint under (click the + beside the database and table names).

2. Right-click on Constraints and select ADD TABLE CONSTRAINT -> UNIQUE.

3. The first field is the name for the constraint. For this example, type the name: invoice_number_ukey

4. In the Available Columns list, select invoice_number and click Add to move that column to the UNIQUE Columns listbox.

   

   Figure 4-17. The Add UNIQUE Constraint Dialog

5. Click OK to create the unique constraint.

Check Constraints

A check constraint prevents updates/inserts on the table by placing a check condition on the selected column. The UPDATE/INSERT is allowed only if the check condition qualifies.

1. A check constraint is an object under a table, so to create a check constraint, first expand the database then the table you wish to create the check constraint under (click the + beside the database and table names).

2. Right-click on Constraints and select ADD TABLE CONSTRAINT -> CHECK.

3. The first field is the name for the constraint. For this example, type the name: invoice_number_check

4. Specify the CHECK Condition that is checked each time an UPDATE/INSERT is made into the table. For this example, set the check condition to be: invoice_number >= 0

   

   Figure 4-18. The Add CHECK Constraints Dialog

5. Click OK to create the check constraint.

Foreign Key (FK) Constraints

A foreign key constraint allows certain fields in one table to refer to fields in another table. This type of constraint is useful if you have two tables, one of which has partial information, details on which can be sought from another table with a matching entry. A foreign key constraint in this case will prevent the deletion of an entry from the table with detailed information if there is an entry in the table with partial information that matches it.

1. A foreign key constraint is an object under a table, so to create a foreign key constraint, first expand the database then the table you wish to create the foreign key constraint under (click the + beside the database and table names).

2. Right-click on Constraints and select ADD TABLE CONSTRAINT -> FOREIGN KEY.

3. The first field is the name for the constraint. For this example, type the name: invoice_customer_fkey

   The dependent column exists in the table customers. Select that table name from the Table drop-down list.

4. Select the dependent columns (from the left) then select the reference columns (from the right), and click Add.

   For this example, the dependent column is customer_id in table invoices, and the column it refers to is customer_id in the customers table.

   If dependent and reference columns have been selected, the number of columns being selected as dependent must be equal to the number of reference columns. Selection of columns on the right can be optional, but only if the dependent columns have the same name as that of the columns they will be referring to on the right, and if the columns on the right make up the primary key for the parent table.

5. Optionally, you can select other foreign key constraint characteristics from the drop down boxes at the bottom of the window.

   

   Figure 4-19. The Add FOREIGN KEY Constraint Dialog

6. Click OK to create the foreign key constraint.

Note that the constraints have been added under the Constraints node in the Tree View.

RHDB Administrator also enables you to perform the following operations:

Refer to the Red Hat Database SQL Guide and Reference for more information on constraints.