Foreign Key Part I

In this blog, we’ll discuss about Foreign Key.

Note : Here we are assuming the 2 tables.Example : Multiple employees can work in 1 department. Here DepartmentID is the Primary Key in Department table(1st table) and DepId is the Foreign Key (referencing) in Employee table (2nd table) referenced to Department table.

  1. Foreign key is use for setting the relation between 2 or more tables.
  2. 1 table can have multiple Foreign keys.
  3. We can define a maximum of 253 Foreign keys in a table or a table can have maximum 253 columns use as referenced for Foregin key in other table.
  4. To set a Foreign key, we need below column property to be satisfied
    1. Column in both the tables must have same datatype and length.
    2. Column of the 1st table with which the second table want to refer for the relation must be Primary Key or Unique Key.
    3. Column in the second table must be set to NOT NULL which is used as a Foreign key in that table. (Default property)
  5. After setting the Foreign key relation between 2 tables, below points are in effect
    1. No values, except those that are present in the referenced table (1st table) key column are allowed to be inserted / updated in the referencing table (2nd table). (Default property)
    2. Deletion/Update of the column value in the referenced table (1st table) key column are not allowed. (Default property)
  6. Foreign key allows duplicate values in the referencing table (2nd table).
  7. Foreign key can be either disable or removed. ( It is not possible with Primary Key / Unique Key).
  8. Foreign key cannot be defined in Temp table (both Local & Global) or Temp Variable. (Note : Interesting point is, SQL server create the table successfully (Local/Global/Temp Variable) without Foreign key with a message “Skipping FOREIGN KEY constraint ‘#Tablename’ definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.)
  9. Any need to change the column’s datatype or need to drop the column (Column which is having the Foreign key) entirely will require first DROP of the FOREIGN constraint on the column prior to your action.

Examples

–Table A
create table Department
(
DepartmentID int Primary key,
DepName varchar(50)
)
–Table C
create table Gender
(
GID int primary key,
GenderType varchar(20)
)

–Example1: Table B with foreign key references to Table A
create table Employee1
(
      EmpId int primary key,
      DepId int foreign key references Department(DepartmentID),
      Name varchar(50),
     Gender char(1),
)
–Example2: Table B with foreign key references to Table A
create table Employee1
(
       EmpId int primary key,
       DepId int,
       Name varchar(50),
       Gender char(1),
       Foreign key(DepId) references Department(DepartmentID)
)
–Example3: Table B with foreign key references to Table A with constraint keyword & create a foreign key with name FK_Department_DepID
create table Employee1
(
         EmpId int primary key,
         DepId int,
        Name varchar(50),
        Gender char(1),
       CONSTRAINT FK_Department_DepID FOREIGN KEY(DepId) REFERENCES            Department (DepartmentID)
)
–Example4: Table B with foreign key references to Table A create outside table
create table Employee1
(
    EmpId int primary key,
    DepId int,
     Name varchar(50),
     Gender char(1)
)
–Without constraint keyword and constraint name
ALTER TABLE [dbo].[Employee1] WITH CHECK ADD FOREIGN KEY([DepId]) REFERENCES [dbo].[Department] ([DepartmentID])
–With constraint keyword and constraint name
ALTER TABLE [dbo].[Employee1] WITH CHECK ADD CONSTRAINT [FK_Department_DepID] FOREIGN KEY([DepId]) REFERENCES [dbo].[Department] ([DepartmentID])

–Example5: Table B with multiple foreign key references to Table A and Table C
create table Employee1
(
    EmpId int primary key,
    DepId int foreign key references Department(DepartmentID),  
     Name varchar(50),
     Gender char(1) foreign key references Gender(GID),
)

–Example6A:  Table A with Composite Primary key
create table DepartmentDetail
(
  DepartmentID int not null,
  HODID int not null,
  primary key(DepartmentID,DepName)
)

–Example6A: Table B with Composite foreign key references to Table A 
create table Employee1
(
  EmpId int primary key,
  DepId int,
  HODID int,
  Name varchar(50),
  Foreign key(DepId,HODID) references DepartmentDetail(DepartmentID,HODID)
)

To drop or remove the Foreign constraint , we need to provide the constraint name. If the constraint name is auto generated then get it GUI from the left side in SSMS under the table => Goto constraint => click on the constraint and drag it over the command window.

ALTER TABLE [dbo].[Employee1] DROP CONSTRAINT [FK_Department_DepID]

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Click here to read the next part :– Foreign Key Part II

Advertisements

2 thoughts on “Foreign Key Part I

  1. Pingback: Foreign Key Part II | DATABASE
  2. Pingback: Integrity Constraint in SQL | DATABASE

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s