How to create one table which shares common id wit

问题:

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?

回答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.