3.7. Constraints

3.7.1. Primary Key Constraints

A primary key constraint requires specific columns to be unique (not duplicated within the table) and not null. Also, there cannot be more than one primary key constraint defined for a table, so the values of the primary key columns uniquely identify a table row. When you create a primary key constraint, an index on the primary key columns is created as a side effect. The index supports fast retrieval when a query selects a row by primary key.

1. A primary key constraint is an object under a table, so to create a primary key constraint, first expand the web_store database (click the [O-] beside the database name), then web_store_schema, then the invoices table.

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 3-27. The ADD PRIMARY KEY Dialog

5. Click OK to create the primary key constraint.

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

3.7.2. Unique Constraints

Unique constraints can be placed on multiple columns. They constrain the INSERTS and UPDATES on a 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 web_store database (click the [O-] beside the database name), then web_store_schema, then the invoices table.

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 3-28. The ADD UNIQUE CONSTRAINT Dialog

5. Click OK to create the unique constraint.

3.7.3. 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 web_store database (click the [O-] beside the database name), then web_store_schema, then the invoices table.

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 3-29. The ADD CHECK CONSTRAINTS Dialog

5. Click OK to create the check constraint.

3.7.4. Foreign Key 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 prevents 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.

The example that follows shows how to create a foreign key constraint.

1. A foreign key constraint is an object under a table, so to create a foreign key constraint, first expand the web_store database (click the [O-] beside the database name), then web_store_schema, then the invoices table.

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

4. Because customers is located in a schema that is not in the default search path, first select web_store_schema from the left drop-down list for Table, then select the customers table from the right drop-down list.

5. 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.

   Note

   If both dependent and reference columns have been selected, the number of dependent columns must be equal to the number of reference columns. Usually, you must specify reference columns, but they can be omitted if the following two conditions are both true: The number of dependent columns that are selected is the same as the number of columns that make up the primary key of the referenced table. The dependent columns have the same types as the columns that make up the primary key of the reference table. If these conditions are true, the columns of the primary key in the referenced table will be automatically considered as the reference columns.

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

   

   Figure 3-30. The ADD FOREIGN KEY CONSTRAINTS Dialog

7. Click OK to create the foreign key constraint.

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

Administrator also enables you to perform the following operations on existing constraints:

To learn more about constraints, refer to the PostgreSQL 7.3 Reference Manual.