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
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', |