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)

)

其中的ord_num订单号设为标识列，随着一条记录的插入，订单号会主动增值……

实用的案例，再建一个销售详单表

create table salesdetail (

stor_id char(4) not null      references stores(stor_id), 
ord_num numeric(6,0)      references sales(ord_num), 

title_id tid not null      references titles(title_id), 

qty smallint not null, 

discount float not null

)

使用时：

begin tran 

insert sales values ("6380", "04/25/97") 

insert salesdetail values ("6380", @@identity, "TC3218", 50, 50) 

commit tran



@@identity用来自动获取当前标识列的值，这样，就可以保证这一条销售明细在两个相关表之间的联系是完整的。

11.0大约是1997年前后的版本，据我所知，微软SQL Server, DB2 UDB, MySQL, 甚至Oracle收购的RDB中都有这个功能。而这15年来，在Oracle里面想要实现这样的功能，只能用另外的功能sequence+trigger来mimick（模拟）：

-----------------------------------------------
-- Create the table for testing
-----------------------------------------------
CREATE TABLE IdentityTest (
   Id   NUMBER        NOT NULL PRIMARY KEY,
   Text VARCHAR2(100) NOT NULL
);

-----------------------------------------------
-- Create a sequence
-----------------------------------------------
CREATE SEQUENCE SeqIdentityTest
   START WITH 1
   INCREMENT BY 1
   NOCYCLE;

-----------------------------------------------
-- Create a trigger to add a unique value to the id
-----------------------------------------------
CREATE OR REPLACE TRIGGER IdentityTest_Insert
BEFORE INSERT ON IdentityTest
FOR EACH ROW
BEGIN
   SELECT SeqIdentityTest.NEXTVAL
   INTO :NEW.Id
   FROM Dual;
END;
/

-----------------------------------------------
-- Test insert
-----------------------------------------------
INSERT INTO IdentityTest (Text) VALUES ('Test2');
SELECT * FROM IdentityTest;

   ID TEXT
----- ----------
    1 Test2