Posted by --CELKO-- on 05/27/07 13:17

>> We need all the records [sic] of this table to be in sequential order <<

Rows are not records and tables have no ordering. That is physical
and applies to files, which do have records. You are still un-
learning file systems.

>> i.e. we need all purchase orders to be in sequence. <<

That is a logical condition that implies you have no gaps in PO
numbers. This sequence can be either numeric or temporal. I would
assume numeric. The constraint for no gaps is that:

(SELECT MAX(order_nbr) - MIN(order_nbr) + 1 FROM PurchaseOrders)
= (SELECT COUNT (order_nbr) FROM PurchaseOrders)

In Standard SQL-92, this would be in a CHECK() constraint; SQL Server
has to use a TRIGGER or stored procedure.

>> 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 [sic: referenced table] which would normally be a foreign key to the child table [sic: referencing table], as the actual primary key in the child tables [sic]. <<

The terms parent and child are from old network databases and imply a
pointer chain. RDBMS uses references rather than points. Subtle but
important differences!

Erland already showed you my trick for doing sub-classes in SQL.

[Back to original message]