InterBase Quick Start: Part II - Primary Keys and Foreign Keys

Go Up to InterBase Quick Start: Part II - Data Definition

The Job table definition includes a primary key and a foreign key.

• A primary key is a column or group of columns that uniquely identify a row. Every table should have a primary key. And a table cannot have more than one primary key. The PRIMARY KEY characteristic can be specified as part of a column definition (as in the first column of the Country table), or it can be specified as a separate clause of theCREATE TABLE statement (as in the statement that creates the Job table). The primary key in the Job table is made up of three columns: job_code, job_grade, and job_country. While a value can appear more than once in any of these columns taken individually, the fact that they are collectively a primary key means that the three values taken together cannot occur in more than one row.

• A foreign key is a column or set of columns in one table whose values must have matching values in the primary key of another (or the same) table. A foreign key is said to reference its primary key. Foreign keys are a mechanism for maintaining data integrity. For example, any country listed in the job_country column of the Job table must also exist in the Country table.

• You can declare a constraint such as UNIQUE, FOREIGN KEY, or PRIMARY KEY either as part of a column definition, or as a table constraint following column definitions. The syntax varies slightly depending on which approach you choose. See the Language Reference Guide for more details.

• You declare the primary key constraint as part of a column definition in the Country and Department tables. For the Job table, you declare the primary key, foreign key, and check constraints at the table level. Both approaches have the same result.

Cascading Referential Integrity Constraints

When you create a foreign key, you are saying that the value in the foreign key must also exist in the primary key that it references. What happens if later the value in the referenced primary key changes or is deleted? The cascading referential integrity constraints let you specify what should happen. Your choices are to take no action, to propagate (cascade) the change to the foreign key column, to set the foreign key to its default, or to set it to NULL.

If you are specifying the foreign key as part of the column definition, the syntax is this:

CREATE TABLE table_name 
 (
   column_name data_type FOREIGN KEY
     REFERENCES other_table(columns)
     [ON UPDATE    {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
     [ON DELETE    {NO ACTION | CASCADE | SET DEFAULT | SET NULL}],
   [, column_definition]
 )

If you specify the foreign key as a table-level constraint, the syntax is similar. In this case you have to specify the column for which the constraint is defined:

CREATE TABLE table_name 
 (
   column_definitions,
   FOREIGN KEY (column_name) REFERENCES other_table(columns)
     [ON UPDATE    {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
     [ON DELETE    {NO ACTION | CASCADE | SET DEFAULT | SET NULL}],
   [, table_constraints]
 )

Later on in the tutorial you use ALTER TABLE to add columns and constrains to the Department table, including some cascading referential integrity constraints.

Naming Constraints

When you declare a constraint you may name the constraint using the optional CONSTRAINT keyword, followed by the constraint name. This allows you to use the ALTER TABLE statement to drop that constraint. In the Job table definition, two of the constraints have names (pkjob and fkjob), and the CHECK constraint does not have a name.

Computed Columns

When you create a table, you can define columns whose value is based on the values of one or more other columns in the table. The computation can include any arithmetic operation that is suitable to the data types of the columns.

• To see an example, open the script TABLES.SQL and take a look at the following column definition for the Employee table:

full_name COMPUTED BY (last_name || ', ' || first_name)

The value of the full_name column consists of the value of the last_name column plus a comma plus the value of the first_name column.

Advance To:

• Backing Up a Database