Re: Query planner and foreign key constraints

Filip Rembiałkowski wrote:
>
> create table table1 (
> key1 char(12),
> key2 integer,
> primary key (key1, key2)
> );
>
> create table table2 (
> key1 char(12),
> key2 integer,
> key3 varchar(20),
> primary key (key1, key2, key3),
> foreign key (key1, key2) references table1 (key1, key2)
> );
>
>
> just a guess, but - did you try to declare NOT NULL on FK columns?
>
> your assumption that "for each row in table2 there *must* exist a row
> in table1" will be enforced then.
>
> maybe the planner will make use of this ...
All columns are implictly declared "not null" because they are part of
the primary key of the tables:

# \d table1
Table "public.table1"
Column | Type | Modifiers
--------+---------------+-----------
key1 | character(12) | not null
key2 | integer | not null
Indexes:
"table1_pkey" PRIMARY KEY, btree (key1, key2)

# \d table2
Table "public.table2"
Column | Type | Modifiers
--------+-----------------------+-----------
key1 | character(12) | not null
key2 | integer | not null
key3 | character varying(20) | not null
Indexes:
"table2_pkey" PRIMARY KEY, btree (key1, key2, key3)
Foreign-key constraints:
"table2_key1_fkey" FOREIGN KEY (key1, key2) REFERENCES table1(key1,
key2)

Regards,
Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer