MySQL – Foreign Key
Table of content
- Creating MySQL Foreign Key
- Creating Foreign Key on Existing Column
- Dropping MySQL Foreign Key
- Primary Key vs Foreign Key
- Creating Foreign Key Using Client Program
”;
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
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 −
$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!
”;