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

Re: Query planner and foreign key constraints

From: Christian Schröder <cs(at)deriva(dot)de>
To: Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com>
Cc: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Query planner and foreign key constraints
Date: 2008-12-30 08:34:27
Message-ID: 4959DD13.6020504@deriva.de (view raw or flat)
Thread:
Lists: pgsql-general
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



In response to

pgsql-general by date

Next:From: Reg Me PleaseDate: 2008-12-30 09:02:04
Subject: Re: [PGSQL 8.3.5] Use of a partial indexes
Previous:From: Julius TuskenisDate: 2008-12-30 07:46:39
Subject: Re: Load Image File From PostgreSQL DB

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