Subject: Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)
From: Hannu Krosing <hannu@tm.ee>
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
In-Reply-To: <200309291604.57988.shridhar_daithankar@persistent.co.in>
References: <200309281940.h8SJe3O01852@candle.pha.pa.us>
	<1064829433.2645.2.camel@fuji.krosing.net>
	<3F78095C.5080507@familyhealth.com.au>
	<200309291604.57988.shridhar_daithankar@persistent.co.in>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1064838206.2645.5.camel@fuji.krosing.net>
Mime-Version: 1.0
Date: Mon, 29 Sep 2003 15:23:26 +0300

Shridhar Daithankar kirjutas E, 29.09.2003 kell 13:34:
> On Monday 29 September 2003 15:58, Christopher Kings-Lynne wrote:
> > >>So a db designer made a bloody mistake.
> > >>The problem is there's no easy way to find out what's missing.
> > >>I'd really like EXPLAIN to display all subsequent triggered queries
> > >>also, to see the full scans caused by missing indexes.
> > >
> > > It could probably be doable for EXPLAIN ANALYZE (by actually tracing
> > > execution), but then you will see really _all_ queries, i.e. for a 1000
> > > row update you would see 1 UPDATE query and 1000 fk checks ...
> > >
> > > OTOH, you probably can get that already from logs with right logging
> > > parameters.
> >
> > Actually - it shouldn't be too hard to write a query that returns all
> > unindexed foreign keys, surely?
> 
> Correct me if I am wrong but I remember postgresql throwing error that foreign 
> key field was not unique in foreign table. Obviously it can not detect that 
> without an index. Either primary key or unique constraint would need an 
> index.
> 
> What am I missing here?
> 
> 
> IOW, how do I exactly create foreign keys without an index?

hannu=# create table pkt(i int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'pkt_pkey' for table 'pkt'
CREATE TABLE
hannu=# create table fkt(j int references pkt);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE TABLE
hannu=#

now the *foreygn key* column (fkt.j) is without index. As foreign keys
are enforced both ways, this can be a problem when changing table pkt or
bulk creating FK's on big tables.

----------------
Hannu