How to create one table which shares common id wit

Question

How to create one table which shares common id with another and which row gets removed in both tables once it's removed from first?

I heard about FOREIGN KEY, REFERENCES but not sure how to actually create such tables. Any example to get me started?

Answer 1

I think you're either talking about a cascading delete or something really weird that you shouldn't do. :)

Info on foreign keys: http://www.postgresql.org/docs/8.3/static/tutorial-fk.html

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

Edit: adding example:

Table creation:

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 triggering cascading delete:

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.