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

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 )

);

--
| Joel Krajden | Rm: LB-915, Tel: 514 848-2424 3052 |
| | Fax: 514 848-2830 |
| Senior Systems Analyst | Email: joelk(at)cs(dot)concordia(dot)ca |
| 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. |