Hi Tom,
Ok. You are right but I am not crazy (yet).
If I create the tables and indexes as user postgres it works like a charm.
But if I create the tables as a mortal user or create them as postgres but in the schema of user joelk and grant all to user joelk, I can insert data without the foreign key constraint being respected. Now if I drop the foreign key constraint and recreate it with a schema prefix in the references section, the constarint works fine.
alter table joelk.jk_users add FOREIGN KEY (department) REFERENCES joelk.jk_map(code);
fis=> INSERT INTO joelk.jk_users VALUES( 'fancott', 'Fancott, T.', '[EMAIL PROTECTED]', '08', 'Professor' );
ERROR: insert or update on table "jk_users" violates foreign key constraint "jk_users_department_fkey"
DETAIL: Key (department)=(08) is not present in table "jk_map".
fis=> \d jk_users Table "joelk.jk_users"
Column | Type | Modifiers
------------+------------------------+-----------
username | character varying(8) |
name | character varying(64) | not null
email | character varying(128) |
department | character varying(2) | not null
title | character varying(64) | not null
Indexes:
"jk_users_pkey" PRIMARY KEY, btree (name, department, title)
Foreign-key constraints:
"jk_users_department_fkey" FOREIGN KEY (department) REFERENCES jk_map(code)Everything also works fine if a mortal user creates the tables and indexes in the appropriate tablespaces and uses the schema.table(column_name) in the references section of the foreign key constraint.
Strange.
Not sure if this is relevant - in this database I dropped the public schema and I had to grant all to the group public so that users could create indexes in the fis_index tablespace.
fis=> \db+
List of tablespaces
Name | Owner | Location | Access privileges
------------+----------+-----------------------------------+-----------------------------------
fis | postgres | /local/data/pgsql-8.0/fis |
fis_index | postgres | /local/data/pgsql-8.0/fis_index | {postgres=C/postgres,=C/postgres}
Thanks for looking into it.
Regards
joel
Tom Lane wrote:
Joel Krajden <[EMAIL PROTECTED]> 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
-- | Joel Krajden | Rm: LB-915, Tel: 514 848-2424 3052 | | | Fax: 514 848-2830 | | Senior Systems Analyst | Email: [EMAIL PROTECTED] | | Engineering & Computer Sc.| http://www.cs.concordia.ca/~staffcs/joelk | | Concordia University | Remember it's a circus and the clowns | | Montreal, Canada | are supposed to make you laugh, not cry. |
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
