pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedunique constraints on foreign keys
4+ messages / 2 participants
[nested] [flat]
* unique constraints on foreign keys
@ 2004-09-20 20:41 Jaime Casanova <systemguards@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jaime Casanova @ 2004-09-20 20:41 UTC (permalink / raw)
To: pgsql-admin
Hi all,
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?
(I mean, a way that not involves the use of a "set"
instruction because the odbc not allow such
instructions)
Thanx in advance,
Jaime Casanova
_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unique constraints on foreign keys
@ 2004-09-20 21:02 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Jaime Casanova <systemguards@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2004-09-20 21:02 UTC (permalink / raw)
To: Jaime Casanova <systemguards@yahoo.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unique constraints on foreign keys
@ 2004-09-20 23:18 Jaime Casanova <systemguards@yahoo.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jaime Casanova @ 2004-09-20 23:18 UTC (permalink / raw)
To: pgsql-admin
--- Stephan Szabo <sszabo@megazone.bigpanda.com>
escribió:
>
> On Mon, 20 Sep 2004, Jaime Casanova wrote:
>
> > 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.
OK. What about to teach the planner always do a
seq on that table and do not ask for use the
index? but not using "set" instructions. can i do
that?
_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unique constraints on foreign keys
@ 2004-09-20 23:27 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Jaime Casanova <systemguards@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2004-09-20 23:27 UTC (permalink / raw)
To: Jaime Casanova <systemguards@yahoo.com>; +Cc: pgsql-admin
On Mon, 20 Sep 2004, Jaime Casanova wrote:
> --- Stephan Szabo <sszabo@megazone.bigpanda.com>
> escribió:
> >
> > On Mon, 20 Sep 2004, Jaime Casanova wrote:
> >
> > > 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.
>
> OK. What about to teach the planner always do a
> seq on that table and do not ask for use the
> index? but not using "set" instructions. can i do
> that?
Not really. Are you worried about the cost of doing the plan analysis for
the index scan or that it might choose to actually use the index?
Theoretically, it's only going to currently plan the fk scan on status
once per connection because that plan should be saved.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-09-20 23:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-20 20:41 unique constraints on foreign keys Jaime Casanova <systemguards@yahoo.com>
2004-09-20 21:02 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-09-20 23:18 ` Jaime Casanova <systemguards@yahoo.com>
2004-09-20 23:27 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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