SQL FOREIGN KEY Constraint

A FOREIGN KEY is a column used to establish and enforce a relationship between the data in two tables.

The SQL FOREIGN KEY is a column or a collection of columns in one table that refers to the PRIMARY KEY in another table.

The table carrying the foreign key is called the child table, and on the other hand, to table carrying the candidate key is called the referenced or parent table.

The FOREIGN KEY constraint is used to prevent acts that would damage links between tables.

The FOREIGN KEY constraint makes sure that the data being inserted into the foreign key column is one of the values contained in the table it points to.

The following diagram is showing the relationship between the "Employee" and "Department" table. You can see that the "dept_id" column of the "Employee" table matches the primary key column of the "Department" table. So, the "dept_id" column of the "Employee" table is the foreign key to the "Department" table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL statement will create a FOREIGN KEY on the "dept_id" of the "Employee" table that references the "dept_id" column of the "Department" table during the creation of the "Employee" table.

• For MySQL, MariaDB :

  CREATE TABLE employee (
    emp_id INT NOT NULL,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    birth_date DATE,
    hire_date DATE,
    salary DOUBLE(12, 2)
    dept_id INT,
  
    PRIMARY KEY(emp_id),
    FOREIGN KEY(dept_id) REFERENCES department(dept_id)
  );
  

• For Oracle, SQL Server:

  CREATE TABLE employee (
    emp_id INT NOT NULL PRIMARY KEY,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    birth_date DATE,
    hire_date DATE,
    salary DOUBLE(12, 2)
    dept_id INT FOREIGN KEY REFERENCES department(dept_id)
  );
  

If you want to give a name to the FOREIGN KEY constraint or to define a FOREIGN KEY constraint on multiple columns, use the following syntax:

• For MySQL, MariaDB, Oracle, SQL Server:

  CREATE TABLE employee (
    emp_id INT NOT NULL,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    birth_date DATE,
    hire_date DATE,
    salary DOUBLE(12, 2)
    dept_id INT,
  
    PRIMARY KEY(emp_id),
    CONSTRAINT employee_department_fk FOREIGN KEY(dept_id)   
    REFERENCES department(dept_id)
  );
  

SQL FOREIGN KEY on ALTER TABLE

The following SQL statement will create a FOREIGN KEY on the "dept_id" of the "Employee" table that references the "dept_id" column of the "Department" table when the "Employee" table is already created.

• For MySQL, MariaDB, Oracle, SQL Server:

  ALTER TABLE employee
    ADD FOREIGN KEY (dep_id) REFERENCES department(dept_id);
  

If you want to give a name to the FOREIGN KEY constraint or to define a FOREIGN KEY constraint on multiple columns, use the following syntax:

• For MySQL, MariaDB, Oracle, SQL Server:

  ALTER TABLE employee
    ADD CONSTRAINT employee_department_fk FOREIGN KEY(dept_id)                  
    REFERENCES department(dept_id);
  

DROP a FOREIGN KEY Constraint

You can drop a FOREIGN KEY constraint using the ALTER TABLE statement.

The following SQL statement will drop the FOREIGN KEY constraint from the "Employee" table.

• For MySQL, MariaDB :

  ALTER TABLE employee
    DROP FOREIGN KEY employee_department_fk;
  

• For Oracle, SQL Server :

  ALTER TABLE employee
    DROP CONSTRAINT employee_department_fk;