Keys

To use RI, you must define keys. There are two types of keys: primary and foreign.

A primary key is a column or group of columns whose value uniquely identifies each row in a table. Because the key value is always unique, you can use it to detect and prevent duplicate rows.

A foreign key is a column or set of columns that is common to the dependent and parent tables in a table relationship. The parent table must have a matching column or set of columns that is defined as the primary key. Foreign keys reference primary keys in a parent table. It is this relationship of a column in one table to a column in another table that provides the transactional database engine with its ability to enforce referential constraints.

Primary Keys

A good primary key has these characteristics:

•

It is mandatory; it must store non-null values.

•

It is unique. For example, the ID column in a Student or Faculty table is a good key because it uniquely identifies each individual. It is less practical to use a person’s name because more than one person might have the same name. Also, databases do not detect variations in names as duplicates (for example, Andy for Andrew or Jen for Jennifer).

•

It is stable. The ID of a student is a good key not only because it uniquely identifies each individual, but it is also unlikely to change, while a person’s name might change.

•

It is short; it has few characters. Smaller columns occupy less storage space, database searches are faster, and entries are less prone to mistakes. For example, an ID column of 9 digits is easier to access than a name column of 30 characters.

Creating Primary Keys

You create a referential constraint by creating a foreign key on a table. However, before creating the foreign key, you must create a primary key on the parent table to which the foreign key refers.

A table can have only one primary key. You can create a primary key using either of the following:

•

A PRIMARY KEY clause in a CREATE TABLE statement.

•

An ADD PRIMARY KEY clause in an ALTER TABLE statement.

The following example creates the primary key ID on the Person table in the sample database:

ALTER TABLE Person
ADD PRIMARY KEY (ID);

When creating a primary key, remember that Pervasive PSQL implements the primary key on the table using a unique, non-null, non-modifiable index. If one does not exist for the specified columns, then Pervasive PSQL adds a non-named index with these attributes containing the columns specified in the primary key definition.

Dropping Primary Keys

You can delete a primary key only after you have dropped all foreign keys that depend on it. To drop a primary key from a table, use a DROP PRIMARY KEY clause in an ALTER TABLE statement. Since a table can have only one primary key, you do not have to specify the column name when you drop the primary key, as the following example illustrates:

ALTER TABLE Person
DROP PRIMARY KEY;

Changing Primary Keys

To change a table’s primary key, follow these steps:

Drop the existing primary key using a DROP PRIMARY KEY clause in an ALTER TABLE statement.

Note: Doing so does not remove the column, or the index used by the primary key; it only removes the primary key definition. To remove the primary key, there must be no foreign key referencing the primary key.

Create a new primary key using an ADD PRIMARY KEY clause in an ALTER TABLE statement.

Foreign Keys

A foreign key is a column or set of columns that is common to the dependent and parent tables in a table relationship. The parent table must have a matching column or set of columns that is defined as the primary key. When you create a foreign key, you are creating a referential constraint, or a data link, between a dependent table and its parent table. This referential constraint can include rules for deleting or updating dependent rows in the parent table.

The foreign key name is optional. If you do not specify a foreign key name, Pervasive PSQL tries to create a foreign key using the name of the first column in the foreign key definition. For more information about naming conventions for foreign keys and other database elements, refer to Naming Conventions.

Because Pervasive PSQL keywords are reserved words, you cannot use them in naming database elements. For a list of the Pervasive PSQL keywords, refer to the following topic in SQL Engine Reference: SQL Reserved Words.

Creating Foreign Keys in Existing Tables

To create a foreign key in an existing table, follow these steps:

Ensure that a primary key exists in the parent table you are referencing.

All columns in the primary and foreign key must be of the same data type and length, and the set of columns must be in the same order in both definitions.

Pervasive PSQL creates a non-null index for the column or group of columns specified in the foreign key definition. If the table definition already has such an index, Pervasive PSQL uses that index; otherwise, Pervasive PSQL creates a non-named index with the non-null, non-unique, and modifiable index attributes.

Create the foreign key using an ADD CONSTRAINT clause in an ALTER TABLE statement.

For example, the following statement creates a foreign key called Faculty_Dept on the column Dept_Name in the Faculty table of the sample database. The foreign key references the primary key created in the Department table and specifies the delete restrict rule.

ALTER TABLE Faculty
ADD CONSTRAINT Faculty_Dept FOREIGN KEY (Dept_Name)
REFERENCES Department
ON DELETE RESTRICT;

Creating Foreign Keys When Creating a Table

To create a foreign key when creating the table, follow these steps: