How to create one table that has a common identifier with another, and which row is deleted in both tables after it is deleted from the first

Question

How to create one table that has a common identifier with another and which row will be deleted in both tables after it is deleted the first time?

I heard about FOREIGN KEY, REFERENCES, but I don’t know how to create such tables. Any example to get me started?

Answer 1

I think you are talking about cascading deletion or something really strange that you shouldn't do. :)

Foreign key information: http://www.postgresql.org/docs/8.3/static/tutorial-fk.html

Information on cascading strikes (search on the "ON DELETE CASCADE" page): http://www.postgresql.org/docs/8.2/static/ddl-constraints.html

Edit: add example:

Creating a table:

CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, ... ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) ); 

Example of running cascading deletion:

 DELETE FROM orders -- This will delete all orders with WHERE order_id BETWEEN 1 AND 10 ; -- order_id between 1 and 10 and -- all associated order items.