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.
Best Answer
Some clarifications:
REFERENCES
is the key word used for aFOREIGN KEY
constraint (which allows to cascadeDELETE
orUPDATE
).Your database design seems to have logical flaws.
rating
seems like a detail of the main tablerestaurant
. 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 arestaurant_id
in therating
table rather than the other way round.Your "rating" columns
average, vote_count and vote_sum
indicate another tablevote
and these values are derived aggregates. AMATERIALIZED VIEW
would be the typical solution for that. Either as separaterating
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 withON DELETE CASCADE
.If you still need your current design, I have two ideas:
1. Multiple FK columns in one
rating
tableWith FK constraints reversed to point in the right direction, the table could look like this:
The
UNIQUE
constraints enforce your 1:1 requirement. Each row in each master table can have at most one row inrating
. 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:
To enforce that exactly one FK column per row is
NOT NULL
: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:
2. 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: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:
You might want to add a rule or trigger to disallow inserts into the master table
rating
directly.SQL Fiddle.
Related: