Skip to content

FOREIGN KEY Constraint

AFOREIGN KEY constraint allows tables to cross-reference related data within the same table or across different tables, helping maintain consistency for related data.

Rules to Follow

When defining a foreign key, you must follow these rules:

  • Theparent table must already exist in the database or be the table currently being created. If it's the latter, the parent and child tables are the same, known as aself-referencing table, and this structure is calledself-referencing integrity.
  • Aprimary key must be defined for the parent table. *Primary keys cannot contain NULL values, but NULL values are allowed in foreign keys. This means the foreign key's content is valid as long as every non-null value in the foreign key appears in the specified primary key.
  • Specify thecolumn name or a combination of column names after the parent table's name. This column or combination of columns must be the primary key or a candidate key of the parent table.
  • Thenumber of columns in the foreign key must be the same as the number of columns in the parent table's primary key.
  • Thedata types of the columns in the foreign key must be the same as the data types of the corresponding columns in the parent table's primary key.
  • Thevalues in the foreign key must match the values in the parent table's primary key.

Foreign Key Characteristics

Self-referencing Foreign Key: This is when a column in a table references the primary key of the same* table. This design is typically used to represent hierarchical or parent-child relationships, such as organizational structures or category trees. Composite Foreign Key: This type of foreign key involves two or more columns in one table jointly referencing the primary key of another table. These columns collectively define the reference to the other table. They must exist as a group and must satisfy the foreign key constraint together. Multi-level Foreign Key: This situation usually involves three or more tables with interdependent relationships. A foreign key in one table can be the primary key of another table, and that table's foreign key can then be the primary key of a third table, forming a multi-level foreign key scenario.

Syntax Explanation

Foreign keys are defined in the child table. The basic foreign key constraint syntax is as follows:

> CREATE TABLE child_table (
    ...,
    foreign_key_column data_type,
    FOREIGN KEY (foreign_key_column) REFERENCES parent_table (parent_key_column)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]
);

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Parameter Definitions

In the syntax structure for foreign key constraints above, here are the definitions of each parameter:

  • child_table: The name of thechild table, which contains the foreign key.
  • foreign_key_column: The name of theforeign key column in the child table used to reference the parent table.
  • data_type: Thedata type of the foreign key column.
  • parent_table: The name of thereferenced parent table.
  • parent_key_column: The name of theprimary key column in the parent table used to establish the relationship.
  • [ON DELETE reference_option]: An optional parameter specifying the action to perform when records in the parent table are deleted.
    • RESTRICT: Prevents deleting data in the referenced table if related foreign key data exists in the referencing table. This helps prevent accidental deletion of associated data, maintaining data consistency.
    • CASCADE: When data in the referenced table is deleted, associated foreign key data is also deleted. This can be used for cascading deletions of related data to ensure data integrity.
    • SET NULL: When data in the referenced table is deleted, sets the foreign key column's value to NULL. This can be used to preserve foreign key data when deleting referenced data but disconnect the association with the referenced data.
    • NO ACTION: Indicates no action is taken, only checking if associated data exists. This is similar to RESTRICT but may have minor differences in some databases.
  • [ON UPDATE reference_option]: An optional parameter specifying the action to perform when records in the parent table are updated. Possible values are the same as [ON DELETE reference_option].

These parameters collectively define the foreign key constraint, ensuring data integrity relationships between the child and parent tables.

Examples

Example 1

-- Create a table named t1 with two columns: a and b. Column 'a' is an integer type and set as the primary key, and column 'b' is a varchar type with a length of 5.
create table t1(a int primary key, b varchar(5));

-- Create a table named t2 with three columns: a, b, and c. Column 'a' is an integer type, column 'b' is a varchar type with a length of 5. Column 'c' is an integer type and is set as a foreign key, establishing a relationship with column 'a' of table t1.
create table t2(a int ,b varchar(5), c int, foreign key(c) references t1(a));

-- Insert two rows of data into table t1: (101, 'abc') and (102, 'def').
mysql> insert into t1 values(101,'abc'),(102,'def');
Query OK, 2 rows affected (0.01 sec)

-- Insert two rows of data into table t2: (1, 'zs1', 101) and (2, 'zs2', 102), where 101 and 102 are primary keys of table t1.
mysql> insert into t2 values(1,'zs1',101),(2,'zs2',102);
Query OK, 2 rows affected (0.01 sec)

-- Insert one row of data into table t2: (3, 'xyz', null), where null indicates that this row has no associated primary key in column c (i.e., the foreign key column).
mysql> insert into t2 values(3,'xyz',null);
Query OK, 1 row affected (0.01 sec)

-- Attempt to insert one row of data into table t2: (3, 'xxa', 103), but 103 does not exist in the primary key of table t1, so the insertion fails, violating the foreign key constraint.
mysql> insert into t2 values(3,'xxa',103);
ERROR 20101 (HY000): internal error: Cannot add or update a child row: a foreign key constraint fails

