Column-Level Foreign Key Constraint

If a foreign key constraint specification involves only one column, then the foreign key constraint can be specified at the column level.

Consider the following example:

CREATE TABLE supplier_item (

     supp_no     INTEGER NOT NULL PRIMARY KEY,

     item_no     INTEGER NOT NULL REFERENCES item,

     qty         INTEGER

) ;

In the above example, item_no is the foreign key, referencing the table item. Since the foreign key involves only one column, they are defined at the column level. If a foreign key references a candidate key, then the specification of the referenced column list is mandatory. If a foreign key references a primary key, then the referenced column list is optional.

Consider the following example:

CREATE TABLE invoice (

     inv_no        INTEGER NOT NULL PRIMARY KEY,

     item_no       INTEGER REFERENCES item,

     part_no       CHAR (3) NOT NULL

                   REFERENCES parts (part_no),

     qty           INTEGER NOT NULL,

     amount        MONEY NOT NULL,

     balance       MONEY NOT NULL

) ;

In the above example, item_no references the primary key of table item. The column part_no references primary or candidate key of table parts depending on whether part_no is the primary key or the candidate key of the table parts.