Re: unique primary key shared between multiple tables.

From: Damjan S. Vujnović <damjan_at_NO_SPAM.galeb.etf.bg.ac.yu>
Date: Fri, 14 Mar 2003 11:12:31 +0100
Message-ID: <b4sa99$o1n$1@news.etf.bg.ac.yu>

ben wrote:
> In one of my database design textbooks, I ran across the following
> line:
>
> PropertyForRent(propertyNo, ownerNo)
>
> Foreign Key ownerNo references PrivateOwner(ownerNo) and
> BusinessOwner(ownerNo)
>
> 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
> BusinessOwner.ownerNo.
>
> 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 - 04:12:31 CST