Free Jobs
Reference




Dot Net Forums: ASPNET (77) C#.NET (39) VB.NET (8) AJAX (21) Silver Light (1) WCF (10) WPF (1) WWF (1) .NET 3.5 (3) SQL SERVER (38) Other (3)

Topic: How to create and drop foreign key relation between two existing Tables in SQLServer

To get the Posted Questions details daily in your mail Click Here

Share this Post on Facebook/Twitter/Orkut Bookmark and Share





User: dhiraj5079 Post Date: 26 May 2011 03:29

Introduction:
create and drop foreign key relation between two existing Tables in SQLServer is very common among SQL Server Developers



Main Content:

Suppose there are two tables named EmployeeMaster and EmployeeDesc

and you want to create a foreign key relation between EmpCode of EmployeeMaster  and EmpCode_Desc of

EmployeeDesc Table where EmpCode is primary key in the EmployeeMaster Table.



Create the two tables as below:


1.CREATE TABLE [dbo].[EmployeeMaster](

 [SNo] [int] IDENTITY(1,1) NOT NULL,

 [EmpName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

 [EmpCode] [int] NOT NULL,

 CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED

(

 [EmpCode] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]


2.CREATE TABLE [dbo].[EmployeeDesc](

 [Sno] [int] IDENTITY(1,1) NOT NULL,

 [EmpCode_Desc] [int] NOT NULL,

 [EmpDesc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]




Use the below query to create a Foreign key relation between EmpCode of EmployeeMaster

and EmpCode_Desc of EmployeeDesc Table


ALTER TABLE EmployeeDesc ADD

      CONSTRAINT FK_EmployeeDesc_EmployeeMaster_EmpCodeDesc

      FOREIGN    KEY (EmpCode_Desc)

      REFERENCES EmployeeMaster(EmpCode)

GO


To drop the foreign key created above use the below query


ALTER TABLE EmployeeDesc DROP CONSTRAINT FK_EmployeeDesc_EmployeeMaster_EmpCode





Conclusion:
Thus we can create and drop foreign key realtion between two existing tables
and note that the name of the coloumns in two tables need not be the same



About Writer:
Hi,I am IT Professional From Mumbai


Writer's Link: http://

Writer's Email:







Comments:
Post Comment





Similar Articles Top ArticlesLatest Articles Other Articles by this Author







www.freejobsreference.com Copyright © 2009-2011 | All Rights Reserved
All content of this website is protected by copyright laws and should not be reproduced in any form or by any means without written permission
(Best viewed in IE 8.0+ or Firefox 3.0+ at resolution of 1024 * 768 or higher)