How to code a foreign key constraint

Three attributes for working with a foreign key

Attribute	Description
CONSTRAINT	The name of the constraint. Can only be coded at the table level.
FOREIGN KEY	The names of the columns that make up the foreign key. Can only be coded at the table level.
REFERENCES	The name of the related table and the column or columns. Can be coded at the column or table level. When coded at the column level, it can only refer to a single column.

A table with a column-level foreign key constraint

       CREATE TABLE orders ( 
         orderID PRIMARY KEY,
         customerID NOT NULL
                    REFERENCES customers (customerID),
         orderDate  NOT NULL
       )

A table with a table-level foreign key constraint

      CREATE TABLE orders (
        orderID INT PRIMARY KEY,
        customerID INT  NOT NULL,
        orderDate DATETIME  NOT NULL,
        CONSTRAINT 
          ordersFkCustomers FOREIGN KEY (customerID)
          REFERENCES customers (customerID)
      )

An INSERT statement that fails because a related row doesn’t exist

      INSERT INTO orders 
      VALUES (1, 999, '2017-08-03');

The response from the system

      Error Code: 1452.
      
      Cannot add or update a child row: a foreign key con-straint fails ('ex'.'orders', CONSTRAINT 'ordersFkCustomers' FOREIGN KEY ('customerID') REFERENCES 'customers' ('customerID'))

A constraint that uses the ON DELETE clause

      CONSTRAINT ordersFkCustomers 
        FOREIGN KEY (customerID) REFERENCES customers (customerID) 
        ON DELETE CASCADE

    Description
      A foreign key constraint requires values in one table to match values in another table. This defines the relationship between two tables and enforces referential integrity.
To define a relationship that consists of two or more columns, you must define the constraint at the table level.

    

Back