SQL > Constraint > Foreign Key

A foreign key is a column (or columns) that references a column (most often the primary key) of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. Business logic requires that all orders must be associated with a customer that is already in the CUSTOMER table. To enforce this logic, we place a foreign key on the ORDERS table and have it reference the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

The structure of these two tables will be as follows:

Table CUSTOMER

Column NameCharacteristic
SIDPrimary Key
Last_Name  
First_Name  

Table ORDERS

Column NameCharacteristic
Order_IDPrimary Key
Order_Date  
Customer_SIDForeign Key
Amount  

In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table. The following examples are operations that violate the referential integrity of this relationship:

  • Inserting a row in the ORDERS table where Customer_SID does not appear in the SID column in the CUSTOMER table.
  • Deleting a row from the CUSTOMER table where the SID of the row to be delete is still present in the Customer_SID column in the ORDERS table.

    When these operations are attempted, the database would return an error stating that referential integrity is violated.

    It is worth noting that foreign keys allow NULL, while the primary key does not. In addition, a foreign key does not always have to reference a primary key of another table. It can also reference a column that has the UNIQUE constraint.

    Below we show examples of how to specify the foreign key when creating the ORDERS table:

    MySQL:

    CREATE TABLE ORDERS
    (Order_ID integer,
    Order_Date date,
    Customer_SID integer,
    Amount double,
    Primary Key (Order_ID),
    Foreign Key (Customer_SID) REFERENCES CUSTOMER(SID));

    Oracle:

    CREATE TABLE ORDERS
    (Order_ID integer primary key,
    Order_Date date,
    Customer_SID integer REFERENCES CUSTOMER(SID),
    Amount double);

    SQL Server:

    CREATE TABLE ORDERS
    (Order_ID integer primary key,
    Order_Date datetime,
    Customer_SID integer REFERENCES CUSTOMER(SID),
    Amount double);

    Below are examples for specifying a foreign key by altering a table. This assumes that the ORDERS table has been created, and the foreign key has not yet been specified:

    MySQL:

    ALTER TABLE ORDERS
    ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

    Oracle:

    ALTER TABLE ORDERS
    ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

    SQL Server:

    ALTER TABLE ORDERS
    ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

    Composite Foreign Key

    A composite foreign key is a foreign key that consists of two or more columns. It is important to note that all the columns in a single foreign key must point to the same table. In other words, it is not possible to have a foreign key that references to a column in Table 1 and a column in Table 2.

    To illustrate composite foreign key, let's look at the following example:

    Table INVOICE

    Column NameCharacteristic
    Invoice_IDPrimary Key
    Store_IDPrimary Key
    CUSTOMER_ID  

    Table PAYMENT

    Column NameCharacteristic
    Payment_IDPrimary Key
    Invoice_IDForeign Key
    Store_IDForeign Key
    Payment_Date  
    Payment_Amount  

    Below is CREATE TABLE statement that creates the composite foreign key when creating the PAYMENT table:

    CREATE TABLE PAYMENT
    (Payment_ID integer,
    Invoice_ID integer,
    Store_ID integer,
    Payment_Date datetime,
    Payment_Amount float,
    PRIMARY KEY (Payment_ID),
    FOREIGN KEY (Invoice_ID, Store_ID) REFERENCES INVOICE (Invoice_ID, Store_ID));

    The above SQL works for MySQL, Oracle, and SQL Server.

    Specifying the composite key using ALTER TABLE can be done as follows:

    MySQL:

    ALTER TABLE PAYMENT
    ADD FOREIGN KEY (Invoice_ID, Store_ID) REFERENCES INVOICE (Invoice_ID, Store_ID);

    Oracle:

    ALTER TABLE ORDERS
    ADD (CONSTRAINT fk_orders2) FOREIGN KEY (Invoice_ID, Store_ID) REFERENCES INVOICE (Invoice_ID, Store_ID);

    SQL Server:

    ALTER TABLE ORDERS
    ADD FOREIGN KEY (Invoice_ID, Store_ID) REFERENCES INVOICE (Invoice_ID, Store_ID);

    Next: SQL ALTER TABLE




    Copyright © 2014 1keydata.com   All Rights Reserved.   Privacy Policy