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@cs.concordia.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. |
