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