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

Michael Brosdorf michael.broesdorf at web.de
Wed Jul 16 02:53:15 CDT 2003


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



More information about the dba-SQLServer mailing list