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.

Dig Deeper on Microsoft SQL Server Installation