[dba-SQLServer]Referential integrity question (Generalization/Specification)

So how do you classify the cross-overs they are coming out with?  Are
they cars or trucks or both or neither?  And how do you classify SUVs?
Some are built on truck chassis and some on auto chassis.  Unless the
subclasses are mutually exclusive there is no way of implementing RI.
If the subclasses are mutually exclusive, then you can write rules to
prohibit the same ID being in two subtables.

Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us



-----Original Message-----
From: Michael Brosdorf [mailto:michael.broesdorf at web.de] 
Sent: Wednesday 2003 Jul 16 02:53
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer]Referential integrity question
(Generalization/Specification)


Dear group,

for an application I need to implement a generalization of data objects.
As an example, let there be 3 tables:

1. Vehicles
2. Cars
3. Trucks

Every vehicle is, of course a vehicle with some common properties (for
instance steering wheel diameter :-)). But then it is either a car or
(xor) a truck, which both have different properties that apply only to
the respective type of vehicle (e.g. number of passenger seats for cars
etc.).

Every vehicle has an entry in tblVehicles with a unique Vehicle_ID.
Additionally, every vehicle has an entry in either tblCars (Cars_ID is
PK, Vehicle_ID is FK) OR tblTrucks (with Truck_ID as PK and Vehicle_ID
as FK).

A given Vehicle_ID can be in only one of the specific table.

Is there any way to enforce referential integrity in a scheme like that
using SQL Server 2000 Constraints or other mechanisms? If not, has
anyone implemented something like that (using triggers?) and would like
to share his/her experiences?


TIA,


Michael