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@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 Portas

Received on Wed Feb 01 2006 - 10:46:14 CST