I have two tablespaces: fis (default) and fis_index. The users are required to
create the tables in fis and the indexes if fis_index.

If the indexes are created in fis_index, the foreign key constraints in the
user table are ignored on insert and update.

The constraints work properly if the indexes are created in the default
tablespace.

Joel

----------

fis=> SELECT version();
version
------------------------------------------------------------------------
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
(1 row)


-- Department Mappings
-- -------------------
-- Mapping between the department code and the full department name.

CREATE TABLE dept_map (

code VARCHAR(2),
description VARCHAR(64),

PRIMARY KEY( code ) USING INDEX TABLESPACE fis_index

-- Card Access category mappings.
-- ------------------------------
-- Different access level to the card access application.

CREATE TABLE card_category_map (

code VARCHAR(16),
description VARCHAR(64),

PRIMARY KEY( code ) USING INDEX TABLESPACE fis_index
);

-- Fis Category Mappings
-- ---------------------
-- Mapping between categories in FIS and access levels in card access.

CREATE TABLE fis_title_map (

fis_title VARCHAR(64),
card_category VARCHAR(64),

PRIMARY KEY( fis_title ) USING INDEX TABLESPACE fis_index,

FOREIGN KEY( card_category ) REFERENCES card_category_map( code )

);

-- Users
-- --------------------
-- Table of users with valid accounts to the application.
--
CREATE TABLE users (

username VARCHAR(8),
name VARCHAR(64),
email VARCHAR(128),
department VARCHAR(2),
title VARCHAR(64),

PRIMARY KEY( name, department, title ) USING INDEX TABLESPACE fis_index,

FOREIGN KEY( department ) REFERENCES dept_map( code ),
FOREIGN KEY( title ) REFERENCES fis_title_map( fis_title )

);

--
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Joel Krajden <joelk (AT) cs (DOT) concordia.ca> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Joel Krajden <joelk (AT) cs (DOT) concordia.ca> writes:
This is even harder to believe than the first report. Could we see a
complete, self-contained test case? A SQL script that demonstrates the
problem from a standing start in an empty database is what I have in mind.

(What I suspect is that you have multiple similarly-named tables in
different schemas and are getting confused by that...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match