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
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 =
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 =
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
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
---
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->
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 tabl
On Wed, Aug 18, 2004 at 10:05:13 -0700,
Josh Berkus <[EMAIL PROTECTED]> 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
>
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_na
On 8/18/2004 12:46 PM, Tom Lane wrote:
Jan Wieck <[EMAIL PROTECTED]> 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
Tom Lane wrote:
Jan Wieck <[EMAIL PROTECTED]> 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 go
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
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.
Jan Wieck <[EMAIL PROTECTED]> 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.
On Wed, 2004-08-18 at 12:27, Jan Wieck wrote:
> On 8/18/2004 12:18 PM, Tom Lane wrote:
>
> > Richard Huxton <[EMAIL PROTECTED]> 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
On 8/18/2004 12:18 PM, Tom Lane wrote:
Richard Huxton <[EMAIL PROTECTED]> 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-d
Richard Huxton <[EMAIL PROTECTED]> 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 spo
Tom Lane wrote:
Jan Wieck <[EMAIL PROTECTED]> 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 alt
> 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 ind
Jan Wieck <[EMAIL PROTECTED]> 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.
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 uniq
Ð ÐÑÐ, 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
--
Marku
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 cons
Ð ÐÑÐ, 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
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
Ð ÐÑÐ, 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
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 ref
Stephan Szabo <[EMAIL PROTECTED]> 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 CONSTRAI
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 const
Ð ÐÑÑ, 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
> > >
> > >
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);
> >
> > cr
Ð ÐÑÑ, 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,
>
Stephan Szabo <[EMAIL PROTECTED]> 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 shall be
> equal to the set of column n
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 c
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 k
Ð ÐÑÑ, 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 deri
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:
>
>
37 matches
Mail list logo
