FOREIGN KEY integrity constraints

FOREIGN KEY constraints can keep related data consistent when cross-referencing associated data across tables.

Rules

When defining FOREIGN KEY, the following rules need to be followed:

  • The parent table must already exist in the database or be a table currently being created. In the latter case, the parent table and the slave table are the same table, such a table is called a self-referential table, and this structure is called self-referential integrity.

  • A primary key must be defined for the parent table.

  • Primary keys cannot contain null values, but null values are allowed in foreign keys. In other words, as long as every non-null value in the foreign key appears in the specified primary key, the content of the foreign key is correct.

  • Specify the column name or combination of column names after the table name of the parent table. This column or combination of columns must be the primary or candidate key of the primary table.

  • The number of columns in the foreign key must be the same as the number of columns in the primary key of the parent table.

  • The data type of the column in the foreign key must be the same as the data type of the corresponding column in the primary key of the parent table.

  • The foreign key’s value must be consistent with the primary key’s value in the main table.

Foreign Key Characteristics

  • Multi-column foreign key: This type of foreign key is when two or more columns in a table jointly reference another table’s primary key. In other words, these columns together define the reference to another table. They must exist in the form of a group and need to meet the foreign key constraint simultaneously.

  • Multi-level foreign key: This situation usually involves three or more tables, and they have a dependency relationship. A table’s foreign key can be another table’s primary key, and this table’s foreign key can be the primary key of a third table, forming a multi-level foreign key situation.

Syntax

Foreign keys are defined in the child table, and the primary foreign key constraint syntax is as follows:

  1. > column_name data_type FOREIGN KEY;
  2. > CREATE TABLE table_name (
  3.     column1 datatype [NOT NULL],
  4.     column2 datatype [NOT NULL],
  5.     ...
  6.     FOREIGN KEY (column1, column2, ... column_n)
  7.     REFERENCES parent_table (column1, column2, ... column_n)
  8. );

Explanation

  • FOREIGN KEY (column1, column2, ... column_n): Defines the columns to be used as the foreign key.
  • REFERENCES parent_table (column1, column2, ... column_n): REFERENCES defines the parent table being referenced and the columns in the parent table.

Examples

Example 1

  1. -- Create a table named t1, containing two columns: a and b. The column a is of type int and is set as the primary key, while the column b is of type varchar with a length of 5.
  2. create table t1(a int primary key, b varchar(5));
  4. -- Create a table named t2, containing three columns: a, b, and c. The column a is of type int, the column b is of type varchar with a length of 5. The column c is of type int, and is set as a foreign key, establishing a relationship with the column a in table t1.
  5. create table t2(a int ,b varchar(5), c int, foreign key(c) references t1(a));
  7. -- Insert two rows of data into table t1: (101, 'abc') and (102, 'def').
  8. mysql> insert into t1 values(101,'abc'),(102,'def');
  9. Query OK, 2 rows affected (0.01 sec)
  11. -- Insert two rows of data into table t2: (1, 'zs1', 101) and (2, 'zs2', 102), where 101 and 102 are the primary keys in table t1.
  12. mysql> insert into t2 values(1,'zs1',101),(2,'zs2',102);
  13. Query OK, 2 rows affected (0.01 sec)
  15. -- Insert a row of data into table t2: (3, 'xyz', null), where null means that this row of data has no associated primary key in column c (the foreign key column).
  16. mysql> insert into t2 values(3,'xyz',null);
  17. Query OK, 1 row affected (0.01 sec)
  19. -- Attempt to insert a row of data into table t2: (3, 'xxa', 103). However, 103 does not exist in the primary keys of table t1, so the insertion fails due to violation of the foreign key constraint.
  20. mysql> insert into t2 values(3,'xxa',103);
  21. ERROR 20101 (HY000): internal error: Cannot add or update a child row: a foreign key constraint fails

Example Explanation: In the above example, column c of t2 can only refer to the value or null value of column a in t1, so the operation of inserting row 1 and row 2 of t1 can be successfully inserted, but row 3 103 in the row is not a value in column a of t1, which violates the foreign key constraint, so the insert fails.

Example 2 - Multi-column foreign key

  1. -- Creating a "Student" table to store student information
  2. CREATE TABLE Student (
  3.     StudentID INT, -- Student ID field, integer
  4.     Name VARCHAR(100), -- Student name field, string with a maximum length of 100
  5.     PRIMARY KEY (StudentID) -- Setting the StudentID as the primary key of this table
  6. );
  8. -- Creating a "Course" table to store course information
  9. CREATE TABLE Course (
  10.     CourseID INT, -- Course ID field, integer
  11.     CourseName VARCHAR(100), -- Course name field, string with a maximum length of 100
  12.     PRIMARY KEY (CourseID) -- Setting the CourseID as the primary key of this table
  13. );
  15. -- Creating a "StudentCourse" table to store student course selection information
  16. CREATE TABLE StudentCourse (
  17.     StudentID INT, -- Student ID field, integer, corresponds to the StudentID field in the Student table.
  18.     CourseID INT, -- Course ID field, integer, corresponds to the CourseID field in the Course table.
  19.     PRIMARY KEY (StudentID, CourseID), -- Setting the combination of StudentID and CourseID as the primary key of this table
  20.     FOREIGN KEY (StudentID) REFERENCES Student(StudentID), -- Setting the StudentID field as the foreign key, referencing the StudentID field in the Student table
  21.     FOREIGN KEY (CourseID) REFERENCES Course(CourseID) -- Setting the CourseID field as the foreign key, referencing the CourseID field in the Course table
  22. );

Example Explanation: In the above example, there are three tables: the Student table, the Course table, and the StudentCourse table for recording which students have chosen which courses. In this case, the Student ID and Course ID in the course selection table can serve as foreign keys, jointly referencing the primary keys of the student table and the course table.

Example 3 - Multi-level foreign key

  1. -- Creating a "Country" table to store country information
  2. CREATE TABLE Country (
  3.     CountryID INT, -- Country ID field, integer
  4.     CountryName VARCHAR(100), -- Country name field, string with a maximum length of 100
  5.     PRIMARY KEY (CountryID) -- Setting the CountryID as the primary key of this table
  6. );
  8. -- Creating a "State" table to store state/province information
  9. CREATE TABLE State (
  10.     StateID INT, -- State/province ID field, integer
  11.     StateName VARCHAR(100), -- State/province name field, string with a maximum length of 100
  12.     CountryID INT, -- Country ID field, integer, corresponds to the CountryID field in the Country table.
  13.     PRIMARY KEY (StateID), -- Setting the StateID as the primary key of this table
  14.     FOREIGN KEY (CountryID) REFERENCES Country(CountryID) -- Setting the CountryID field as the foreign key, referencing the CountryID field in the Country table
  15. );
  17. -- Creating a "City" table to store city information
  18. CREATE TABLE City (
  19.     CityID INT, -- City ID field, integer
  20.     CityName VARCHAR(100), -- City name field, string with a maximum length of 100
  21.     StateID INT, -- State/province ID field, integer, corresponds to the StateID field in the State table
  22.     PRIMARY KEY (CityID), -- Setting the CityID as the primary key of this table
  23.     FOREIGN KEY (StateID) REFERENCES State(StateID) -- Setting the StateID field as the foreign key, referencing the StateID field in the State table
  24. );

Example Explanation: In the above example, there are three tables: the Country table, the State table, and the City table. The State table has a field, CountryID, which is the primary key of the Country table and is also the foreign key of the State table. The City table has a field, StateID, which is the State table’s primary key and the City table’s foreign key. This forms a multi-level foreign key situation.