SQL FOREIGN KEY

SQL FOREIGN KEY Constraint

The SQL FOREIGN KEY constraint is used to link two tables together. This command is essential for maintaining referential integrity by ensuring that the values in one table correspond to values in another table. A foreign key in one table points to a primary key in another table, creating a relationship between the two tables.

---

Syntax

-- To create a foreign key when creating a table
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES other_table (primary_key_column)
);

-- To add a foreign key to an existing table
ALTER TABLE table_name
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES other_table (primary_key_column);

• CREATE TABLE: This is the SQL keyword used to create a new table.
• ALTER TABLE: This is the SQL keyword used to modify an existing table.
• FOREIGN KEY: This is the SQL keyword used to define a foreign key.
• REFERENCES: This is the SQL keyword used to specify the table and column that the foreign key points to.
• column1, column2, ...: These specify the names of the columns in the table.
• datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.
• table_name: This specifies the name of the table to create or modify.
• column_name: This specifies the name of the column to set as the foreign key.
• other_table: This specifies the name of the table that the foreign key points to.
• primary_key_column: This specifies the name of the column in the other table that is the primary key.
• fk_name: This specifies the name of the foreign key constraint.

---

Example

Let's go through a complete example that includes creating two related tables, defining a foreign key constraint, and inserting data into the tables.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating the Primary Table

In this step, we create a table named departments with a primary key.

USE example_db;

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

Here, we define the departments table with columns for department_id and department_name. The department_id column is set as the primary key.

Step 3: Creating the Foreign Table

In this step, we create a table named employees and define a foreign key constraint that references the departments table.

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Here, we define the employees table with columns for employee_id, first_name, last_name, email, hire_date, and department_id. The department_id column is set as a foreign key that references the department_id column in the departments table.

Step 4: Inserting Data into the Tables

This step involves inserting some sample data into the departments and employees tables.

INSERT INTO departments (department_name) VALUES ('Human Resources'), ('Finance'), ('IT'), ('Marketing');

INSERT INTO employees (first_name, last_name, email, hire_date, department_id) VALUES
('John', 'Doe', 'john.doe@example.com', '2023-01-01', 1),
('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01', 2),
('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01', 3),
('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01', 4);

Here, we insert data into the departments table and then insert data into the employees table, ensuring that each employee is linked to a department through the department_id foreign key.