KR (kr****@bastyr. edu) writes:
Let us say you have a parent/master table( Ex: purchase order) that
is generating number (primary key for the main table)using the seed
and increment specified. We need all the records of this table to be
in sequential order - i.e. we need all purchase orders to be in
sequence.
Do you permit gaps in the sequence? Since you talk about seed and
increment, I suspect that you are using IDENTITY. Beware that IDENTITY
is very likely to give you gaps, since if an INSERT fails, an IDENTITY
number is still consumed.
Now there are two different types purchase orders different
enough to have entity/tables of their own. So what are the downsides
of using the primary key generated in the main table which would
normally be a foreign key to the child table, as the actual primary
key in the child tables.
So you would have
CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY,
....
go
CREATE TABLE BlackOrders(Ord erID int NOT NULL
PRIMARY KEY REFERENCES Orders(OrderId) ,
....
go
CREATE TABLE WhiteOrders(Ord erID int NOT NULL
PRIMARY KEY REFERENCES Orders(OrderId) ,
....
That's a perfectly normal design, and quite a common way to address
supertypes and subtypes.
Joe Celko has a twist to this:
CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY,
OrderColour char(3)
CHECK (OrderColour IN ('BLK', 'WHT'))
....
UNIQUE(OrderId, OrderColour
go
CREATE TABLE BlackOrders(Ord erID int NOT NULL PRIMARY KEY,
OrderColour char(3)
DEFAULT 'BLK',
CHECK (OrderColour = 'BLK'),
...
FOREIGN KEY (OrderID, OrderColour)
REFERENCES Orders(OrderId, OrderColour),
....
In this way you also assures that BlackOrders really only have black
orders.
Note: since the hour is later, I'm tired at the end of the week etc,
I have left out constraint names. But I like to stress that best
practice is to name your constraints.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.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 
