Message-ID: <4353D810.2050606@archonet.com>
Date: Mon, 17 Oct 2005 17:57:52 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
MIME-Version: 1.0
To: David Pradier <david.pradier@clarisys.fr>
Cc: codeWarrior <gpatnude@hotmail.com>, pgsql-sql@postgresql.org
Subject: Re: Design problemi : using the same primary keys for inherited
References: <20051014091829.GB19930@clarisys.fr> <dioirq$lsu$1@news.hub.org>
	<20051017141353.GF19930@clarisys.fr>
In-Reply-To: <20051017141353.GF19930@clarisys.fr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

David Pradier wrote:
> Well yes, i find your system very interesting and will maybe use it as
> solution for another problem we have ( :-) ), but i fail to see where
> it makes use of a primary key referencing another primary key ?

As regards the issue of one primary-key referencing another, I can't see 
any problem. You want 0 or 1 references from table B to any row in table 
A - this does that without any fuss or complication.

A primary key is a value (or set of values) like any other. It is unique 
over any one table, but nothing says it can't be the same as a value in 
some other table.

The other way would be something like:

CREATE TABLE A (
	id serial not null unique,
	aval text,
	primary key (id)
);
CREATE TABLE B (
	id serial not null unique,
	aref int4 not null unique references A,
	bval text,
	primary key (id)
);

So - in table B we now have two candidate keys (id, aref) and above I've 
chosen "id" as the primary-key. But I can eliminate "id" completely and 
not lose any of the meaning of table "B" - which tells me that I was 
wrong to think the rest of the table was dependent on "id". So, I must 
have been wrong in making "id" a primary-key and since it has no meaning 
of its own, I can eliminate it.

CREATE TABLE B (
	aref int4 not null unique references A,
	bval text,
	primary key (aref)
);


--
   Richard Huxton
   Archonet Ltd