Primary Keys and Foreign Keys
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 the CREATE 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. In the Job table, for example, any country listed in the job_country column must also exist in the Country table. By stating that the job_country column of the Job table is a foreign key that references the country column of the Country table, you are guaranteeing this, because InterBase will return an error if a value is entered the job_country column that does not have a matching entry in the country column of 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 the column definitions. The syntax varies slightly depending on which you choose. See the Language Reference for details.
- You declared the primary key constraint as part of a column definition in the Country and Department tables. For the Job table, you declared the primary key, foreign key, and check constraints at the table level. Functionally, the effect is the same.
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 datatype FOREIGN KEY
- REFERENCES other_table(columns)
- [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
- [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}],
- [, more columns defs])
If you are specifying the foreign key as a table-level constraint, the syntax is nearly the same except that you have to identify the column for which it is being defined, so the syntax becomes:
- CREATE TABLE table_name (column_defs,
- 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}],
- [, more table constraints])
A little later, you will use ALTER TABLE to add columns and table constraints to the Department table, including some cascading referential integrity constraints.
Naming Constraints
When you declare a constraint at either the column level or the table level, you have the option of naming the constraint using the optional CONSTRAINT keyword, followed by a constraint name. When a constraint is named, you can drop it using the ALTER TABLE statement. In the Job table definition, two of the constraints have names “pkjob” and “fkjob”), but the CHECK constraint does not have a name (although it could have). When you alter the Department table a little later, you will add two named constraints.
