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 Name  Characteristic 
 SID  Primary Key 
 Last_Name   
 First_Name   

Table ORDERS

 Column Name  Characteristic 
 Order_ID  Primary Key 
 Order_Date   
 Customer_SID  Foreign 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 Name  Characteristic 
     Invoice_ID  Primary Key 
     Store_ID  Primary Key 
     CUSTOMER_ID   

    Table PAYMENT

     Column Name  Characteristic 
     Payment_ID  Primary Key 
     Invoice_ID  Foreign Key 
     Store_ID  Foreign 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

    This page was last updated on October 11, 2024.



    Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact
    		•