FOREIGN KEY
Remarks
Include the FOREIGN KEY keywords in the ADD clause to add a foreign key to a table definition.
Note You must be logged in to the database using a database name before you can add a foreign key or conduct any other referential integrity (RI) operation. Also, when security is enabled, you must have the Reference right on the table to which the foreign key refers before you can add the key.
Include a FOREIGN KEY clause in your CREATE TABLE statement to define a foreign key on a dependent table. In addition to specifying a list of columns for the key, you can define a name for the key.
The columns in the foreign key column may be nullable. However, ensure that pseudo-null columns do not exist in an index that does not index pseudo-null values.
The foreign key name must be unique in the dictionary. If you omit the foreign key name, PSQL uses the name of the first column in the key as the foreign key name. This can cause a duplicate foreign key name error if your dictionary already contains a foreign key with that name.
When you specify a foreign key, PSQL creates an index on the columns that make up the key. This index has the same attributes as the index on the corresponding primary key except that it allows duplicate values. To assign other attributes to the index, create it explicitly using a CREATE INDEX statement. Then, define the foreign key with an ALTER TABLE statement. When you create the index, ensure that it does not allow null values and that its case and collating sequence attributes match those of the index on the corresponding primary key column.
The columns in a foreign key must be the same data types and lengths and in the same order as the referenced columns in the primary key. The only exception is that you can use an integer column in the foreign key to refer to an IDENTITY, SMALLIDENTITY, or BIGIDENTITY column in the primary key. In this case, the two columns must be the same length.
PSQL checks for anomalies in the foreign keys before it creates the table. If it finds conditions that violate previously defined referential integrity (RI) constraints, it generates a status code and does not create the table.
Note When you create a foreign key on a table that already contains data, PSQL does not validate the data values already present in the foreign key columns and those in the primary key columns. This constraint applies to an INSERT, UPDATE, or DELETE action made after the foreign key is created.
When you define a foreign key, you must include a REFERENCES clause indicating the name of the table that contains the corresponding primary key. The primary key in the parent table must already be defined. In addition, if security is enabled on the database, you must have the Reference right on the table that contains the primary key.
You cannot create a self-referencing foreign key with the CREATE TABLE statement. Use an ALTER TABLE statement to create a foreign key that references the primary key in the same table.
Also, you cannot create a primary key and a foreign key on the same set of columns in a single statement. Therefore, if the primary key of the table you are creating is also a foreign key on another table, you must use an ALTER TABLE statement to create the foreign key.
Examples
The following statement adds a new foreign key to the Class table. (The Faculty column is defined as an index that does not include null values.)
ALTER TABLE Class ADD Constraint Teacher FOREIGN KEY (Faculty_ID) REFERENCES Faculty ON DELETE RESTRICT
In this example, the restrict rule for deletions prevents someone from removing a faculty member from the database without first either changing or deleting all of that faculty's classes.
See Also