MySQL - Foreign Key

Table of content

In MySQL, a Foreign Key is a column (or combination of columns) in a table whose values match the values of a Primary Key column in another table. Thus, using the Foreign key, we can link two tables together.

A Foreign Key is also known as a Referencing key of a table because it can reference any field defined as unique.

The table that has the primary key is known as the parent table and the key with the foreign key is known as the child table.

In addition to linking to tables, the Foreign Key constraint ensures referential integrity by preventing changes to data in the primary key table from invalidating the link to data in the foreign key table. i.e, a Foreign key prevents operations, like "dropping the table", that would eliminate the connection between two tables.

Creating MySQL Foreign Key

We can create a Foreign Key on a MySQL table using the CONSTRAINT... FOREIGN KEY... REFERENCES keyword in the CREATE TABLE statement.

Syntax

Following is the syntax to add Foreign Key constraints on a column of a table −

CREATE TABLE table2(
   column1 datatype,
   column2 datatype,
   ...
   CONSTRAINT constraint_name 
   FOREIGN KEY (column2) 
   REFERENCES table1(column1)
);

Example

Let us create a table named CUSTOMERS using the CREATE TABLE statement −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) UNIQUE,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

To demonstrate the foreign key we need two tables so lets create another table as −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
   CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID)
   REFERENCES CUSTOMERS(ID)
);

Verification

To verify if the foreign key is created, let us drop the CUSTOMERS table without dropping the ORDERS table using the following statement −

DROP TABLE CUSTOMERS;

An error is displayed as follows −

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.

Creating Foreign Key on Existing Column

We can also create a Foreign Key constraint on a column of an existing table using the ALTER TABLE... ADD CONSTRAINT statement.

Syntax

Following is the syntax to add foreign key constraint on an existing table −

ALTER TABLE table_name2 
ADD CONSTRAINT constraint_name 
FOREIGN KEY(column_name2) 
REFERENCES table_name1(column_name1);

Example

Following is the MySQL query to add a foreign key constraint FK_CUSTOMERS on an existing column of an existing table ORDERS referencing primary key of CUSTOMERS table −

ALTER TABLE ORDERS 
ADD CONSTRAINT FK_CUSTOMERS 
FOREIGN KEY(CUSTOMER_ID) 
REFERENCES CUSTOMERS(ID);

Output

The table structure displayed will contain a FOREIGN KEY constraint on the CUSTOMER_ID column as shown −

Field Type Null Key Default Extra
OID int NO NULL
DATE varchar(20) NO NULL
CUSTOMER_ID int NO MUL NULL
AMOUNT decimal(18,2) YES NULL

Verification

To verify if the foreign key we created on ORDERS is referenced to CUSTOMERS table or not, let us drop the CUSTOMERS table without dropping the ORDERS table using the following statement −

DROP TABLE CUSTOMERS;

An error is displayed as follows −

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.

Dropping MySQL Foreign Key

We can also drop the foreign key, created on a MySQL table, whenever it is no longer needed in that table. We can do this using the ALTER TABLE... DROP CONSTRAINT statement in MySQL.

Syntax

Following is the syntax to drop the foreign key from a table −

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example

Using the following MySQL query, we are dropping the foreign key constraint from a table −

ALTER TABLE CUSTOMERS DROP CONSTRAINT fk_customers;

Verification

Let us verify whether the foreign key is dropped or not by dropping the CUSTOMERS table using the following query −

DROP TABLE CUSTOMERS;

Primary Key vs Foreign Key

Even though both the primary key and foreign key refer to the same column, there are many differences to be observed in the way they work. They are listed below.

Primary Key Foreign Key
The Primary Key is always unique. The Foreign Key can be duplicated.
The Primary Key can not be NULL. The Foreign Key can be NULL.
A table can contain only one Primary Key. We can have more than one Foreign Key per table.

Creating Foreign Key Using Client Program

We can also apply a Foreign Key constraint on a table field using a client program.

Syntax

PHP NodeJS Java Python

To apply foreign key on a field through a PHP program, we need to execute the FOREIGN KEY keyword in CREATE statement using the mysqli function query() as follows −

$sql = 'CREATE TABLE customers(Customer_Id INT, Customer_Name VARCHAR(30), 
CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))';
$mysqli->query($sql);

To apply foreign key on a field through a JavaScript program, we need to execute the FOREIGN KEY keyword in CREATE statement using the query() function of mysql2 library as follows −

sql = `CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID) )`;
con.query(sql);

To apply foreign key on a field through a Java program, we need to execute the FOREIGN KEY keyword in CREATE statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))";
statement.execute(sql);

To apply foreign key on a field through a Python program, we need to execute the FOREIGN KEY keyword in CREATE statement using the execute() function of the MySQL Connector/Python as follows −

foreign_key_query = 'CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))'
cursorObj.execute(foreign_key_query)

Example

Following are the programs −

PHP NodeJS Java Python 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = 'CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), 
CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))';
if ($mysqli->query($sql)) {
    echo "foreign key column created successfully in customers table \n";
}
if ($mysqli->errno) {
    printf("Table could not be created!.
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

foreign key column created successfully in customers table

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //creating a column that is foreign key!
  sql = `CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID) )`;
  con.query(sql);

  //describe table details
  sql = "DESCRIBE TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[
    {
      id: 1,
      select_type: 'SIMPLE',
      table: 'customers',
      partitions: null,
      type: 'ALL',
      possible_keys: null,
      key: null,
      key_len: null,
      ref: null,
      rows: 1,
      filtered: 100,
      Extra: null
    }
]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ForeignKey {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Create a foreign key in the customer table...!;
         String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))";
         statement.execute(sql);
         System.out.println("Foreign key created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customer");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" "
                    +resultSet.getString(3)+ " "+ resultSet.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
Foreign key created successfully...!
Customer_Id int YES MUL
Customer_Name varchar(30) YES

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Create table 
foreign_key_query = '''CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))'''
cursorObj.execute(foreign_key_query)
connection.commit()
print("Foreign key column is created successfully!")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Foreign key column is created successfully!
Advertisements