Re: modeling either/or relationship...
From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: 1 Feb 2006 08:46:14 -0800
Message-ID: <1138812374.457238.256460_at_o13g2000cwo.googlegroups.com>
> > The classic scenario calls for a root class with all the common
> > attributes and then specialized sub-classes under it. As an example,
> > let's take the class of Vehicles and find an industry standard
> > identifier (VIN), and add two mutually exclusive sub-classes, Sport
> > utility vehicles and sedans ('SUV', 'SED').
> >
> > 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:
> >
> > CREATE TABLE SUV
> > (vin CHAR(17) NOT NULL PRIMARY KEY,
> > vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
> > CHECK(vehicle_type = 'SUV'),
> > UNIQUE (vin, vehicle_type),
> > FOREIGN KEY (vin, vehicle_type)
> > REFERENCES Vehicles(vin, vehicle_type)
> > ...
Date: 1 Feb 2006 08:46:14 -0800
Message-ID: <1138812374.457238.256460_at_o13g2000cwo.googlegroups.com>
paul c wrote:
> -CELKO- wrote:
> > The classic scenario calls for a root class with all the common
> > attributes and then specialized sub-classes under it. As an example,
> > let's take the class of Vehicles and find an industry standard
> > identifier (VIN), and add two mutually exclusive sub-classes, Sport
> > utility vehicles and sedans ('SUV', 'SED').
> >
> > 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:
> >
> > CREATE TABLE SUV
> > (vin CHAR(17) NOT NULL PRIMARY KEY,
> > vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
> > CHECK(vehicle_type = 'SUV'),
> > UNIQUE (vin, vehicle_type),
> > FOREIGN KEY (vin, vehicle_type)
> > REFERENCES Vehicles(vin, vehicle_type)
> > ...
> > > No comment about the design but not knowing much about SQL, I'm > wondering if the "overlapping candidate keys" (ignoring whether the term > as used here is wrong) is a way of "trick"ing some products into > allowing a reference to different columns than those of a primary key? > > Would interchanging the columns of the PRIMARY KEY and UNIQUE clauses > behave the same? Also, do all/most of the SQL products require a > foreign key to reference a primary key? (If so, I don't understand why.) > > cheers, > p >
AFAIK in standard SQL the PRIMARY KEY / UNIQUE constraints are interchangeable except that UNIQUE may include nullable columns whereas PRIMARY KEY may not and is the default for a foreign key reference if no other column(s) are specified using REFERENCES.
SQL's idea of designating a PRIMARY key from among the candidate keys is apparently a legacy of Codd's use of that term. If it has a useful purpose at all I guess it's to act as a hint to the DBMS on how to implement that key physically.
-- David PortasReceived on Wed Feb 01 2006 - 17:46:14 CET