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: [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 6: Have you searched our list archives?
http://archives.postgresql.org
