Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using newid in SQL Server to insert records in parent-child tables

In SQL Server parent-child tables, the child records seem to have no information about the unique ID given by SQL to the parent record. Developer expert Andrew Novick explains how to address this problem.

In my project I have two ( parent-child) tables that are bound by a primary key (PK)/foreign key (FK) relation. To insert new records in my tables, I would like to use the newid() function (in the PK of the parent and FK of the child). While this would work fine with the parent, the child records seem to have no information about the unique ID given by SQL to the parent record.

Is there a way to get the required information so that I am able to insert records in both the parent and the child...

at the same time and keep the relation in place?

Yes. Uniqueidentier, also known as a GUID (Globally Unique ID), can be a very useful type for a primary key. To get the result that you require, the newid can be placed into a local variable in your SQL script and used to insert into both tables. Let's start with the tables and the foreign key:
 CREATE TABLE dbo.ParentTable (ParentKey uniqueidentifier PRIMARY KEY , DataCol1 varchar(10) , DataCol2 varchar(10) ) GO CREATE TABLE dbo.ChildTable ( ChildKey uniqueidentifier DEFAULT (NEWID()) , ParentKey uniqueidentifier , DataCol3 varchar(10) , DataCol4 varchar(10) ) GO ALTER TABLE dbo.ChildTAble ADD CONSTRAINT FK_ChildTable_ParentTable_ParentKey FOREIGN KEY (ParentKey) REFERENCES dbo.ParentTable(ParentKey) GO

Now to do the insert, the @myNewID is created and used in both inserts:

 DECLARE @myNewID uniqueidentifier select @myNewID = NEWID() INSERT INTO ParentTable (ParentKey, DataCol1, DataCol2) VALUES (@myNEWID, 'ABC', 'DEF') INSERT INTO ChildTable (ParentKey, DataCol3, DataCol4) VALUES (@myNEWID, 'GHI', 'JKL') go

Note that ChildTable's primary key is created by the default and is not supplied in the insert statement. It's a different uniqueidentifier. Because we don't need to use ChildKey in any other table it doesn't have to be created by the script.

 


Do you have comments on this Ask the Expert Q&A? Let us know.
This was last published in July 2005

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close