SQL Foreign Key Constraint | Foreign Key In SQL Example
SQL foreign key is used to form a link between two tables, which makes it a referencing key. The foreign key is a constraint, which is a column or a combination of the column which is used to point the primary key of another table. If the table has the primary key defined on any field(s), then a user cannot have the two records having the same value of that field(s).
SQL Foreign Key Constraint
The primary purpose of the foreign key is that only those values will appear in the columns which are present in the primary key table and a foreign key is a referencing key in one table, the foreign key must match an existing primary key in the referenced table. This enforcement of foreign key is known as referential integrity.
Let’s understand the use of a foreign key with an example.
We are going to create a foreign key with create keyword.
See the following syntax.
Create table table_name( Column1 datatype, Column2 datatype, …….., Column(n) datatype, Constraint (constraint name) FOREIGN KEY [column1, column2…] REFERENCES [primary_key_table] (column_list_of_primary_key_table) ….);
- Table_name: Name of the table
- Column(n) datatype: Name of the column associated with its datatype.
- Constraint: This is a keyword, and this is optional.
- Constraint name: It is a constraint name defined by a user.
- Primary_key_table: This a table where the primary key resides.
- column_list_of_primary_key_table: List of columns that are used for making the primary key in a table.
See the following query.
Create table customers ( Id integer primary key, Name varchar(20), Age integer, Address varchar(25), Salary integer);
In the above statements, we have created a customers table with ID as a primary key.
Let’s create a Table which will be having foreign keys.
Create table orders ( Id integer, Date date, Cust_id integer foreign key references Customers(Id), Amount integer, Primary key(Id));
In the above statements, we have created the orders table with ID as primary key and Cust_id as reference key, i.e. a foreign key which is referring to the primary key of the customers table.
#Creating a foreign key using ALTER statement
This is used when a table is created without making any column as a foreign key. Using the alter statement, we can create a foreign key. Then to do that use the following Syntax.
Alter table table_name ADD foreign key(column) references refer_table_name(Primary_key_of_refer_table);
- Table_name: Name of the table which will contain the foreign key.
- ADD foreign key: Used for creating a foreign key.
- Foreign key(Column): Used for creating a column which will be the foreign key.
- Refer_table_name: Name of the table which will be referred.
- Primary_key_of_refer_table: Primary key of the table which is referred to.
Consider previously created table orders. Suppose that the orders table doesn’t contain the foreign key. So, to make a foreign key use the following statements.
Let’s create it.
Alter table orders ADD foreign key(Cust_id) REFERENCES CUSTOMERS(Id);
By the above statements, a foreign key name cust_id is created, which is referring to the primary key, an, i.e. ID of customers table.
#Dropping A Foreign Key
Suppose we want to delete the foreign key. Then to do that use the following syntax.
ALTER TABLE table_name DROP foreign key foreign_key_name;
- table_name: Name of the table which is containing the primary key.
- DROP: Name of the keyword which is used for dropping.
- Foreign key: Name of the keyword which will identify the foreign key.
- Foreign_key_name: Name of the foreign key.
Suppose, we want to delete previously created foreign key.
Use the following statements.
ALTER TABLE orders DROP foreign key cust_id;
The above statement has deleted the orders table foreign key which was cust_id.
Finally, SQL Foreign Key Constraint Example is over.