Skip site navigation (1) Skip section navigation (2)

Re: unique constraints on foreign keys

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Jaime Casanova <systemguards(at)yahoo(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: unique constraints on foreign keys
Date: 2004-09-20 21:02:22
Message-ID: 20040920135913.H79243@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
On Mon, 20 Sep 2004, Jaime Casanova wrote:

> I have a reference table that holds all the status
> used in a system (it's a very little table just 10 or
> 15 rows).
>
> create table status (
>  cod_status     char(2)     not null primary key,
>  nam_status     text        not null
> );
>
> create table some_other_table (
>  ... definition of the table ...
>  status         char(2) not null references status
> );
>
> ok. This is the idea if a want to do a reference to
> the status table i need to create a primary key or a
> unique index on the status table.
>
> Obviously the planner will choose always a seq scan in
> such a table (it's obvious to you, it's obvious to me,
> but not to the planner), but the planner will
> calculate the cost of use the index and will choose
> the seq.
> So, there is a way to teach the planner always do a
> seq on that table and do not ask for use the index?
> There is a way to not enforce the creation of a
> primary or unique index on a referenced table?

Not really. It's not the index that's important per-se, it's the
uniqueness that is implied. The SQL spec requires the set of referenced
columns to be the set of columns of a unique or primary key on the
referenced table and we enforce that. Without that the behavior of all the
foreign key types becomes as complicated as the match partial behavior
because you have to be able to deal with multiple matching referenced
rows.



In response to

Responses

pgsql-admin by date

Next:From: Ramiro Batista da LuzDate: 2004-09-20 21:28:49
Subject: unsubscribe
Previous:From: Jaime CasanovaDate: 2004-09-20 20:41:51
Subject: unique constraints on foreign keys

pgsql-general by date

Next:From: jaoDate: 2004-09-20 21:03:50
Subject: Any reason not to use inheritance?
Previous:From: Jeff AmielDate: 2004-09-20 20:45:22
Subject: Re: using database for queuing operations?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group