Example Explanation: In the example above, column c of t2 can only reference values from column a in t1 or NULL. Therefore, the first three insert operations into t2 succeed. However, the fourth row's 103 is not a value in column a of t1, which violates the foreign key constraint, causing the insertion to fail.

Example 2 - Self-referencing Foreign Key

-- Create a table named 'categories' to store product category information.
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,-- 'id' is the primary key, used to uniquely identify each category.
    name VARCHAR(255) NOT NULL,-- 'name' is the category's name.
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)-- 'parent_id' is a foreign key that references the 'id' column in the 'categories' table.
);

-- The 'parent_id' column allows us to specify a parent category for a category. If there's no parent category (i.e., a top-level category), 'parent_id' can be set to NULL. Next, we can insert some data to demonstrate this hierarchical relationship:

-- Insert top-level categories
mysql> INSERT INTO categories (name) VALUES ('Electronics'),('Books');
Query OK, 2 rows affected (0.01 sec)

-- Insert subcategories
mysql> INSERT INTO categories (name, parent_id) VALUES ('Laptops', 1),('Smartphones', 1),('Science Fiction', 2),('Mystery', 2);
Query OK, 4 rows affected (0.01 sec)

mysql> select * from categories;
+------+-----------------+-----------+
| id   | name            | parent_id |
+------+-----------------+-----------+
|    1 | Electronics     |      NULL |
|    2 | Books           |      NULL |
|    3 | Laptops         |         1 |
|    4 | Smartphones     |         1 |
|    5 | Science Fiction |         2 |
|    6 | Mystery         |         2 |
+------+-----------------+-----------+
6 rows in set (0.01 sec)

Example Explanation: In the code above, we created a table named categories to store product category information. We first inserted two top-level categories, Electronics and Books. Then, we added subcategories for each top-level category, such as Laptops and Smartphones as subcategories of Electronics, and Science Fiction and Mystery as subcategories of Books.

Example 3 - Composite Foreign Key

-- Create a table named "Student" to store student information.
CREATE TABLE Student (
    StudentID INT, -- Student ID field, integer.
    Name VARCHAR(100), -- Student name field, string with maximum length of 100.
    PRIMARY KEY (StudentID) -- Set StudentID as the primary key of this table.
);

-- Create a table named "Course" to store course information.
CREATE TABLE Course (
    CourseID INT, -- Course ID field, integer.
    CourseName VARCHAR(100), -- Course name field, string with maximum length of 100.
    PRIMARY KEY (CourseID) -- Set CourseID as the primary key of this table.
);

-- Create a table named "StudentCourse" to store student course selection information.
CREATE TABLE StudentCourse (
    StudentID INT, -- Student ID field, integer, corresponds to the StudentID field of the Student table.
    CourseID INT, -- Course ID field, integer, corresponds to the CourseID field of the Course table.
    PRIMARY KEY (StudentID, CourseID), -- Set the combination of StudentID and CourseID as the primary key of this table.
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID), -- Set the StudentID field as a foreign key, referencing the StudentID field of the Student table.
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID) -- Set the CourseID field as a foreign key, referencing the CourseID field of the Course table.
);

Example Explanation: In the examples above, we have a Student table and a Course table, and a StudentCourse table to record which student chose which course. In this scenario, the StudentID and CourseID in the StudentCourse table can be used asforeign keys, jointly referencing the primary keys of the Student table and Course table.

Example 4 - Multi-layer Foreign Key

-- Create a table called "Country" to store country information.
CREATE TABLE Country (
    CountryID INT, -- Country ID field, integer.
    CountryName VARCHAR(100), -- Country Name field, string with maximum length of 100.
    PRIMARY KEY (CountryID) -- Set CountryID as the primary key of this table.
);

-- Create a table named "State" to store state/province information.
CREATE TABLE State (
    StateID INT, -- State/Province ID field, integer.
    StateName VARCHAR(100), -- State/Province Name field, string with maximum length of 100.
    CountryID INT, -- Country ID field, integer, corresponds to CountryID field of Country table.
    PRIMARY KEY (StateID), -- Set StateID as the primary key of this table.
    FOREIGN KEY (CountryID) REFERENCES Country(CountryID) -- Set the CountryID field to a foreign key, referring to the CountryID field of the Country table.
);

-- Create a table called "City" to store city information.
CREATE TABLE City (
    CityID INT, -- CityID field, integer.
    CityName VARCHAR(100), -- City Name field, string with maximum length of 100.
    StateID INT, -- State/Province ID field, integer, corresponding to the StateID field of the State table.
    PRIMARY KEY (CityID), -- Set CityID as the primary key of this table.
    FOREIGN KEY (StateID) REFERENCES State(StateID) -- Set the StateID field to a foreign key, referring to the StateID field of the State table.
);

Example Explanation: In the example above, there are three tables: Country, State, and City. The State table has a CountryID field, which is both the primary key of the Country table and a foreign key in the State table. The City table has a StateID field, which is both the primary key of the State table and a foreign key in the City table. This creates amulti-layer foreign key situation.