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

Joel Krajden <joelk@cs.concordia.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