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

Re: foreign key constraint not working when index tablespace is not default.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joel Krajden <joelk(at)cs(dot)concordia(dot)ca>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: foreign key constraint not working when index tablespace is not default.
Date: 2005-03-27 07:39:09
Message-ID: 3393.1111909149@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Joel Krajden <joelk(at)cs(dot)concordia(dot)ca> writes:
> If the indexes are created in fis_index, the foreign key constraints in the 
> user table are ignored on insert and update.

Works for me...

$ mkdir /tmp/fis
$ mkdir /tmp/fis_index
$ psql regression
...
regression=# create tablespace fis location '/tmp/fis';
CREATE TABLESPACE
regression=# create tablespace fis_index location '/tmp/fis_index';
CREATE TABLESPACE
regression=# \i joel.sql
psql:joel.sql:11: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dept_map_pkey" for table "dept_map"
CREATE TABLE
psql:joel.sql:23: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "card_category_map_pkey" for table "card_category_map"
CREATE TABLE
psql:joel.sql:38: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fis_title_map_pkey" for table "fis_title_map"
CREATE TABLE
psql:joel.sql:57: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE
regression=# insert into users values('username','name','email','dp','title');
ERROR:  insert or update on table "users" violates foreign key constraint "users_department_fkey"
DETAIL:  Key (department)=(dp) is not present in table "dept_map".
regression=# insert into dept_map values('dp','desc');
INSERT 0 1
regression=# insert into users values('username','name','email','dp','title');
ERROR:  insert or update on table "users" violates foreign key constraint "users_title_fkey"
DETAIL:  Key (title)=(title) is not present in table "fis_title_map".
regression=# insert into fis_title_map values('title','cardcat');
ERROR:  insert or update on table "fis_title_map" violates foreign key constraint "fis_title_map_card_category_fkey"
DETAIL:  Key (card_category)=(cardcat) is not present in table "card_category_map".
regression=# insert into card_category_map values('cardcat','desc');
INSERT 0 1
regression=# insert into fis_title_map values('title','cardcat');
INSERT 0 1
regression=# insert into users values('username','name','email','dp','title');
INSERT 0 1
regression=# 

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-03-27 07:41:18
Subject: Re: BUG #1556: psql ON_ERROR_STOP returns 3 when scripts ends in C style comment
Previous:From: Tom LaneDate: 2005-03-27 07:26:02
Subject: Re: BUG #1563: wrong week returnded by date_trunc('week',

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