Course
Foreign Key
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
Foreign Key
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 −
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.
Creating Foreign Key Using Client Program
We can also apply a Foreign Key constraint on a table field using a client program.
Syntax
PHPNodeJSJavaPython
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);
Example
Following are the programs −
PHPNodeJSJavaPython
$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