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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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