Foreign Key in SQL

Create A Foreign Key

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between   two tables. foreign key can be created by using FOREIGN KEY constraint when you create or modify a table.

Create a ForignKey constraints

Create two tables with following script:

GO

/****** Object:  Table [dbo].[EmployeeMaster]    Script Date: 12/15/2012 14:13:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON

GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmployeeMaster](
      [EmployeeId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
      [EmployeeCode] [varchar](30) NOT NULL,
      [FirstName] [varchar](100) NOT NULL,
      [LastName] [varchar](100) NOT NULL,
      [ContactNo] [varchar](50) NOT NULL,
      [DateOfBirth] [datetime] NULL,
      [UserName] [varchar](100) NOT NULL,
      [Password] [varchar](100) NOT NULL,
      [EmailId] [varchar](150) NOT NULL,
      [ServiceAgreementNo] [varchar](150) NULL,
      [DesignationID] [int] NULL,
 CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED
(
      [EmployeeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

 

/****** Object:  Table [dbo].[DesignationMaster]    Script Date: 12/15/2012 14:04:23 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DesignationMaster](

      [DesignationId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
      [DesignationCode] [varchar](20) NOT NULL,
      [DesignationDesc] [varchar](50) NOT NULL,
      [IsActive] [bit] NOT NULL,
      [CreatedDate] [datetime] NULL,

      [CreatedBy] [numeric](10, 0) NULL,

      [ModifiedDate] [datetime] NULL,
      [ModifiedBy] [numeric](10, 0) NULL,
 CONSTRAINT [PK_DesignationMaster] PRIMARY KEY CLUSTERED
(
      [DesignationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
GO

SET ANSI_PADDING OFF

GO

/*Now create a Forign key to table department with column Department ID */

SET ANSI_PADDING OFF

GO

Right click on employee table , Click Design :

 

 

 

 

 

 

Now click add ,

Click tables and column specification

Now select designation master table from list

It will pop up all columns from DesignationMaster table

Now select DesignationID colum from Primary key table and foreign key column would be EmployeeID from employeeMaster table

Click OK.              

Alternatively by script you can use this syntax

/*Now create a Forign key to table department with column Department ID */

 

SET ANSI_PADDING OFF

GO

 

ALTER TABLE [dbo].[EmployeeMaster]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeMaster_EmployeeMaster] FOREIGN KEY([EmployeeId])

REFERENCES [dbo].[EmployeeMaster] ([EmployeeId])

GO

ALTER TABLE [dbo].[EmployeeMaster] CHECK CONSTRAINT [FK_EmployeeMaster_EmployeeMaster]

GO