0

I have the following tables

-- basic table
CREATE TABLE IF NOT EXISTS people (
  id serial PRIMARY KEY,
  person_id TEXT NOT NULL
);

-- the other side of the join
CREATE TABLE IF NOT EXISTS stats (
  id serial primary key,
  name TEXT,
  description TEXT
);

-- my join table
CREATE TABLE IF NOT EXISTS peoplestats (
  id serial PRIMARY KEY,
  person_id INTEGER NOT NULL REFERENCES people(id) on delete cascade,
  stat_id INTEGER NOT NULL REFERENCES stats(id),
  value numeric NOT NULL,
  created_at timestamptz default timezone('utc', now())
);

https://www.db-fiddle.com/f/nn4Uh8YGqXkCTJZGowB6hN/0

Is it right to only use the cascade on on the person_id in the Peoplestats table, or should /both/ REFERENCES statements have cascades?

In particular, how do I execute a deletion for a record formed from

select * 
from people p
left join peoplestats ps on p.id = ps.person_id
left join stats s on s.id = ps.stat_id
where p.id = 'blah'

such that I delete a row in People, and I delete the corresponding Peoplestats, but I don't delete the corresponding stats unless it has no other Peoplestats referring to it?

Mittenchops
  • 18,633
  • 33
  • 128
  • 246
  • Answer: You need cascading deletion for _both_ foreign keys in the junction table. The accepted answer in the duplicate link nicely shows you how to do this. – Tim Biegeleisen Mar 15 '21 at 03:00
  • Sorry, and I understand this is a 101 question, so I appreciate the dedupe, but doesn't having both on delete cascades cause the stats record to get deleted even if other peoplestats have the same stat? Also, what is the delete syntax I should use to do the deletion? – Mittenchops Mar 15 '21 at 03:03
  • If you delete a record from the junction table, based on either an input person _or_ stat, MySQL will cascade down to _both_ child tables and do deletions there. So, the functionality you want I think is already here. There is no special syntax, just delete as usual. – Tim Biegeleisen Mar 15 '21 at 03:05

0 Answers0