foreign key restrictions

On Sun, Aug 10, 2008 at 1:15 AM, <rafal@zorro.isa-geek.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

"Richard Broersma" <richard.broersma@gmail.com> writes:

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

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.

Uhm, afaics he's right. if ID is unique not null then <ID, OFONE> also has to
be unique. That is, there could be duplicate values of OFONE but they'll all
have different values of ID anyways.

I'm not sure if there's a fundamental reason why there has to be an index that
exactly matches the foreign key or not -- offhand I can't think of one.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

On Sun, Aug 10, 2008 at 07:10:10AM -0700, Richard Broersma wrote:

On Sun, Aug 10, 2008 at 1:15 AM, <rafal@zorro.isa-geek.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));

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.

Eh? If ID is unique, then (ID,OFONE) is also unique. You don't need to
add another unique constraint because they're already guarenteed to be
unique.

While I admit the table structure is a bit odd, it should be fairly
easy to support it in postgres.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Gregory Stark <stark@enterprisedb.com> writes:

I'm not sure if there's a fundamental reason why there has to be an index that
exactly matches the foreign key or not -- offhand I can't think of one.

The reason why is that the SQL spec says so:

a) If the <referenced table and columns> specifies a <reference
column list>, then the set of <column name>s contained
in that <reference column list> shall be equal to the
set of <column name>s contained in the <unique column
list> of a unique constraint of the referenced table. Let
referenced columns be the column or columns identified by
that <reference column list> and let referenced column be one
such column. Each referenced column shall identify a column
of the referenced table and the same column shall not be
identified more than once.

I'm not entirely sure, but I think the restrictive definition might be
necessary with some of the more complex options for foreign keys, such
as MATCH PARTIAL.

regards, tom lane

<rafal@zorro.isa-geek.com> wrote in message
news:64cc57edd02dabd82e3f95268aee1a67.squirrel@localhost...

Hi All,

on numerous times I had fell onto postgress complaining, that I try to
create foreign key, pointing to a set not embraced within a unique key
constraint.

Here is the case:

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

now when I try to:

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.

Naturaly I can, and I do, add the requested constraint to the table TWO,
but to me it looks spurious - not providing any additional constraint
(which is already quearanteed by unique(ID), just a 'syntax glue'.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Postgresql is being faithful to the SQL standard. ISO/IEC 9075-2:2003 11.8
<referential constraint definition> says:

"If the <referenced table and columns> specifies a <reference column list>,
then there shall be a one-to-one correspondence between the set of <column
name>s contained in that <reference column list> and the set of <column
name>s contained in the <unique column list> of a unique constraint of the
referenced table such that corresponding <column name>s are equivalent."

I don't think there is any sound justification(*) for this limitation but it
is shared by other SQL DBMSs too. Most are incredibly lame when it comes to
support for multi-table constraints. The general type of constraint you are
referring to is often called an "inclusion dependency". Probably the reason
why it isn't well supported is that the optimisation of such constraints
within the limitations of SQL is potentially quite a hard problem.

(*) Note that the term "FOREIGN KEY" is misleading anyway. The constraint
that SQL calls a FOREIGN KEY is not the same as what the relational model
calls a "foreign key". In the RM, convention has it that only referential
constraints that reference candidate keys are called foreign keys whereas
SQL allows its FOREIGN KEY to reference any columns declared as unique (ie
may be a super key rather than a candidate key).

--
David Portas

Gregory Stark <stark@enterprisedb.com> writes:

I'm not sure if there's a fundamental reason why there has to be an
index that
exactly matches the foreign key or not -- offhand I can't think of one.

The reason why is that the SQL spec says so:

a) If the <referenced table and columns> specifies a
<reference
column list>, then the set of <column name>s contained
in that <reference column list> shall be equal to the
set of <column name>s contained in the <unique column
list> of a unique constraint of the referenced table. Let
referenced columns be the column or columns identified by
that <reference column list> and let referenced column be
one
such column. Each referenced column shall identify a column
of the referenced table and the same column shall not be
identified more than once.

I'm not entirely sure, but I think the restrictive definition might be
necessary with some of the more complex options for foreign keys, such
as MATCH PARTIAL.

I must admit, the standard is not very easy reading for me; what exactly
does the standarad mean by "<unique column list>":
1. is that a requirement for mathematical properties of that list, or
2. is that a requirement for explicit SQL UNIQUE INDEX existing over the
entire list.

Since <column list> is a <unique column list> whenever a subset of <column
list> is a <unique column list>, then if interpretation nr.1 of the
standard is OK, there is no real requirement to install (and require to
install) an additional unique constraint on the target <column list>.

-R

<rafal@zorro.isa-geek.com> wrote in message
news:fed538acdecf7f90be655937817877c1.squirrel@localhost...

Gregory Stark <stark@enterprisedb.com> writes:

I'm not sure if there's a fundamental reason why there has to be an
index that
exactly matches the foreign key or not -- offhand I can't think of one.

The reason why is that the SQL spec says so:

a) If the <referenced table and columns> specifies a
<reference
column list>, then the set of <column name>s contained
in that <reference column list> shall be equal to the
set of <column name>s contained in the <unique column
list> of a unique constraint of the referenced table. Let
referenced columns be the column or columns identified by
that <reference column list> and let referenced column be
one
such column. Each referenced column shall identify a column
of the referenced table and the same column shall not be
identified more than once.

I'm not entirely sure, but I think the restrictive definition might be
necessary with some of the more complex options for foreign keys, such
as MATCH PARTIAL.

I must admit, the standard is not very easy reading for me; what exactly
does the standarad mean by "<unique column list>":
1. is that a requirement for mathematical properties of that list, or
2. is that a requirement for explicit SQL UNIQUE INDEX existing over the
entire list.

<unique column list> just means the column name list specified between the
parentheses of some UNIQUE or PRIMARY KEY constraint. There is no such thing
as a UNIQUE INDEX in SQL.

rafal@zorro.isa-geek.com writes:

The reason why is that the SQL spec says so:

a) If the <referenced table and columns> specifies a
<reference
column list>, then the set of <column name>s contained
in that <reference column list> shall be equal to the
set of <column name>s contained in the <unique column
list> of a unique constraint of the referenced table.

I must admit, the standard is not very easy reading for me; what exactly
does the standarad mean by "<unique column list>":
1. is that a requirement for mathematical properties of that list, or

The point is it says "shall be equal to", not "shall be a superset of".

regards, tom lane

rafal@zorro.isa-geek.com writes:

The reason why is that the SQL spec says so:

a) If the <referenced table and columns> specifies a
<reference
column list>, then the set of <column name>s contained
in that <reference column list> shall be equal to the
set of <column name>s contained in the <unique column
list> of a unique constraint of the referenced table.

I must admit, the standard is not very easy reading for me; what exactly
does the standarad mean by "<unique column list>":
1. is that a requirement for mathematical properties of that list, or

The point is it says "shall be equal to", not "shall be a superset of".

So its the "meaning nr.2". The "syntax glue" not the actual math of sets.

This is strange, I must say. But no further questions when this is a
"syntax" requirement.

Thenx,

-R