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

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 (view raw or flat)
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

pgsql-novice by date

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

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