Inheritence-relations problem - Mailing list pgsql-novice

From Tom Strickland
Subject Inheritence-relations problem
Date
Msg-id 20010514112358.A2531@localhost.localdomain
Whole thread Raw
List 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
(whichinherits 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:

Previous
From: "Ken Laws"
Date:
Subject: Linking postgres databases
Next
From: "Brett W. McCoy"
Date:
Subject: Re: Linking postgres databases