Postgresql delete on cascade

Question

I have two tables. Device and Device_Config. Device table has the following columns:

device_id
config_id
bla
bla2
foo
bar

And Device_config has the following columns

id
foo
bar

As you can predict, config_id on my Device table is a foreign key referencing id column on the Device_config So I have added this constraint on my Device table.

ALTER TABLE device
ADD CONSTRAINT device_config_id_fk FOREIGN KEY (config_id)
REFERENCES device_config (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE;

But this way, when a row in Device_config is deleted, the corresponding row on the Device table is deleted. However I want the opposite. When a device is deleted, I want the corresponding entry in device_config to be deleted. How can I achieve this?

So several different devices share the same config? I might not understand your business case, but I would have expected the foreign key to point from device_config to device, i.e. a device_id in the device_config table, not the other way round — user330315
– user330315, Commented May 10, 2013 at 13:43
Please add the real DDLS. IMHO most people read sql faster than your shorthand pseudocode. — wildplasser
– wildplasser, Commented May 10, 2013 at 14:57

Cipous · Accepted Answer · 2013-05-24 11:42:44Z

4

then change the logic. Device table wont have *config_id* and device_config will have *device_id*. Relation is one device may have many device_configs.

Then

ALTER TABLE device_config
ADD CONSTRAINT device_config_fk FOREIGN KEY (device_id)
REFERENCES config (device_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE;

answered May 24, 2013 at 11:42

Cipous

1,0129 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql delete on cascade

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related