Re: unique primary key shared between multiple tables.

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.

If you are using Oracle, SEQUENCES can provide the functionality you were looking for, without need to introduce table Owner. Also, if you don't wont to use introduce table Owner, you can "simulate" sequences by yourself (with another table), but that will probably degrade your performance...

-- 
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/