Re: foreign key restrictions

On Sun, Aug 10, 2008 at 1:15 AM, <rafal(at)zorro(dot)isa-geek(dot)com> wrote:

> CREATE TABLE two (id int not null unique, ofone int references one(id),
> CREATE TABLE three(one int not null, two int, info text, foreign key (one,
> two) references two (one, id));
>
> I get the following error:
> ERROR: there is no unique constraint matching given keys for referenced
> table "two"
>
> But.
>
> Since table TWO has a unique constraint on column ID, the (ID, OFONE) pair
> will also be unique, obviously.
This statement is not completely true. The only part of the pair that
is true is ID. Also there is not unique constraint on the pare. So
there is no way to PG to build a foreing key on the pair.

> I must have missed something here. Can someone help me understand this?

A foreign Key can only reference a field(s) that has some type of
unique constraint ( primary key or unique ).

Try this with table two:

CREATE TABLE two (
id int not null unique,
ofone int references one(id),
txt text not null,
PRIMARY KEY ( id, ofone ));

Once you've created you two field primary key, would will be able to
reference it in table three.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug