Prev Next
- FOREIGN KEY in one table is used to point a PRIMARY KEY in another table in SQL. FOREIGN KEY is used to maintain the data integrity between tables.
- It is also called as REFERENCE KEY.
- FOREIGN KEY acts as a link between tables.
- A table may have more than one FOREIGN KEY either on one column or multiple columns.
- When one column or multiple columns are defined as FOREIGN KEY in a table, then, those values will match with PRIMARY KEY of the other tables.
- PRIMARY KEY can’t have null values. FOREIGN KEY also should not have NULL values.
- If we try to insert/update duplicate values for the PRIMARY KEY column, then, the query will be aborted. Syntax for SQL FOREIGN KEY constraint is given below.
SQL Syntax for FOREIGN KEY constraint:
| SQL Syntax for FOREIGN KEY constraint | PRIMARY KEY (TABLE1): – This is one table CREATE TABLE table_name1 ( column_name1 data_type(size) NOT NULL PRIMARY KEY, column_name2 data_type(size) NOT NULL, column_name3 data_type(size), etc… ); FOREIGN KEY (TABLE2): – This is another table CREATE TABLE table_name2 ( column_name1 data_type(size) NOT NULL PRIMARY KEY, column_name2 data_type(size) NOT NULL, column_name3 data_type(size), column_name4 data_type(size), FOREIGN KEY (column_name3) REFERENCES table_name1(column_name1), etc… ); |
Example for how to use SQL FOREIGN KEY constraint:
- Please execute below query in SQL to create a table with PRIMARY KEY constraint.
| CREATE TABLE student1 (Student_ID int NOT NULL PRIMARY KEY, Student_name varchar(255) NOT NULL, City varchar(255), Age int); |
- Now, a new table called “student1” is created and the field “Student_ID” is defined as PRIMARY KEY.
- Please execute below query in SQL to create a table with a FOREIGN KEY constraint.
| CREATE TABLE student2 (ID int NOT NULL PRIMARY KEY, S_ID int NOT NULL, Department varchar(255), College varchar(255), Rank int, FOREIGN KEY (S_ID) REFERENCES student1(Student_ID),); |
- Now, a new table called “student2” is created and the field “ID” is defined as PRIMARY KEY. And, the field ‘S_ID’ is defined as FOREIGN KEY which points the field ‘Student_ID’ in the table student1.
- Assume that some data are inserted in above 2 tables. Please check below how data will be integrated into these tables. Below are sample data for your understanding.
Table1:
Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Table2:
Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows
Note:
- Whenever a new data is inserted or updated in student2 table where FOREIGN KEY is defined, S_ID value should match with PRIMARY KEY value in stundet1 table. Else, the data can’t be inserted or updated.
- Also, when we delete a data from student1 table, the respective PRIMARY KEY value should not be available in FOREIGN KEY in student2 table. Then only, we can delete the value. Else, the data can’t be deleted.
- A table in which FOREIGN KEY is defined will act as child table and another table which one a FOREIGN KEY is referring will act as a parent table. So, Student1 table acts as a parent table and student2 table acts as a child table. Always, child data can’t exist without parent data.
How to ALTER a FOREIGN KEY constraint?
- To ALTER a FOREIGN KEY constraint on an existing table, you can use below Syntax.
Syntax:
| ALTER table table_name ADD FOREIGN KEY (column_name) REFERENCES table_name(column_name); |
Example:
| ALTER table Student2 ADD FOREIGN KEY (Rank) REFERENCES student1(Age); |
How to DROP a FOREIGN KEY constraint?
- To DROP a FOREIGN KEY constraint on an existing table, you can use below Syntax.
Syntax:
| ALTER table table_name DROP FOREIGN KEY; |
Example:
| ALTER table Student2 DROP FOREIGN KEY; |