Re: unique primary key shared between multiple tables.
Date: Fri, 14 Mar 2003 11:12:31 +0100
> In one of my database design textbooks, I ran across the following
> PropertyForRent(propertyNo, ownerNo)
> Foreign Key ownerNo references PrivateOwner(ownerNo) and
> This implies that I can make PrivateOwner.ownerNo and
> BusinessOwner.ownerNo unique not just for thier respective tables, I
> can make ownerNo unique across both tables. In other words, no
> PrivateOwner.ownerNo may have the the same value as any
> This would be extremely usefull, but I can't find any way to actually
> implement a shared primary key between tables. How do I do this?
Create a "supertype" called Owner and make PrivateOwner and BusinessOwner it's "subtypes". In DDL, something like:
CREATE TABLE Owner (
OwnerNo INTEGER NOT NULL PRIMARY KEY, ...
CREATE TABLE PrivateOwner (
OwnerNo INTEGER NOT NULL PRIMARY KEY, CONSTRAINT fk_p_o FOREIGN KEY OwnerNo REFERENCES Owner(OwnerNo) ...
CREATE TABLE BusinessOwner (
OwnerNo INTEGER NOT NULL PRIMARY KEY, CONSTRAINT fk_b_o FOREIGN KEY OwnerNo REFERENCES Owner(OwnerNo) ...
CREATE TABLE PropertyForRent (
... OwnerNo INTEGER NOT NULL REFERENCES Owner(OwnerNo), ...
Also, place all the common attributes for PrivateOwner and BusinessOwner in table Owner.
-- Regards, Damjan S. Vujnovic University of Belgrade School of Electrical Engineering Department of Computer Engineering & Informatics Belgrade, Serbia http://galeb.etf.bg.ac.yu/~damjan/Received on Fri Mar 14 2003 - 11:12:31 CET