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 insert data in two tables with foreign key in sql server

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:31

Introduction:
Inserting data in two tables with foreign key in sql server needs extra attention



Main Content:

I have not found a single query to insert a row in multiple tables.

So if there is a foreign key relationship between two tables

you should insert into the parent table first,

because that row has to be there to satisfy the foreign key constraint in the child table.


Now you want both the insert to happen or none to happen so this is a clear case of transaction.

use the two inserts inside transaction and you are done.


consider the two tables with foreign key constraint as below


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]




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]


GO

USE [TestDataBase]

GO

ALTER TABLE [dbo].[EmployeeDesc]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDesc_EmployeeMaster_EmpCodeDesc] FOREIGN KEY


([EmpCode_Desc])

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


So do your inserts in the two tables safely using transaction as below


BEGIN TRANSACTION


--First Statement

insert into EmployeeMaster (EmpName,EmpCode) values(''a'',6)


IF @@ERROR <> 0

 BEGIN

    -- Rollback the transaction

    ROLLBACK


    -- Raise an error and return

    RAISERROR (''Error in inserting into EmployeeMaster'', 16, 1)

    RETURN

 END


--Second statement

insert into EmployeeDesc (EmpCode_Desc,EmpDesc) values(6,''desc1'')


IF @@ERROR <> 0

 BEGIN

    -- Rollback the transaction

    ROLLBACK


    -- Raise an error and return

    RAISERROR (''Error in inserting into EmployeeDesc'', 16, 1)

    RETURN

 END


COMMIT











Conclusion:
Thus for inserting data into two tables with foreign we can use transaction for safety



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)