|
|
Posted by Erland Sommarskog on 05/25/07 22:11
KR (kraman@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(OrderID int NOT NULL
PRIMARY KEY REFERENCES Orders(OrderId),
....
go
CREATE TABLE WhiteOrders(OrderID 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(OrderID 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|