471,092 Members | 1,166 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Setting foreign key on insert

INSERT INTO X ...... ( A, B, C )
INSERT INTO Y ...... ( J, K, L )

If Y has a foreign key M which is the primary key D of X,
is there an easy and/or efficient way to have SQL Server
assign D, and tell us what it is so we can add M to the
list for Y ?

I know I can select D where A, B, C but I wondered about
other tricks.

--
Wes Groleau
Alive and Well
http://freepages.religions.rootsweb.com/~wgroleau/
Dec 2 '06 #1
1 11937
Wes Groleau (gr**********@freeshell.org) writes:
INSERT INTO X ...... ( A, B, C )
INSERT INTO Y ...... ( J, K, L )

If Y has a foreign key M which is the primary key D of X,
is there an easy and/or efficient way to have SQL Server
assign D, and tell us what it is so we can add M to the
list for Y ?

I know I can select D where A, B, C but I wondered about
other tricks.
The first question is: why would you want SQL Server to assign the
primary key?

Normally, the primary key of tbe table is to be found in the data. For
instance, if you have a table EmployeeProjects which specifies which
employee that are members of which projects, then that table would
have a primary of (EmployeeID, ProjectCode), and there would not be
any one-column key.

Thus, in the example above, the columns A, B and C would be the primary
key in X, and they would appear in Y as well,

There are of course cases where a system-generated surrogate key is
called for. For instance, the natural key may be difficult to determine
exactly, or it may be mutable. In the cases of multi-column keys, you
may sometimes prefer to introduce a surrogate key to reduce the size
of foreign keys in referring tables. Personally, I've more or less adding
surrogate keys of this reason to my tables, because I've seen cases
where it did more harm than benefit.

If you need to use surrogate keys, it's often best to roll your own:

BEGIN TRANSACTION

SELECT @D = coaelsce(MAX(D), 0) + 1 FROM X WITH (UPDLOCK, HOLDLOCK)

INSERT X (A, B, C, D, ...)
VALUES (..., @D, ...)

INSERT Y (J, K, L, M, ...)
VALUES(..., @D, ...)

COMMIT TRANSACTION

This can easily be extended if you insert more than one row at a time.

To have SQL Server to generate the surrogate key for you, you can use
the IDENTITY property. This is particularly a good idea, if you expect
plenty of concurrent inserts. The code above serlializes parallel
inserts on the SELECT statement. In this case you do:

BEGIN TRANSACTION

INSERT X (A, B, C, ...)
VALUES (...)

SELECT @D = scope_identity()

INSERT Y (J, K, L, M, ...)
VALUES(..., @D, ...)

COMMIT TRANSACTION

But this only works for single-row inserts. For multi-row inserts, you
are better off mapping from the natural keys in X. And if there are no
natural keys in X to map back to, you are in trouble. Overlall, there
several places where IDENTITY gives you issues when rolling your own
does not, so I recommend that you only use it when concurrency calls
for it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 2 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeremiah Jacks | last post: by
1 post views Thread by Thomas T. Thai | last post: by
5 posts views Thread by coosa | last post: by
reply views Thread by Frank Swarbrick | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.