Thread: foreign key restrictions
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?
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/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
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
> 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 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
<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. > 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 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
<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