Syntax:

When creating a table, you can define a foreign key using the FOREIGN KEY constraint.

                                  
                                    CREATE TABLE table_name1 (
    column1 datatype1 PRIMARY KEY,
    column2 datatype2,
    ...
);

CREATE TABLE table_name2 (
    columnA datatypeA,
    columnB datatypeB,
    ...
    FOREIGN KEY (columnA) REFERENCES table_name1(column1)
);
                                  
                                

Example:

Let's create two tables, students and courses, and establish a foreign key relationship:

                                  
                                    CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    major VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(50),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);
                                  
                                

In this example:

• The students table has a primary key student_id.
• The courses table has a primary key course_id and a foreign key student_id that references the student_id in the students table.

Actions on Foreign Key Relationships:

ON DELETE CASCADE:

If a record in the referenced table is deleted, the corresponding records in the table with the foreign key will be automatically deleted.

                                
                                    CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(50),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE
);
                                
                              

ON UPDATE CASCADE:

If the primary key in the referenced table is updated, the corresponding foreign key values in the table with the foreign key will be automatically updated.

                                  
                                    CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(50),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON UPDATE CASCADE
);                                    
                                  
                                

Composite Foreign Key:

A foreign key can consist of multiple columns, forming a composite foreign key.

                                  
                                    CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id, product_id) REFERENCES orders(order_id, product_id)
);
                                  
                                

In this example, the order_details table has a composite foreign key referencing the composite primary key in the orders table.

Tips:

• Ensure that the data type of the foreign key matches the data type of the referenced primary key.
• Use meaningful column names and maintain consistency in naming conventions.
• Understand the impact of actions like ON DELETE CASCADE and ON UPDATE CASCADE on your data.