A Foreign key is a key used to link two tables together. This is sometimes also called as a referencing key, usually a primary key table is considered as a parent table while a foreign key table is considered as a child table .
It is used to maintain the referential integrity of the data in one table to match values in another table.
The values of the FOREIGN KEY columns in each row of the referencing table have to match with the values of the corresponding primary key columns of a row in the referenced table
The structure and data type of PRIMARY KEY and FOREIGN KEY must be same.
Syntax
CREATE TABLE tablename ( col1 datatype1,ย col2 datatyp2,ย ย FOREIGN KEY [col1,col2...] REFERENCES [primary key table name] (column name of primary key table) ... );
Lets look at an example of Foreign Key in SQL Server.
Suppose you have table named Empdetails which have a primary key column on column empId .
Create table EmpDetails ( empid int primary key, empname varchar(100), empcode varchar(50) )
Lets chekc whether the primary key constraint is created or not on table, you can get the details of all the constraints on table using sp_help command.
sp_help EmpDetails
Create a foreign key constraintย
Lets create another table named EmpAddress, that will store the address of employees.
In this table we will create a foreign key that will reference to primary key column empId of table Empdetails.
The structure and data type of PRIMARY KEY and FOREIGN KEY column must be same.
Create table EmpAddress( RowId int primary key identity(1,1) , empId int references empDetails(Empid) , empaddress varchar(100) )
Lets verify whether a foreign key is created or not on table by using sp_Help command.
As you can see, a foreign key is created on table.
Lets understand the Primary key and foreign key relationship
As you can see for empId = 4, a EmpDetails table contains basic details of empid = 4 while EmpAddress table contains the address of this employee.ย
Both table can be linked together using empId column to get the complete details of employee .
Following is the output of both EmpDetails and EmpAddress tables.
To link both table together you can use SQL Server Join as given below.
Select emp.empid, emp.empname, emp.empcode, empadd.empaddressย from Empdetails emp inner join EmpAddress on emp.empid=empadd.empid
Foreign key to ensure the referential integrity of the data
The main purpose of using foreign key is to ensure the referential integrity of the data in one table to match values in another table. Table that have primary key is called as parent table, or primary key table while table that have foreign key is called as child table.
Here EmpDetails table is primary key table while EmpAddress table is Child table.
Lets assume primary key table (EmpDetails) is having the list of employees and empid column of this table is being used as a referenced column in child table that EmpAddress.
Later you delete any employee record directly from primary key table with out verifying that whether this employee record is being used by child table or not.
So in this case you lost the accuracy and consistency of data as data of employee still available in child table EmpAddress while employee is deleted from primary key table EmpDetails.
Therefore to avoid this situation, you need to use Foreign key to maintain the data referential integrity.
Lets look at an example how Foreign key ensure the referential integrity of the data between tables.
As you know primary key column empid in empdetails table is referenced by a foreign key column empid of EmpAddress table .ย ย
So when you try to delete a record for empId=4 directly from Empdetails table(primary key table) that is being used in EmpAddress table, it gives an error as shown below.
Delete from EmpDetails where empid =4
This is the way foreign key handle the referential integrity. You can not delete any record from primary key table directly untill it is being referenced by any child table.
So to delete the record for empId =4 from parent table first you need to delete this record from all the references table that is referecing this record such as delete record from EmpAddress table first, then only you can delete it from parent table EmpDetails .
So basically its act like a parent- child relationship, you can not delete any records from parent table if record is being referenced by any child table.
So to delete any reocrd from parent table, first you need to delete it from child reference table then only it can be deleted from parent table.ย In this way foreign key manage the data referential integrity.
Lets delete the record first from child table EmpAddress table.
Delete from EmpAddress where empId =4
Now you can eaisly delete the record from parent table EmpDetails table.
Delete from EmpDetails where empId =4
Drop foreign Key constraint from an existing table
To drop the foreign key from any table by using T- SQL query, you need to specify constraint name that you want to drop.
If you do not know the constraint name, you can use sp_help command, it lets you know all the constraint details with columns data type and structure of table.
As you can see foreign key constraint name is FK__EmpAddres__empId__66603565
Now use Alter table command to drop an existing constraint on table.
Alter table empaddress drop constraint FK__EmpAddres__empId__66603565
Next, you can check whether theย foreign key is removed from table or not, using sp_help command.
As you can see, there is no foreign key constraint on table, which means it is removed.
Creating a foreign Key constraint on an existing table
As you have just removed the foreign key from table, now EmpAddress table does not have any foreign key.
So to create a foreign key on an existing table, you can use Alter table command to add constraint.
ALTER TABLE empaddress ADD FOREIGN KEY (empId) REFERENCES empdetails (empId)
Lets check whether foreign key is created on table or not , using sp_help command.
As youcan see, foreign key is created on table.
Recommended for you
3,025 total views, 1 views today