SQL FOREIGN KEY Tutorial

Example Tables

We will create two tables named departments and employees to demonstrate the SQL FOREIGN KEY constraint:

    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(100)
    );

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(100),
        department_id INT,
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
    );
    

Creating the Tables

To create the tables, use the following SQL commands:

    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(100)
    );

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(100),
        department_id INT,
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
    );
    

Result:

Command	CREATE TABLE departments (department_id INT PRIMARY KEY, department_name VARCHAR(100));
Result	A table named departments is created.
Command	CREATE TABLE employees (employee_id INT PRIMARY KEY, employee_name VARCHAR(100), department_id INT, FOREIGN KEY (department_id) REFERENCES departments(department_id));
Result	A table named employees is created with a foreign key constraint on department_id.

Inserting Data

To insert data into the tables, use the following SQL commands:

    INSERT INTO departments (department_id, department_name) VALUES (1, 'HR'), (2, 'Engineering');

    INSERT INTO employees (employee_id, employee_name, department_id) VALUES (1, 'John Doe', 1), (2, 'Jane Smith', 2);
    

Result:

Command	INSERT INTO departments (department_id, department_name) VALUES (1, 'HR'), (2, 'Engineering');
Result	Data is inserted into the departments table.
Command	INSERT INTO employees (employee_id, employee_name, department_id) VALUES (1, 'John Doe', 1), (2, 'Jane Smith', 2);
Result	Data is inserted into the employees table.

Important Considerations

When using foreign keys, ensure that:

• The referenced table and column exist.
• The data types of the foreign key column and the referenced column match.
• Proper indexing is in place for performance optimization.