Foreign Key/REFERENCES

Foreign keys are more complex than primary keys. Primary keys make a column UNIQUE and NOT NULL. Foreign keys, on the other hand, constrain data based on columns in other tables. They are called foreign keys because the constraints are foreign--that is, outside the table.

For example, suppose a table contains customer addresses, and part of each address is a United States two-character state code. If a table held all valid state codes, a foreign key constraint could be created to prevent a user from entering invalid state codes.

Figure  shows the creation of a primary key/foreign key relationship.  

        test=>  CREATE TABLE statename (code CHAR(2) PRIMARY KEY,
        test(>                          name  CHAR(30) 
        test(> );
        CREATE
        test=> INSERT INTO statename VALUES ('AL', 'Alabama');
        INSERT 18934 1
        ... 
         
        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2) REFERENCES statename,
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE  

Foreign key constraints are created by using REFERENCES to refer to the primary key of another table. Foreign keys link the tables together and prevent the insertion or updating of invalid data.

Figure  shows how foreign keys constrain column values.  

        test=> INSERT INTO customer (state) 
        test-> VALUES ('AL');
        INSERT 148732 1
        test=> INSERT INTO customer (state)
        test-> VALUES ('XX');
        ERROR:  <unnamed> referential integrity violation - key referenced from customer not found in statename  

Here AL is a primary key value in statename, so the INSERT is accepted. XX is not a primary key value in statename, so the INSERT is rejected by the foreign key constraint.

Figure  shows the creation of the company tables from Figure , page , using primary and foreign keys.  

        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER PRIMARY KEY,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2),
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE
        test=> CREATE TABLE employee (
        test(>                        employee_id INTEGER PRIMARY KEY,
        test(>                        name        CHAR(30),
        test(>                        hire_date   DATE
        test(> );
        CREATE
        test=> CREATE TABLE part (
        test(>                    part_id     INTEGER PRIMARY KEY,
        test(>                    name        CHAR(30),
        test(>                    cost        NUMERIC(8,2),
        test(>                    weight      FLOAT
        test(> );
        CREATE
        test=> CREATE TABLE salesorder (
        test(>                          order_id      INTEGER,
        test(>                          customer_id   INTEGER REFERENCES customer,
        test(>                          employee_id   INTEGER REFERENCES employee,
        test(>                          part_id       INTEGER REFERENCES part,
        test(>                          order_date    DATE,
        test(>                          ship_date     DATE, 
        test(>                          payment       NUMERIC(8,2)
        test(> );
        CREATE  

A variety of foreign key options are discussed next that make foreign keys even more powerful.

Modification of Primary Key Row

If a foreign key constraint references a row as its primary key, and the primary key row is updated or deleted, then the default foreign key action is to prevent the operation. The foreign key options ON UPDATE  and ON DELETE , however, allow a different action to be taken. Figure  shows how these options work.  

        test=> CREATE TABLE customer (                              
        test(>                        customer_id INTEGER,
        test(>                        name        CHAR(30),
        test(>                        telephone   CHAR(20),
        test(>                        street      CHAR(40),
        test(>                        city        CHAR(25),
        test(>                        state       CHAR(2) REFERENCES statename 
        test(>                                            ON UPDATE CASCADE 
        test(>                                            ON DELETE SET NULL,
        test(>                        zipcode     CHAR(10),
        test(>                        country     CHAR(20)
        test(> );
        CREATE  

The new customer table's ON UPDATE  CASCADE specifies that if statename's primary key is updated, customer.state should be updated with the new value as well. The foreign key ON DELETE  SET NULL  option specifies that if someone tries to delete a statename row that is referenced by another table, the delete operation should set the foreign key to NULL.

 The ON UPDATE and ON DELETE options can have the following actions:

NO ACTION

UPDATEs and DELETEs to the primary key are prohibited if referenced by a foreign key row. This is the default.

CASCADE

UPDATEs to the primary key update all foreign key columns that reference it. DELETEs on the primary key cause the deletion of all foreign key rows that reference it.

SET NULL

UPDATEs and DELETEs to the primary key row cause the foreign key to be set to NULL. 

SET DEFAULT

UPDATEs and DELETEs to the primary key row cause the foreign key to be set to its DEFAULT. 

Figure  illustrates the use of the CASCADE and NO ACTION rules.  

        
        test=> CREATE TABLE primarytest (col INTEGER PRIMARY KEY);
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest_pkey' for table 'primarytest'
        CREATE
        test=> CREATE TABLE foreigntest (
        test(>                           col2 INTEGER REFERENCES primarytest
        test(>                           ON UPDATE CASCADE 
        test(>                           ON DELETE NO ACTION
        test(>                          );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> INSERT INTO primarytest values (1);
        INSERT 148835 1
        test=> INSERT INTO foreigntest values (1);
        INSERT 148836 1
        test=>
        test=> -- CASCADE UPDATE is performed
        test=>
        test=> UPDATE primarytest SET col = 2;
        UPDATE 1
        test=> SELECT * FROM foreigntest;
         col2 
        ------
            2
        (1 row) 
         
        test=> 
        test=> -- NO ACTION prevents deletion
        test=>
        test=> DELETE FROM primarytest;
        ERROR:  <unnamed> referential integrity violation - key in primarytest still referenced from foreigntest
        test=>
        test=> -- By deleting the foreign key first, the DELETE succeeds
        test=>
        test=> DELETE FROM foreigntest;
        DELETE 1
        test=> DELETE FROM primarytest;
        DELETE 1
         

First, primarytest, which was used in Figure , is created. Then a foreigntest table with ON UPDATE  CASCADE and ON DELETE  NO ACTION is created. NO ACTION is the default, so ON DELETE NO ACTION was not required. Next, a single row is inserted into each table, and an UPDATE on primarytest cascades to UPDATE foreigntest. The primarytest row cannot be deleted unless the foreign key row is deleted first. Foreign key actions offer you great flexibility in controlling how primary key changes affect foreign key rows. 

  
Multicolumn Primary Keys

To specify a multicolumn primary key, it was necessary to use PRIMARY KEY on a separate line in the CREATE TABLE statement. Multicolumn foreign keys have the same requirement. Using primarytest2 from Figure , Figure  shows how to create a multicolumn foreign key.  

        test=> CREATE TABLE primarytest2 (
        test(>                            col1 INTEGER, 
        test(>                            col2 INTEGER, 
        test(>                            PRIMARY KEY(col1, col2)
        test(>                           );
        NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'primarytest2_pkey' for table 'primarytest2'
        CREATE
        test=> CREATE TABLE foreigntest2 (col3 INTEGER, 
        test(>                            col4 INTEGER,
        test(>                            FOREIGN KEY (col3, col4) REFERENCES primarytest2
        test->                          );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE  

FOREIGN KEY (col, ...) must be used to label any multicolumn foreign key table constraints. 

  
Handling NULL Values in the Foreign Key

A NULL value cannot reference a primary key. A single-column foreign key is either NULL or matches a primary key. In a multicolumn foreign key, sometimes only part of a foreign key can be NULL. The default behavior allows some columns in a multicolumn foreign key to be NULL and others to be not NULL.

Using MATCH FULL in a multicolumn foreign key constraint requires all columns in the key to be NULL or all columns to be not NULL. Figure  illustrates this case.  

        test=> INSERT INTO primarytest2 
        test-> VALUES (1,2);
        INSERT 148816 1
        test=> INSERT INTO foreigntest2
        test-> VALUES (1,2);
        INSERT 148817 1
        test=> UPDATE foreigntest2
        test-> SET col4 = NULL;
        UPDATE 1
        test=> CREATE TABLE matchtest (
        test(>                    col3 INTEGER,
        test(>                    col4 INTEGER,
        test(>                    FOREIGN KEY (col3, col4) REFERENCES primarytest2 
        test(>                                             MATCH FULL
        test(>                        );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> UPDATE matchtest 
        test-> SET col3 = NULL, col4 = NULL;
        UPDATE 1
        test=> UPDATE matchtest
        test-> SET col4 = NULL;
        ERROR:  <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values  

First, the tables from Figure  are used to show that the default allows one column of a foreign key to be set to NULL. Next, the table matchtest is created with the MATCH FULL foreign key constraint option. MATCH FULL allows all key columns to be set to NULL, but rejects the setting of only some multicolumn key values to NULL.   

   
Frequency of Foreign Key Checking

By default, foreign key constraints are checked at the end of each INSERT, UPDATE , and DELETE  query. Thus, if you perform a set of complex table modifications, the foreign key constraints must remain valid at all times. For example, using the tables in Figure , if a new state is added and then a new customer in the new state is inserted, the new state must be added to statename before the customer is added to customer.

In some cases, it may not be possible to keep foreign key constraints valid between queries. For example, if two tables are foreign keys for each other, it may not be possible to INSERT into one table without having the other table row already present. A solution is to use the DEFERRABLE foreign key option and SET CONSTRAINTS so that foreign key constraints are checked only at transaction  commit. With this approach, a multiquery transaction can make table modifications that violate foreign key constraints inside the transaction as long as the foreign key constraints are met at transactions commit. Figure  is a contrived example of this case; the proper way to perform this query is to INSERT into primarytest first, then INSERT into defertest.  

        test=> CREATE TABLE defertest( 
        test(>                        col2 INTEGER REFERENCES primarytest 
        test(>                                     DEFERRABLE
        test(> );
        NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
        CREATE
        test=> BEGIN;
        BEGIN
        test=> -- INSERT is attempted in non-DEFERRABLE mode
        test=>
        test=> INSERT INTO defertest VALUES (5);
        ERROR:  <unnamed> referential integrity violation - key referenced from defertest not found in primarytest
        test=> COMMIT;
        COMMIT
        test=> BEGIN;
        BEGIN
        test=> -- all foreign key constraints are set to DEFERRED
        test=>
        test=> SET CONSTRAINTS ALL DEFERRED;
        SET CONSTRAINTS
        test=> INSERT INTO defertest VALUES (5);
        INSERT 148946 1
        test=> INSERT INTO primarytest VALUES (5);
        INSERT 148947 1
        test=> COMMIT;
        COMMIT  

In complex situations, such reordering might not be possible, so DEFERRABLE and SET CONSTRAINTS should be used to defer foreign key constraints. A foreign key may also be configured as INITIALLY DEFERRED, causing the constraint to be checked only at transaction  commit by default.

You can name constraints if desired. The constraint names will appear in constraint violation messages and can be used by SET CONSTRAINTS. See the CREATE_TABLE and SET manual pages for more information.