Referencing IDENTITY columns

When you create a table column that references an IDENTITY column, as with any referenced column, make sure it has the same datatype definition as the IDENTITY column. For example, in the pubs3 database, the sales table is defined using the ord_num column as an IDENTITY column:

create table sales
(stor_id char(4) not null
     references stores(stor_id),
ord_num numeric(6,0) identity,
date datetime not null,
unique nonclustered (ord_num))

The ord_num IDENTITY column is defined as a unique constraint, which it needs to reference the ord_num column in salesdetail. salesdetail is defined as follows:

create table salesdetail
(stor_id char(4) not null
     references storesz(stor_id),
ord_num numeric(6,0)
     references salesz(ord_num),
title_id tid not null
     references titles(title_id),
qty smallint not null,
discount float not null)

An easy way to insert a row into salesdetail after inserting a row into sales is to use the @@identity global variable to insert the IDENTITY column value into salesdetail. The @@identity global variable stores the most recently generated IDENTITY column value. For example:

begin tran
insert sales values ("6380", "04/25/97")
insert salesdetail values ("6380", @@identity, "TC3218", 50, 50)
commit tran

This example is in a transaction because both inserts depend on each other to succeed. For example, if the sales insert fails, the value of @@identity is different, resulting in an erroneous row being inserted into salesdetail. Because the two inserts are in a transaction, if one fails, the entire transaction is rejected.