To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
|
