Full analysis of SQL Foreign Key

1. What are foreign keys? ​​ ​​

• Definition: A foreign key is a column (or a group of columns) in a database table, which is used to establish an association between two tables. The value of a foreign key must match the value of the primary key or Unique Constraint of another table.
• Function:
  • Ensure the reference integrity of the data (Referential Integrity) to prevent invalid data insertion.
  • Maintain logical relationships between tables (such as "one-to-many" or "many-to-many").

​​2. The syntax of foreign keys

Define foreign keys when creating a table:

CREATE TABLE Sub-table (
    List1 Data Type,
    List2 Data Type,
    ...
    FOREIGN KEY (外键List) REFERENCES Parent table(主键List)
    [ON DELETE Constraint behavior] [ON UPDATE Constraint behavior]
);

Add a foreign key to an existing table:

ALTER TABLE Sub-table
ADD CONSTRAINT Constraint name
FOREIGN KEY (Foreign key column) REFERENCES Parent table(Primary key column)
[ON DELETE Constraint behavior] [ON UPDATE Constraint behavior];

​​3. Constraint behavior of foreign keys

How to deal with foreign keys of the child table when the records of the parent table are deleted or updated? passON DELETEandON UPDATESpecified:

​​4. Multi-column foreign keys

Foreign keys can be composed of multiple columns, which need to meet:

• The number of columns, order, and data types of children and parents are the same.
• The columns of the parent table must have unique constraints (such as primary keys or unique indexes).

​Example​：

CREATE TABLE Order details (
    OrderID INT,
    productID INT,
    quantity INT,
    PRIMARY KEY (OrderID, productID),
    FOREIGN KEY (OrderID) REFERENCES Order(OrderID),
    FOREIGN KEY (productID) REFERENCES product(productID)
);

​​5. Restrictions and precautions for foreign keys​​​​

• The parent table must have a primary key or unique constraint​。
• ​The data type of the foreign key column must be consistent with the parent table's primary key​​。
• ​Engine support​: For example, InnoDB of MySQL supports foreign keys, but MyISAM does not.
• ​Performance Impact​: Foreign keys will increase the inspection overhead of data operations, but can improve data consistency.
• ​Circular Dependencies​: Avoid referring to each other.

​​6. Practical application examples

​Scene​: Student Table (students) and course schedule (courses), through course selection schedule (enrollments) Relationship.

-- Parent table：Student Table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);
-- Parent table：Course Schedule
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);
-- Sub-table：Course selection schedule（Include foreign keys）
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE RESTRICT
);

​Insert data​：

-- Insert students and courses
INSERT INTO students VALUES (1, 'Alice');
INSERT INTO courses VALUES (101, 'Math');
-- Legal insertion：Students and courses exist
INSERT INTO enrollments VALUES (1, 101, '2023-10-01');
-- Illegal insertion：The student does not exist，Triggering foreign key error
INSERT INTO enrollments VALUES (999, 101, '2023-10-01'); -- Report an error！

​​7. FAQ​​

​​Does the foreign key point to the primary key? ​​
No, Unique Constraint that can point to the parent table.

​​Can you reference across databases? ​​
Usually not supported, foreign keys need to be in the same database.

​Does foreign keys allow NULL? ​​
If the foreign key column allows NULL, inserting NULL is legal (indicating that there is no association).

​​How to view foreign key constraints? ​​
Use database tools or query metadata (such as MySQL'sSHOW CREATE TABLE）。

8. Summary​

• The core role of foreign keys​: Maintain the consistency and relevance of data. ​​
• Applicable scenarios​: Systems that require strong data integrity (such as e-commerce and finance). ​​
• Use caution scenarios​: Systems with high concurrent writes and extremely high performance requirements (consistency and performance need to be weighed).

This is the end of this article about the detailed explanation of SQL Foreign Key. For more related contents of SQL Foreign Key, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!