Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating this rating schema for e.g pictures I moved them to separate rating table and only store the rating_id in restaurant.

I know that 1 rating will only be used by 1 other row in the whole database.
How can I cascade a delete to rating when deleting a row in restaurant or picture?

I've been looking around but all I can find is set a REFERENCES, but for that I need to know which table data will be deleted.

I know triggers would do the job, I was simply hoping for something more elegant.

Some clarifications:

-
REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE).

-
Your database design seems to have logical flaws. rating seems like a detail of the main table restaurant. Since you have a 1:1 relationship, you could just include the "rating" columns in the main table. If you need a separate table, you would include a restaurant_id in the rating table rather than the other way round.

-
Your "rating" columns average, vote_count and vote_sum indicate another table vote and these values are derived aggregates. A MATERIALIZED VIEW would be the typical solution for that. Either as separate rating table or in combination with columns from each main table ...

The clean way would be to have a separate rating table for each main table. Then you can have a FK constraint on each with ON DELETE CASCADE.

If you still need your current design, I have two ideas:

1. Multiple FK columns in one rating table

With FK constraints reversed to point in the right direction, the table could look like this:

CREATE TABLE rating (
  rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
, restaurant_id int UNIQUE REFERENCES restaurant(restaurant_id)
                    ON UPDATE CASCADE ON DELETE CASCADE
, picture_id int UNIQUE REFERENCES picture(picture_id)
                 ON UPDATE CASCADE ON DELETE CASCADE
-- more references to other tables
);

The UNIQUE constraints enforce your 1:1 requirement. Each row in each master table can have at most one row in rating. Also creates very useful indexes automatically.

Alternatively, if you have several main tables, you might want to create partial unique indexes instead to exclude irrelevant rows from each index:

CREATE UNIQUE INDEX rating_restaurant_id ON rating (restaurant_id)
WHERE restaurant_id IS NOT NULL;
-- etc.

To enforce that exactly one FK column per row is NOT NULL:

ALTER TABLE rating ADD CONSTRAINT exactly_one_fk CHECK (
      (restaurant_id IS NOT NULL)::int
    + (picture_id    IS NOT NULL)::int = 1);  -- add more ...

With a couple of master tables, it may look like a lot of wasted storage but it really is not. A bunch of NULL columns hardly cost anything. NULL storage is very cheap:

• How do completely empty columns in a large table affect performance?

1. Inheritance

Since your motivation is to avoid repeating this rating schema, inheritance could be a good solution for you. A limitation of table inheritance is that foreign keys of the parent table are not inherited - which turns into an advantage for your case:

CREATE TABLE rating (
  rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
);

CREATE TABLE restaurant_rating (
   restaurant_id int PRIMARY KEY REFERENCES restaurant(restaurant_id)
                                 ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

CREATE TABLE picture_rating (
   picture_id int PRIMARY KEY REFERENCES picture (picture_id)
                              ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

-- more?

-
Now you have to define the basic schema only once and inherit from it.

-
You still have a common PK column for all ratings with a single attached sequence.

-
Each child table adds the ID of the master table as PK and FK, thus enforcing your 1:1 relationship and providing the important index on the column automatically.

-
You can query the master table to get all ratings at once. If you need to know from which child table each row originates:

SELECT tableoid::regclass::text AS origin, *
FROM   rating;

• Get all partition names for a table

• Retrieve all records from multiple schemas

-
You might want to add a rule or trigger to disallow inserts into the master table rating directly.

SQL Fiddle.

Related:

• Use triggers on inherited tables to replace foreign keys

• Database design - should two projects share the same table?

StackExchange Database Administrators Q#128915, answer score: 5