pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmulti column foreign key for implicitly unique columns
38+ messages / 9 participants
[nested] [flat]
* multi column foreign key for implicitly unique columns
@ 2004-08-17 09:25 Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 2 replies; 38+ messages in thread
From: Markus Bertheau @ 2004-08-17 09:25 UTC (permalink / raw)
To: pgsql-sql
Hi,
PostgreSQL doesn't allow the creation of a foreign key to a combination
of fields that has got no dedicated unique key but is unique nonetheless
because a subset of the combination of fields has a unique constraint.
Example:
CREATE TABLE p (
name TEXT PRIMARY KEY,
"type" TEXT
);
CREATE TABLE f (
name TEXT,
"type" TEXT,
FOREIGN KEY(name, "type") REFERENCES p(name, "type")
);
ERROR: there is no unique constraint matching given keys for referenced table "p"
Is this on purpose? I think the foreign key should be allowed. Creating
an extra unique key only has a negative impact on performance, right?
Thanks
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 09:39 Oliver Elphick <olly@lfix.co.uk>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 38+ messages in thread
From: Oliver Elphick @ 2004-08-17 09:39 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
On Tue, 2004-08-17 at 10:25, Markus Bertheau wrote:
> Hi,
>
> PostgreSQL doesn't allow the creation of a foreign key to a combination
> of fields that has got no dedicated unique key but is unique nonetheless
> because a subset of the combination of fields has a unique constraint.
> Example:
>
> CREATE TABLE p (
> name TEXT PRIMARY KEY,
> "type" TEXT
> );
>
> CREATE TABLE f (
> name TEXT,
> "type" TEXT,
> FOREIGN KEY(name, "type") REFERENCES p(name, "type")
> );
> ERROR: there is no unique constraint matching given keys for referenced table "p"
What's the point of this? p.name is the primary key and is therefore
unique in p, so your foreign key should simply reference p.name. Having
f.type as a repetition of p.type violates normalisation principles,
since name is completely derivable by a join of f to p on name.
> Is this on purpose? I think the foreign key should be allowed. Creating
> an extra unique key only has a negative impact on performance, right?
If there is no unique key, how does the foreign key trigger find the
referenced row except by doing a sequential scan? Bad news! And when
one of the duplicate referenced rows changes, what should happen with ON
UPDATE or ON DELETE?
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"If ye abide in me, and my words abide in you, ye shall
ask what ye will, and it shall be done unto you."
John 15:7
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 09:49 Markus Bertheau <twanger@bluetwanger.de>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 1 reply; 38+ messages in thread
From: Markus Bertheau @ 2004-08-17 09:49 UTC (permalink / raw)
To: olly@lfix.co.uk; +Cc: pgsql-sql
В Втр, 17.08.2004, в 11:39, Oliver Elphick пишет:
> What's the point of this? p.name is the primary key and is therefore
> unique in p, so your foreign key should simply reference p.name. Having
> f.type as a repetition of p.type violates normalisation principles,
> since name is completely derivable by a join of f to p on name.
The real situation is a little more complicated:
CREATE TABLE classes (
name TEXT PRIMARY KEY
);
CREATE TABLE class_fields (
class_name TEXT REFERENCES classes(name),
field_name TEXT,
PRIMARY KEY(class_name, field_name)
);
CREATE TABLE objects (
name TEXT PRIMARY KEY,
class_name TEXT REFERENCES classes(name)
);
CREATE TABLE object_versions (
object_name TEXT REFERENCES objects(name),
object_version DATE,
PRIMARY KEY(object_name, object_version)
);
CREATE TABLE object_version_property_values (
object_name TEXT REFERENCES objects(name),
object_version DATE,
class_name TEXT,
field_name TEXT,
value TEXT,
FOREIGN KEY(object_name, object_version)
REFERENCES object_versions(object_name, object_version),
-- this fk is needed to make sure that the the object in
-- question really is of the class that field_name is a field of
FOREIGN KEY(object_name, class_name)
REFERENCES objects(name, class_name),
FOREIGN KEY(class_name, field_name)
REFERENCES class_fields(class_name, field_name)
);
ERROR: there is no unique constraint matching given keys
for referenced table "objects"
I need the fk on the columns.
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 09:52 Richard Huxton <dev@archonet.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
1 sibling, 1 reply; 38+ messages in thread
From: Richard Huxton @ 2004-08-17 09:52 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql
Markus Bertheau wrote:
> Hi,
>
> PostgreSQL doesn't allow the creation of a foreign key to a combination
> of fields that has got no dedicated unique key but is unique nonetheless
> because a subset of the combination of fields has a unique constraint.
[snip example]
> Is this on purpose? I think the foreign key should be allowed. Creating
> an extra unique key only has a negative impact on performance, right?
As you say, the uniqueness is guaranteed so there's no good reason why
it couldn't be made to work. It's probably more of an implementation
issue. Unique constraints are implemented with an index, so I'm guessing
the FK code assumes there is an index there to check against.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 14:26 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 38+ messages in thread
From: Stephan Szabo @ 2004-08-17 14:26 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql
On Tue, 17 Aug 2004, Richard Huxton wrote:
> Markus Bertheau wrote:
> > Hi,
> >
> > PostgreSQL doesn't allow the creation of a foreign key to a combination
> > of fields that has got no dedicated unique key but is unique nonetheless
> > because a subset of the combination of fields has a unique constraint.
> [snip example]
> > Is this on purpose? I think the foreign key should be allowed. Creating
> > an extra unique key only has a negative impact on performance, right?
>
> As you say, the uniqueness is guaranteed so there's no good reason why
> it couldn't be made to work. It's probably more of an implementation
> issue. Unique constraints are implemented with an index, so I'm guessing
No, actually, it's that the SQL92 (at least) spec says explicitly that
there must be a unique constraint across all of the columns specified, not
merely across a subset.
"then the set of column names of that <reference column list> shall be
equal to the set of column names in the unique columns of a unique
constraint of the referenced table."
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 14:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 38+ messages in thread
From: Tom Lane @ 2004-08-17 14:46 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Richard Huxton <dev@archonet.com>; Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> No, actually, it's that the SQL92 (at least) spec says explicitly that
> there must be a unique constraint across all of the columns specified, not
> merely across a subset.
> "then the set of column names of that <reference column list> shall be
> equal to the set of column names in the unique columns of a unique
> constraint of the referenced table."
SQL99 says the same. 11.8 syntax rule 3a:
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 think one reason for this is that otherwise it's not clear which
unique constraint the FK constraint depends on. Consider
create table a (f1 int unique, f2 int unique);
create table b (f1 int, f2 int,
foreign key (f1,f2) references a(f1,f2));
How would you decide which constraint to make the FK depend on?
It'd be purely arbitrary.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 14:57 Markus Bertheau <twanger@bluetwanger.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 38+ messages in thread
From: Markus Bertheau @ 2004-08-17 14:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Richard Huxton <dev@archonet.com>; pgsql-sql
В Втр, 17.08.2004, в 16:46, Tom Lane пишет:
> I think one reason for this is that otherwise it's not clear which
> unique constraint the FK constraint depends on. Consider
>
> create table a (f1 int unique, f2 int unique);
>
> create table b (f1 int, f2 int,
> foreign key (f1,f2) references a(f1,f2));
>
> How would you decide which constraint to make the FK depend on?
Either way, the semantics are the same, right?
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 15:06 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 38+ messages in thread
From: Stephan Szabo @ 2004-08-17 15:06 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; pgsql-sql
On Tue, 17 Aug 2004, Markus Bertheau wrote:
> В Втр, 17.08.2004, в 16:46, Tom Lane пишет:
>
> > I think one reason for this is that otherwise it's not clear which
> > unique constraint the FK constraint depends on. Consider
> >
> > create table a (f1 int unique, f2 int unique);
> >
> > create table b (f1 int, f2 int,
> > foreign key (f1,f2) references a(f1,f2));
> >
> > How would you decide which constraint to make the FK depend on?
>
> Either way, the semantics are the same, right?
Unfortunately, not in the case of dropping the chosen constraint.
Theoretically in that case, you'd probably have to extend the spec there
as well to say that you check any dependent objects again to see if they
would still be valid rather than dropping them (on cascade) or erroring
(on restrict).
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 15:12 Markus Bertheau <twanger@bluetwanger.de>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 38+ messages in thread
From: Markus Bertheau @ 2004-08-17 15:12 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; pgsql-sql
В Втр, 17.08.2004, в 17:06, Stephan Szabo пишет:
> On Tue, 17 Aug 2004, Markus Bertheau wrote:
>
> > В Втр, 17.08.2004, в 16:46, Tom Lane пишет:
> >
> > > I think one reason for this is that otherwise it's not clear which
> > > unique constraint the FK constraint depends on. Consider
> > >
> > > create table a (f1 int unique, f2 int unique);
> > >
> > > create table b (f1 int, f2 int,
> > > foreign key (f1,f2) references a(f1,f2));
> > >
> > > How would you decide which constraint to make the FK depend on?
> >
> > Either way, the semantics are the same, right?
>
> Unfortunately, not in the case of dropping the chosen constraint.
Can't you choose at fk check time rather than fk creation time?
> Theoretically in that case, you'd probably have to extend the spec there
> as well to say that you check any dependent objects again to see if they
> would still be valid rather than dropping them (on cascade) or erroring
> (on restrict).
That also makes sense and is more efficient as I see it.
Thanks
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 15:24 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 38+ messages in thread
From: Stephan Szabo @ 2004-08-17 15:24 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; pgsql-sql
On Tue, 17 Aug 2004, Markus Bertheau wrote:
> Ð ÐÑÑ, 17.08.2004, в 17:06, Stephan Szabo пиÑеÑ:
> > On Tue, 17 Aug 2004, Markus Bertheau wrote:
> >
> > > Ð ÐÑÑ, 17.08.2004, в 16:46, Tom Lane пиÑеÑ:
> > >
> > > > I think one reason for this is that otherwise it's not clear which
> > > > unique constraint the FK constraint depends on. Consider
> > > >
> > > > create table a (f1 int unique, f2 int unique);
> > > >
> > > > create table b (f1 int, f2 int,
> > > > foreign key (f1,f2) references a(f1,f2));
> > > >
> > > > How would you decide which constraint to make the FK depend on?
> > >
> > > Either way, the semantics are the same, right?
> >
> > Unfortunately, not in the case of dropping the chosen constraint.
>
> Can't you choose at fk check time rather than fk creation time?
>
> > Theoretically in that case, you'd probably have to extend the spec there
> > as well to say that you check any dependent objects again to see if they
> > would still be valid rather than dropping them (on cascade) or erroring
> > (on restrict).
>
> That also makes sense and is more efficient as I see it.
I'm not seeing what you're seeing then.
Right now, at creation, we can say object A depends on object B. When you
go to drop object B, we can easily lookup up which objects (A) depend on
it. When you go to drop object C, we can easily lookup up which objects
() depend on it.
If instead you put it off to drop time, when you drop object B, you need
to figure out which objects might potentially depend on be (lets say
(A,C)) and then determine which objects those do depend on and see if B is
among those sets.
If we do the in-between one, we could say that object A partially depends
on B (because something else can fufill the requirement as well
potentially). When you go to drop object B, we can see that A partially
depends on B and then check only A's dependencies to see whether any other
thing that might fufill the requirement still exists. In general, such a
system would need to be able to make sure that it worked properly with
multiple concurrent drops of objects that an object partially dependended
on (even though the constraint case is probably safe.) It sounds like
it'd be a pain at best.
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-17 15:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 38+ messages in thread
From: Tom Lane @ 2004-08-17 15:38 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Richard Huxton <dev@archonet.com>; pgsql-sql
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> ... It sounds like it'd be a pain at best.
Also, that would directly violate the SQL spec's model of dependencies,
with possibly unpleasant consequences. The current implementation does
exactly what SQL says to do. I cite from SQL99 11.99 DROP CONSTRAINT:
3) If TC is a unique constraint and RC is a referential constraint
whose referenced table is T and whose referenced columns are the
unique columns of TC, then RC is said to be dependent on TC.
...
6) If RESTRICT is specified, then:
a) No table constraint shall be dependent on TC.
NOTE 195 - If CASCADE is specified, then any such dependent
object will be dropped by the effective execution of the
<alter table statement> specified in the General Rules of this
Subclause.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 02:45 Josh Berkus <josh@agliodbs.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 2 replies; 38+ messages in thread
From: Josh Berkus @ 2004-08-18 02:45 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk; +Cc: pgsql-sql
Markus,
Hey, I see you figured out a workaround to writing a trigger for this. Let's
see if we can make it work.
ERROR: there is no unique constraint matching given keys
for referenced table "objects"
The reason for this is that CASCADE behavior gets quite odd when there is an
FK reference to a non-unique column. We used to allow it, in 7.1, and I was
responsible for a number of bug reports that led to us disallowing it. It
should be theoretically implementable and relationally sound but will require
a *lot* of troubleshooting to make work. So far, nobody's really interested
enough.
However, you have an easy way out:
ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name);
This will add the unique constraint that Postgres wants without changing your
data at all.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 06:00 Markus Bertheau <twanger@bluetwanger.de>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 38+ messages in thread
From: Markus Bertheau @ 2004-08-18 06:00 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: olly@lfix.co.uk; pgsql-sql
В Срд, 18.08.2004, в 04:45, Josh Berkus пишет:
> Markus,
Hi Josh,
> Hey, I see you figured out a workaround to writing a trigger for this. Let's
> see if we can make it work.
>
> ERROR: there is no unique constraint matching given keys
> for referenced table "objects"
>
> The reason for this is that CASCADE behavior gets quite odd when there is an
> FK reference to a non-unique column. We used to allow it, in 7.1, and I was
> responsible for a number of bug reports that led to us disallowing it. It
> should be theoretically implementable and relationally sound but will require
> a *lot* of troubleshooting to make work. So far, nobody's really interested
> enough.
>
> However, you have an easy way out:
>
> ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name);
I was worried about the performance hit because (name, class_name) will
always be unique, yet they will be checked for uniqueness.
Thanks
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 13:33 Jan Wieck <JanWieck@Yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 38+ messages in thread
From: Jan Wieck @ 2004-08-18 13:33 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk; pgsql-sql
On 8/17/2004 10:45 PM, Josh Berkus wrote:
> Markus,
>
> Hey, I see you figured out a workaround to writing a trigger for this. Let's
> see if we can make it work.
>
> ERROR: there is no unique constraint matching given keys
> for referenced table "objects"
>
> The reason for this is that CASCADE behavior gets quite odd when there is an
> FK reference to a non-unique column. We used to allow it, in 7.1, and I was
> responsible for a number of bug reports that led to us disallowing it. It
> should be theoretically implementable and relationally sound but will require
> a *lot* of troubleshooting to make work. So far, nobody's really interested
> enough.
SQL92 4.10 Integrity constraints:
...
In the case that a table constraint is a referential constraint,
the table is referred to as the referencing table. The referenced
columns of a referential constraint shall be the unique columns of
some unique constraint of the referenced table.
...
Meaning that not enforcing the uniqueness of those columns isn't an
option. PostgreSQL is currently happy with a UNIQUE constraint that
covers those columns in any order, which is to the letter spec
compliant. "Really interested" will not do here.
Jan
>
> However, you have an easy way out:
>
> ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name);
>
> This will add the unique constraint that Postgres wants without changing your
> data at all.
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 13:49 Markus Bertheau <twanger@bluetwanger.de>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 1 reply; 38+ messages in thread
From: Markus Bertheau @ 2004-08-18 13:49 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk; pgsql-sql
В Срд, 18.08.2004, в 15:33, Jan Wieck пишет:
> Meaning that not enforcing the uniqueness of those columns isn't an
> option.
The thing is that the columns _are_ unique, there's just no unique
constraint on them. They are unique because there's a unique constraint
on a subset of these columns. So no additional uniqueness enforcing
needed.
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 14:06 Jan Wieck <JanWieck@Yahoo.com>
parent: Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 4 replies; 38+ messages in thread
From: Jan Wieck @ 2004-08-18 14:06 UTC (permalink / raw)
To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
On 8/18/2004 9:49 AM, Markus Bertheau wrote:
> В Срд, 18.08.2004, в 15:33, Jan Wieck пишет:
>
>> Meaning that not enforcing the uniqueness of those columns isn't an
>> option.
>
> The thing is that the columns _are_ unique, there's just no unique
> constraint on them. They are unique because there's a unique constraint
> on a subset of these columns. So no additional uniqueness enforcing
> needed.
>
Yes, you are right, a superset of columns of a unique constraint is
allways unique as well.
I assume it is performance why you are denormalizing your data?
However, Bruce, this should be on the TODO list:
* Allow foreign key to reference a superset of the columns
covered by a unique constraint on the referenced table.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 14:28 Markus Bertheau <twanger@bluetwanger.de>
parent: Jan Wieck <JanWieck@Yahoo.com>
3 siblings, 0 replies; 38+ messages in thread
From: Markus Bertheau @ 2004-08-18 14:28 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
В Срд, 18.08.2004, в 16:06, Jan Wieck пишет:
> I assume it is performance why you are denormalizing your data?
Please have a look at
http://archives.postgresql.org/pgsql-sql/2004-08/msg00157.php
for the schema and an explanation. I'm not denormalizing it as far as I
can tell.
Thanks
--
Markus Bertheau <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 14:38 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
3 siblings, 0 replies; 38+ messages in thread
From: Stephan Szabo @ 2004-08-18 14:38 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
On Wed, 18 Aug 2004, Jan Wieck wrote:
> On 8/18/2004 9:49 AM, Markus Bertheau wrote:
>
> > РСÑд, 18.08.2004, в 15:33, Jan Wieck пиÑеÑ:
> >
> >> Meaning that not enforcing the uniqueness of those columns isn't an
> >> option.
> >
> > The thing is that the columns _are_ unique, there's just no unique
> > constraint on them. They are unique because there's a unique constraint
> > on a subset of these columns. So no additional uniqueness enforcing
> > needed.
> >
>
> Yes, you are right, a superset of columns of a unique constraint is
> allways unique as well.
True, but the spec explicitly asks for the columns to be the members of a
unique constraint, not that the columns be provably unique. See the other
portion of the thread related to dropping constraints for other spec
extensions doing this implies. I think the actual specific change to make
it look for the subset should be trivial, but we'd really need to work
out those dependency issues at the very least and I'm afraid there are
more such little corners.
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <JanWieck@Yahoo.com>
3 siblings, 1 reply; 38+ messages in thread
From: Tom Lane @ 2004-08-18 15:16 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Jan Wieck <JanWieck@Yahoo.com> writes:
> However, Bruce, this should be on the TODO list:
> * Allow foreign key to reference a superset of the columns
> covered by a unique constraint on the referenced table.
See the followup discussion as to why this is a bad idea.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 15:45 Rod Taylor <pg@rbt.ca>
parent: Jan Wieck <JanWieck@Yahoo.com>
3 siblings, 0 replies; 38+ messages in thread
From: Rod Taylor @ 2004-08-18 15:45 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
> However, Bruce, this should be on the TODO list:
>
> * Allow foreign key to reference a superset of the columns
> covered by a unique constraint on the referenced table.
It would probably be more beneficial to be able to create a unique
constraint without requiring the fields be indexed.
Gets rid of most of the overhead from double uniques.
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 16:02 Richard Huxton <dev@archonet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 38+ messages in thread
From: Richard Huxton @ 2004-08-18 16:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
>
>>However, Bruce, this should be on the TODO list:
>> * Allow foreign key to reference a superset of the columns
>> covered by a unique constraint on the referenced table.
>
>
> See the followup discussion as to why this is a bad idea.
Maybe an alternative todo?
* Allow multiple unique constraints to share an index where one is a
superset of the others' columns.
That way you can mark it unique without having the overhead of multiple
indexes.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 16:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 38+ messages in thread
From: Tom Lane @ 2004-08-18 16:18 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Richard Huxton <dev@archonet.com> writes:
> * Allow multiple unique constraints to share an index where one is a
> superset of the others' columns.
> That way you can mark it unique without having the overhead of multiple
> indexes.
That just moves the uncertain-dependency problem over one spot, ie, it's
the fabricated unique constraint that you can't pin down the
requirements for.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 16:27 Jan Wieck <JanWieck@Yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 38+ messages in thread
From: Jan Wieck @ 2004-08-18 16:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
On 8/18/2004 12:18 PM, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> * Allow multiple unique constraints to share an index where one is a
>> superset of the others' columns.
>
>> That way you can mark it unique without having the overhead of multiple
>> indexes.
>
> That just moves the uncertain-dependency problem over one spot, ie, it's
> the fabricated unique constraint that you can't pin down the
> requirements for.
If we allow for a unique index, that
* it is NOT maintained (no index tuples in there)
* depends on another index that has a subset of columns
* if that subset-index is dropped, the index becomes maintained
then the uncertainty is gone. At the time someone drops the other
constraint or unique index, the data is unique with respect to the
superset of columns. So building the unique index data at that time will
succeed.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 16:46 Rod Taylor <pg@rbt.ca>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 38+ messages in thread
From: Rod Taylor @ 2004-08-18 16:46 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
On Wed, 2004-08-18 at 12:27, Jan Wieck wrote:
> On 8/18/2004 12:18 PM, Tom Lane wrote:
>
> > Richard Huxton <dev@archonet.com> writes:
> >> * Allow multiple unique constraints to share an index where one is a
> >> superset of the others' columns.
> >
> >> That way you can mark it unique without having the overhead of multiple
> >> indexes.
> >
> > That just moves the uncertain-dependency problem over one spot, ie, it's
> > the fabricated unique constraint that you can't pin down the
> > requirements for.
>
> If we allow for a unique index, that
Silly question, but why does UNIQUE require an index at all? Yes,
current implementation does, and agreed that checks will be mighty slow
without an index (so are CASCADES to a non-indexed column)...
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 16:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 2 replies; 38+ messages in thread
From: Tom Lane @ 2004-08-18 16:46 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Richard Huxton <dev@archonet.com>; Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Jan Wieck <JanWieck@Yahoo.com> writes:
> If we allow for a unique index, that
> * it is NOT maintained (no index tuples in there)
> * depends on another index that has a subset of columns
> * if that subset-index is dropped, the index becomes maintained
> then the uncertainty is gone. At the time someone drops the other
> constraint or unique index, the data is unique with respect to the
> superset of columns. So building the unique index data at that time will
> succeed.
My goodness this is getting ugly. The notion of having to invoke an
index build as a side-effect of a DROP sounds like a recipe for trouble.
(Sample problem: what you're actually trying to do is drop the entire
table ... but because the subset-index happens to get visited first,
you go off and build the superset-index before you let the DROP finish.
User will be unhappy, if table is large. Or try this one: the superset-
index build actually fails because you've already dropped something it
depends on. This seems quite possible in cases involving cascading from
a drop of an individual column or datatype, for instance.)
I'd like to see more than one person needing it, before we go to that
kind of trouble to do something that's not in the spec.
regards, tom lane
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 17:05 Josh Berkus <josh@agliodbs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 3 replies; 38+ messages in thread
From: Josh Berkus @ 2004-08-18 17:05 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk; pgsql-sql
Jan,
> In the case that a table constraint is a referential constraint,
> the table is referred to as the referencing table. The referenced
> columns of a referential constraint shall be the unique columns of
> some unique constraint of the referenced table.
Missed that one. Interesting. AFAIK, the uniqueness of referenced columns is
NOT a requirement of Relaitonal Algebra. So why does SQL require it?
Maybe I'll ask Joe Celko after he finishes moving to Austin.
I have my own issue that forced me to use triggers. Given:
table users (
name
login PK
status
etc. )
table status (
status
relation
label
definition
PK status, relation )
the relationship is:
users.status = status.status AND status.relation = 'users';
This is a mathematically definable constraint, but there is no way in standard
SQL to create an FK for it. This is one of the places I point to whenever
we have the "SQL is imperfectly relational" discussion.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 17:45 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 38+ messages in thread
From: Stephan Szabo @ 2004-08-18 17:45 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk; pgsql-sql
On Wed, 18 Aug 2004, Josh Berkus wrote:
> > In the case that a table constraint is a referential constraint,
> > the table is referred to as the referencing table. The referenced
> > columns of a referential constraint shall be the unique columns of
> > some unique constraint of the referenced table.
>
> Missed that one. Interesting. AFAIK, the uniqueness of referenced columns is
> NOT a requirement of Relaitonal Algebra. So why does SQL require it?
>
> Maybe I'll ask Joe Celko after he finishes moving to Austin.
>
> I have my own issue that forced me to use triggers. Given:
>
> table users (
> name
> login PK
> status
> etc. )
>
> table status (
> status
> relation
> label
> definition
> PK status, relation )
>
> the relationship is:
> users.status = status.status AND status.relation = 'users';
>
> This is a mathematically definable constraint, but there is no way in standard
> SQL to create an FK for it. This is one of the places I point to whenever
> we have the "SQL is imperfectly relational" discussion.
Well, I think SQL does give a way of specifying that constraint through
assertions and check constraints with subselects. We just don't support
either of those constructs.
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 17:49 Richard Huxton <dev@archonet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 38+ messages in thread
From: Richard Huxton @ 2004-08-18 17:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
>
>>If we allow for a unique index, that
>> * it is NOT maintained (no index tuples in there)
>> * depends on another index that has a subset of columns
>> * if that subset-index is dropped, the index becomes maintained
>>then the uncertainty is gone. At the time someone drops the other
>>constraint or unique index, the data is unique with respect to the
>>superset of columns. So building the unique index data at that time will
>>succeed.
>
>
> My goodness this is getting ugly. The notion of having to invoke an
> index build as a side-effect of a DROP sounds like a recipe for trouble.
I'm not sure it needs to be as clever as Jan suggested (but then I'm not
as clever as Jan :-). I'd have thought a reference that forces you to
use DROP...CASCADE would be enough. In those cases where you're dropping
a whole table, presumably that's already implied.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 18:01 Jan Wieck <JanWieck@Yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 38+ messages in thread
From: Jan Wieck @ 2004-08-18 18:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; Markus Bertheau <twanger@bluetwanger.de>; Josh Berkus <josh@agliodbs.com>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
On 8/18/2004 12:46 PM, Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
>> If we allow for a unique index, that
>> * it is NOT maintained (no index tuples in there)
>> * depends on another index that has a subset of columns
>> * if that subset-index is dropped, the index becomes maintained
>> then the uncertainty is gone. At the time someone drops the other
>> constraint or unique index, the data is unique with respect to the
>> superset of columns. So building the unique index data at that time will
>> succeed.
>
> My goodness this is getting ugly. The notion of having to invoke an
> index build as a side-effect of a DROP sounds like a recipe for trouble.
The idea sure needs some refinement :-)
> I'd like to see more than one person needing it, before we go to that
> kind of trouble to do something that's not in the spec.
Actually, the whole thing strikes me more as a sign for a denormalized
database schema.
If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring
that the redundant copy of y in b.y stays in sync with a.y.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 18:55 Josh Berkus <josh@agliodbs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 38+ messages in thread
From: Josh Berkus @ 2004-08-18 18:55 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
Jan,
>
> If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring
> that the redundant copy of y in b.y stays in sync with a.y.
So? What's denormalized about that? His other choice is to use a trigger.
What he's trying to do is ensure that the class selected for the FK
class_name, field_name relates to the same class_name in objects.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 19:24 Bruno Wolff III <bruno@wolff.to>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 38+ messages in thread
From: Bruno Wolff III @ 2004-08-18 19:24 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk; pgsql-sql
On Wed, Aug 18, 2004 at 10:05:13 -0700,
Josh Berkus <josh@agliodbs.com> wrote:
>
> I have my own issue that forced me to use triggers. Given:
>
> table users (
> name
> login PK
> status
> etc. )
>
> table status (
> status
> relation
> label
> definition
> PK status, relation )
>
> the relationship is:
> users.status = status.status AND status.relation = 'users';
>
> This is a mathematically definable constraint, but there is no way in standard
> SQL to create an FK for it. This is one of the places I point to whenever
> we have the "SQL is imperfectly relational" discussion.
If users is supposed to reference status you can do this by adding a relation
column to users, using a constraint to force relation to always be 'users'
and then having (status, relation) being a foreign key.
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 19:36 Josh Berkus <josh@agliodbs.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 38+ messages in thread
From: Josh Berkus @ 2004-08-18 19:36 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Bruno,
> If users is supposed to reference status you can do this by adding a
> relation column to users, using a constraint to force relation to always be
> 'users' and then having (status, relation) being a foreign key.
But that requires the addition of an extra, indexed Text column to the table.
And the "status" reference table is not the only place I need this construct;
I have other relationships to reference tables which are similar. It would
amount to adding probably a total of 25-40 columns to various tables in my
database overall; maybe 100MB of completely redundant data. :-(
I'll wait for ASSERTIONS, I think.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-18 22:33 Jan Wieck <JanWieck@Yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 38+ messages in thread
From: Jan Wieck @ 2004-08-18 22:33 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk, pgsql-sql@postgresql.org, Bruce Momjian <pgman@candle.pha.pa.us>
On 8/18/2004 2:55 PM, Josh Berkus wrote:
> Jan,
>>
>> If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring
>> that the redundant copy of y in b.y stays in sync with a.y.
>
> So? What's denormalized about that? His other choice is to use a trigger.
Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same
value and he even wants to ensure this with the constraint.
Jan
>
> What he's trying to do is ensure that the class selected for the FK
> class_name, field_name relates to the same class_name in objects.
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-19 16:21 Josh Berkus <josh@agliodbs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 38+ messages in thread
From: Josh Berkus @ 2004-08-19 16:21 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: pgsql-sql
Jan,
> Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same
> value and he even wants to ensure this with the constraint.
And in the absence of that constraint, what ensures that b.y = a.y, exactly?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-19 16:52 Oliver Elphick <olly@lfix.co.uk>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 38+ messages in thread
From: Oliver Elphick @ 2004-08-19 16:52 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; pgsql-sql
On Thu, 2004-08-19 at 17:21, Josh Berkus wrote:
> Jan,
>
> > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same
> > value and he even wants to ensure this with the constraint.
>
> And in the absence of that constraint, what ensures that b.y = a.y, exactly?
In the absence of b.y, it would be impossible for it to be anything
else. Isn't that the point?
It seems to me that he was trying to use the database to show errors in
his source data, but since his constraint would reject the data, he
wouldn't be able to enter it; all he could do would be to see the
error. So he might as well turn it round, normalise the data properly
and use the database to tell the rest of the system what the data ought
to be.
Oliver Elphick
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-19 21:41 Jan Wieck <JanWieck@Yahoo.com>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 38+ messages in thread
From: Jan Wieck @ 2004-08-19 21:41 UTC (permalink / raw)
To: olly@lfix.co.uk; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
On 8/19/2004 12:52 PM, Oliver Elphick wrote:
> On Thu, 2004-08-19 at 17:21, Josh Berkus wrote:
>> Jan,
>>
>> > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same
>> > value and he even wants to ensure this with the constraint.
>>
>> And in the absence of that constraint, what ensures that b.y = a.y, exactly?
>
> In the absence of b.y, it would be impossible for it to be anything
> else. Isn't that the point?
Precisely. I meant that the entire column is redundant and obsolete.
Without the column, no need for any constraint.
> It seems to me that he was trying to use the database to show errors in
> his source data, but since his constraint would reject the data, he
> wouldn't be able to enter it; all he could do would be to see the
> error. So he might as well turn it round, normalise the data properly
> and use the database to tell the rest of the system what the data ought
> to be.
I assumed he often queries b, and to avoid joining a all the time he
duplicates values from a into b and then tries to ensure that they stay
in sync with constraints.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-20 07:53 Richard Huxton <dev@archonet.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 38+ messages in thread
From: Richard Huxton @ 2004-08-20 07:53 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk; pgsql-sql
Josh Berkus wrote:
> I have my own issue that forced me to use triggers. Given:
>
> table users (
> name
> login PK
> status
> etc. )
>
> table status (
> status
> relation
> label
> definition
> PK status, relation )
>
> the relationship is:
> users.status = status.status AND status.relation = 'users';
>
> This is a mathematically definable constraint, but there is no way in standard
> SQL to create an FK for it. This is one of the places I point to whenever
> we have the "SQL is imperfectly relational" discussion.
It'd be nice to say something like:
ALTER TABLE status ADD CONSTRAINT user_status_fk
FOREIGN KEY (status) WHERE relation = 'users'
REFERENCES users(status);
And the flip-side so you can have:
ALTER TABLE cheque_details ADD CONSTRAINT chq_trans_fk
FOREIGN KEY (trans_id)
REFERENCES transactions(trans_id) WHERE trans_type='CHQ';
Actually, since we can have a "unique index with where" this second form
should be do-able shouldn't it?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 38+ messages in thread
* Re: multi column foreign key for implicitly unique columns
@ 2004-08-20 15:04 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 38+ messages in thread
From: Stephan Szabo @ 2004-08-20 15:04 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Jan Wieck <JanWieck@Yahoo.com>; Markus Bertheau <twanger@bluetwanger.de>; olly@lfix.co.uk; pgsql-sql
On Fri, 20 Aug 2004, Richard Huxton wrote:
> It'd be nice to say something like:
>
> ALTER TABLE status ADD CONSTRAINT user_status_fk
> FOREIGN KEY (status) WHERE relation = 'users'
> REFERENCES users(status);
>
> And the flip-side so you can have:
>
> ALTER TABLE cheque_details ADD CONSTRAINT chq_trans_fk
> FOREIGN KEY (trans_id)
> REFERENCES transactions(trans_id) WHERE trans_type='CHQ';
>
> Actually, since we can have a "unique index with where" this second form
> should be do-able shouldn't it?
Maybe, but there are some issues about how the feature would be defined.
What is legal in those WHERE clauses?
Can it refer to columns of the other table?
Does the condition need to be immutable?
If not, can it contain subselects?
Can one use referentials actions on the constraint?
If so, which rule is used for the second if a row is updated from having
'CHQ' to something else? Is it update because that's the original
command, in which case things like update cascade will still error, or
is it delete because the row is disappearing from the table created with
the where clause?
SQL has assertions which would presumably be able to handle the general
constraints above which should have questions like this defined (and
doesn't have referential actions I believe). It might be better to
implement those if one was going to do it.
^ permalink raw reply [nested|flat] 38+ messages in thread
end of thread, other threads:[~2004-08-20 15:04 UTC | newest]
Thread overview: 38+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-17 09:25 multi column foreign key for implicitly unique columns Markus Bertheau <twanger@bluetwanger.de>
2004-08-17 09:39 ` Oliver Elphick <olly@lfix.co.uk>
2004-08-17 09:49 ` Markus Bertheau <twanger@bluetwanger.de>
2004-08-18 02:45 ` Josh Berkus <josh@agliodbs.com>
2004-08-18 06:00 ` Markus Bertheau <twanger@bluetwanger.de>
2004-08-18 13:33 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-18 13:49 ` Markus Bertheau <twanger@bluetwanger.de>
2004-08-18 14:06 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-18 14:28 ` Markus Bertheau <twanger@bluetwanger.de>
2004-08-18 14:38 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-18 15:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-18 16:02 ` Richard Huxton <dev@archonet.com>
2004-08-18 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-18 16:27 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-18 16:46 ` Rod Taylor <pg@rbt.ca>
2004-08-18 16:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-18 17:49 ` Richard Huxton <dev@archonet.com>
2004-08-18 18:01 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-18 18:55 ` Josh Berkus <josh@agliodbs.com>
2004-08-18 22:33 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-19 16:21 ` Josh Berkus <josh@agliodbs.com>
2004-08-19 16:52 ` Oliver Elphick <olly@lfix.co.uk>
2004-08-19 21:41 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-18 15:45 ` Rod Taylor <pg@rbt.ca>
2004-08-18 17:05 ` Josh Berkus <josh@agliodbs.com>
2004-08-18 17:45 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-18 19:24 ` Bruno Wolff III <bruno@wolff.to>
2004-08-18 19:36 ` Josh Berkus <josh@agliodbs.com>
2004-08-20 07:53 ` Richard Huxton <dev@archonet.com>
2004-08-20 15:04 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-17 09:52 ` Richard Huxton <dev@archonet.com>
2004-08-17 14:26 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-17 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-17 14:57 ` Markus Bertheau <twanger@bluetwanger.de>
2004-08-17 15:06 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-17 15:12 ` Markus Bertheau <twanger@bluetwanger.de>
2004-08-17 15:24 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-17 15:38 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox