Skip site navigation (1) Skip section navigation (2)

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

From: Joel Krajden <joelk(at)cs(dot)concordia(dot)ca>
To: pgsql-bugs(at)postgresql(dot)org
Cc: Joelk(at)cs(dot)concordia(dot)ca
Subject: foreign key constraint not working when index tablespace is not default.
Date: 2005-03-23 20:22:30
Message-ID: 4241D006.1000605@cs.concordia.ca (view raw or flat)
Thread:
Lists: pgsql-bugs
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.  |

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2005-03-23 20:31:09
Subject: Re: BUG #1517: SQL interval syntax is accepted by the parser,
Previous:From: Tom LaneDate: 2005-03-23 20:12:43
Subject: Re: BUG #1552: massive performance hit between 7.4 and 8.0.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group