Inheritence-relations problem

From: Tom Strickland <tom(at)stricklandc(dot)demon(dot)co(dot)uk>
To: Postgres Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Inheritence-relations problem
Date: 2001-05-14 15:23:58
Message-ID: 20010514112358.A2531@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,
For our CRM system, I am trying to define the following many-to-many
relationship:
table 1: 'problems' has primary key 'prob_id'
clients come to our charity with problems that they want help with

table 2: 'actions' has primary key 'action_id'
employees spend time on an action. Some actions are spent working on
client_problems, so we define a new table that extends action:

table 3: 'prob_actions' inherits from action

table 4: is used to manage a many-to-many relationship between problems and prob_actions
When creating this table and its constraints, I get an error, as shown below:

crm=# CREATE TABLE prob_action_rel (
crm(# prob_id INTEGER,
crm(# action_id INTEGER,
crm(# CONSTRAINT prob_action_rel_key PRIMARY KEY(prob_id, action_id));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'prob_action_rel_key' for table 'prob_action_rel'
CREATE
crm=# ALTER TABLE prob_action_rel
crm-# ADD CONSTRAINT prob_id_fk
crm-# FOREIGN KEY(prob_id)
crm-# REFERENCES problem(prob_id)
crm-# ON UPDATE CASCADE;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
crm=# ALTER TABLE prob_action_rel
crm-# ADD CONSTRAINT prob_id_fk
crm-# FOREIGN KEY(prob_id)
crm-# REFERENCES problem(prob_id)
crm-# ON UPDATE CASCADE;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
crm=# ALTER TABLE prob_action_rel
crm-# ADD CONSTRAINT action_id_fk
crm-# FOREIGN KEY(action_id)
crm-# REFERENCES prob_actions(action_id)
crm-# ON UPDATE CASCADE;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "prob_actions" not found

What's wrong? If I change the last ALTER statement to relate to the actions table instead of the prob_actions table (which inherits from actions), the I don't get the error. Is there a way that I can get around this problem?

Thanks,

Tom

Browse pgsql-novice by date

  From Date Subject
Next Message Danny Aldham 2001-05-15 01:58:23 Drop table wildcard
Previous Message Matus fantomas Uhlar 2001-05-14 15:09:42 convert timestamp to number ?