Re: primary key as subtype discriminator

On Sep 3, 1:46 pm, -CELKO- <jcelko..._at_earthlink.net> wrote:

[cut]

> CREATE TABLE Vehicles
> (vin CHAR(17) NOT NULL PRIMARY KEY,
>  vehicle_type CHAR(3) NOT NULL
>        CHECK(vehicle_type IN ('SUV', 'SED')),
>  UNIQUE (vin, vehicle_type),
>  ..);
>
> Notice the overlapping candidate keys. I then use a compound candidate
> key (vin, vehicle_type) and a constraint in each sub-class table to
> assure that the vehicle_type is locked and agrees with the Vehicles
> table. Add some DRI actions and you are done:

[cut]

Thanks for your answer. I'm not sure about this implementation of a generalization structure. The problem is the UNIQUE constraint and not only because it's redundant.

> CREATE TABLE Sedans
> (vin CHAR(17) NOT NULL PRIMARY KEY,
>  vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
>        CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),

Here a Vehicle can be just a Sedan even if 2 subtypes are present (twodoor  and four-door). This violates the "exhaustive" rule.

>  UNIQUE (vin, vehicle_type),
>  FOREIGN KEY (vin, vehicle_type)
>   REFERENCES Vehicles(vin, vehicle_type)

If 'vehicle_type' is 2DR or 4DR, then you can't reference the Vehicles table - CHECK(vehicle_type IN ('SUV', 'SED')). If you change it into CHECK(vehicle_type IN ('SUV', 'SED', '2DR', '4DR')) then 2DR and 4DR are both subtypes of Vehicles and not of Sedans. Otherwise you are using a category discriminator to provide the domain for more than a generalization structure.

>   ON UPDATE CASCADE
>   ON DELETE CASCADE,
>  ..);

[cut] Received on Thu Sep 04 2008 - 02:58:37 CEST