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