Foreign Key Constraint

What is a Foreign Key Constraint in MySQL?

A Foreign Key Constraint in MySQL is used to create a relationship between two tables.

It creates a link between a column in one table and the Primary Key column of another table.

Syntax:

CREATE TABLE table_name (
    column_name data_type,
    FOREIGN KEY (column_name) REFERENCES parent_table_name(column_name)
);

Example:

Suppose the Student table contains two columns: Student_ID and Name, and the Course table contains three columns: Course_ID, Course_Name, and Student_ID. The Student_ID field in the Course table is the Foreign Key because it creates a connection between the Student table and the Course table.

Output:

Student Table

+------------+---------+
| Student_ID | Name    |
+------------+---------+
| 101        | Rahul   |
| 102        | Amit    |
| 103        | Neha    |
+------------+---------+


Course Table

+-----------+-------------+------------+
| Course_ID | Course_Name | Student_ID |
+-----------+-------------+------------+
| 1         | MySQL       | 101        |
| 2         | PHP         | 102        |
| 3         | HTML        | 101        |
+-----------+-------------+------------+

Explanation:

The column Student_ID in the Course table is a Foreign Key. It stores values from the Primary Key column of the Student table. Because a Foreign Key Constraint is set on this column, MySQL ensures that the value entered in this column exists in the referenced table.

How Do You Set a Foreign Key Constraint on a Table in MySQL?

You can set a Foreign Key Constraint on a table by using the following two methods:

1. While creating a new table using the CREATE TABLE statement.

   
   

   Example:

   CREATE TABLE Course (
       Course_ID INT,
       Course_Name VARCHAR(50),
       Student_ID INT,
       FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID)
   );
   

   
   

   Steps:

   1. First, you write the CREATE TABLE statement, and then you write the table name.
   2. Open the parentheses (.
   3. Inside the parentheses, write the column names and their data types.
   4. For the column that you want to make the Foreign Key, write the FOREIGN KEY keyword with the column name.
   5. Write the REFERENCES keyword followed by the parent table name and Primary Key column name.
   6. Close the parentheses ) and end the query with a semicolon ;.
   7. Finally, execute the query to create the table with the Foreign Key.

2. By adding a Foreign Key to an existing table using the ALTER TABLE statement.

   
   

   Example:

   ALTER TABLE Course
   ADD FOREIGN KEY (Student_ID)
   REFERENCES Student(Student_ID);
   

   
   

   Steps:

   1. First, write the ALTER TABLE statement.
   2. Next, write the name of the existing table.
   3. Write the ADD FOREIGN KEY clause.
   4. Inside the parentheses (), write the column name that you want to make the Foreign Key.
   5. Write the REFERENCES keyword with the parent table and Primary Key column.
   6. Close the query and end it with a semicolon ;.
   7. Finally, execute the query to add the Foreign Key to the existing table.

Advantages of a Foreign Key

• Maintains Relationship Between Tables

  A Foreign Key Constraint creates a relationship between two tables that helps MySQL connect related data stored in different tables.

• Maintains Data Integrity

  A Foreign Key Constraint prevents invalid data from being entered into a table. It ensures that the value does exist in the referenced table.

• Prevents Invalid Records

  A Foreign Key Constraint does not allow values that do not exist in the referenced Primary Key column.

• Improves Data Management

  When tables are connected using Foreign Keys, MySQL can manage related data more efficiently.

Common Mistakes When Setting a Foreign Key Constraint in MySQL

The following are common mistakes that you should avoid when setting the Constraints.

1. To Reference a Wrong Column

   In the parent table, you should not reference a column that does not exist because MySQL cannot create a Foreign Key relationship with a non-existing column.

2. To Use Different Data Types

   You should keep the same data type for the Foreign Key column as the referenced Primary Key column.

3. To Insert Invalid Values

   You should not insert a value into the Foreign Key column because that value does not exist in the referenced table.

4. To Forget the REFERENCES Clause

   When you create a Foreign Key, you should not forget to indicate the REFERENCES clause in the CREATE TABLE query because it defines which column of the parent table the Foreign Key is connected to.

5. To Delete Parent Records Incorrectly

   When you delete a record from the parent table, it can also affect the related records in the child table. So, whenever you delete a record from the parent table, you should delete it only when required.

6. Choose the Incorrect Data Type

   Both the Foreign Key column and the Primary Key column's data types should be the same.

7. To Create Multiple Incorrect Relationships

   You should create relationships between tables in a database, but remember that you should not create unnecessary relationships.

What is a Composite Foreign Key Constraint and Why is it used?

A Composite Foreign Key Constraint is set on two or more columns in a table. It creates a relationship between the combined values of these columns and the combined values of the Primary Key or Unique Key in another table.

It is used when a single column cannot correctly reference a record in another table. In such cases, two or more columns whose combined values match the referenced Primary Key or Unique Key are used together to create a Composite Foreign Key.

Syntax:

CREATE TABLE child_table
(
    column1 datatype,
    column2 datatype,
    column3 datatype,

    FOREIGN KEY (column1, column2)
    REFERENCES parent_table (column1, column2)
);

Here,

➤ child_table → identifies the name of the child table.

➤ FOREIGN KEY (column1, column2) → column1 and column2 are the columns that together form the Composite Foreign Key.

➤ REFERENCES parent_table (column1, column2) →identifies the Primary Key or Unique Key columns in the parent table that are referenced by the Composite Foreign Key.

Example:

The following example creates an Order_Details table. In this table, Order_ID and Product_ID are combined to create a Composite Foreign Key. This ensures that the combined values of these two columns match the combined values of the Composite Primary Key in the Orders table.

CREATE TABLE Orders
(
    Order_ID INT,
    Product_ID INT,

    PRIMARY KEY (Order_ID, Product_ID)
);

CREATE TABLE Order_Details
(
    Order_ID INT,
    Product_ID INT,
    Quantity INT,

    FOREIGN KEY (Order_ID, Product_ID)
    REFERENCES Orders (Order_ID, Product_ID)
);

CREATE TABLE Orders
(
    Order_ID INT,
    Product_ID INT,

    PRIMARY KEY (Order_ID, Product_ID)
);

CREATE TABLE Order_Details
(
    Order_ID INT,
    Product_ID INT,
    Quantity INT,

    FOREIGN KEY (Order_ID, Product_ID)
    REFERENCES Orders (Order_ID, Product_ID)
);

Output:

 After executing the above queries, the Orders and Order_Details tables are created successfully.

The following table shows how records can be stored in the Order_Details table.

+----------+------------+----------+
| Order_ID | Product_ID | Quantity |
+----------+------------+----------+
| 101      | 1          | 5        |
| 101      | 2          | 3        |
| 102      | 1          | 8        |
+----------+------------+----------+

Explanation:

In the above table, Order_ID and Product_ID together create the Composite Foreign Key.
Both columns combined reference the matching record in the Orders table.
Therefore, the same combination of Order_ID and Product_ID must already exist in the Orders table before it can be added to the Order_Details table.