ALTER TABLE - ADD clauses for foreign keys

These ADD clauses of the MySQL statement ALTER TABLE add foreign keys and references to MySQL tables that use the storage engine, InnoDB in MySQL.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER [IGNORE] TABLE table
ADD [CONSTRAINT [symbol]] PRIMARY KEY [USING index_type] (column,...) |
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] index [USING index_type] (column,...) |
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...) 
   [REFERENCES table (column, ...)
   [ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]
   [ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]]

Explanation

These ADD clauses add foreign keys and referencesto InnoDB tables. A foreign key is an index that refers to a key or index in another table. See the explanation of the CREATE TABLE statement for more information and an example of an SQL statement involving the creation of foreign keys in a table. The various flags shown are also explained under the CREATE TABLE statement.

This page lists all of the table options that can be set with the ALTER TABLE statement. The options are the same as those that can be specified for CREATE TABLE when a table is first created. See the description of that statement for more information about the options available. You can give multiple options to CREATE TABLE in a comma-separated list.

Examples

CREATE TABLE employees 
(emp_id INT AUTO_INCREMENT PRIMARY KEY, 
tax_id CHAR(12), 
emp_name VARCHAR(100))
ENGINE = INNODB;

CREATE TABLE employees_telephone 
(emp_id INT, 
tel_type ENUM('office','home','mobile'),
tel_number CHAR(25)) 
ENGINE = INNODB;

ALTER TABLE employees_telephone 
ADD FOREIGN KEY emp_tel (emp_id) 
REFERENCES employees (emp_id) 
ON DELETE RESTRICT;

The first two SQL statements create InnoDB tables: one for basic employee information and the second for employees' telephone numbers. Using the ALTER TABLE statement afterward, we add a foreign key restriction between the two. Let's look at the results using the SHOW TABLE STATUS statement, because the SHOW INDEXES statement won't show foreign key restraints:

SHOW TABLE STATUS FROM human_resources 
LIKE 'employees_telephone' G

*************************** 1. row ***************************
           Name: employees_telephone
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-04-03 04:01:39
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 4096 kB; (`emp_id`) 
                 REFER `human_resources`.`employees`(`emp_id`)

In the Comment field, we can see that we've created a restraint on the main table employees from employees_telephone. We're telling MySQL not to allow a row for an employee to be removed from theemployees table without first removing the rows of data for the employee in the employees_telephone table.

In the following example we first insert an employee in the employees table, then add her home telephone number to the second table, and then attempt to delete her from the first table:

INSERT INTO employees 
VALUES(1000,'123-45-6789','Paola Caporale');

INSERT INTO employees_telephone 
VALUES(1000,2,'+39 343-12-34-5678');

DELETE FROM employees WHERE emp_id = 1000;

ERROR 1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails 
(`human_resources`.`employees_telephone`, 
  CONSTRAINT `employees_telephone_ibfk_1` 
  FOREIGN KEY (`emp_id`) REFERENCES `employees` (`emp_id`)
)

As you can see, we cannot delete the employee from the employees table and leave the stray row of data in the employees_telephone table. We have to delete the data in employees_telephone first, before deleting the related data from employees. See the explanation under CREATE TABLE in the section FOREIGN KEY definitions for examples of the other options with foreign keys. Incidentally, you can't drop and add a foreign key in the same ALTER TABLE statement.