What you did was create an n-to-m relationship between knives and notes. Are you sure that is what you need?
Because, the way your datamodel is set up now, it is actually desirable that notes aren't deleted. The current model allows (amongst others):
A. A knife that has 1 note
B. A specific knife that has more than 1 note (2 rows in knife_notes that point to the same knife and to different notes)
C. A specific note that is related to multiple knives
Because of scenario C the database can't just cascade from the knive_notes table to the notes table: there might be other tables that are dependent on a specific note.
To make it visual, think of this scenario in the knive_notes table:
id knife_id note_id
--------------------------------------
1 11 101
2 11 102
3 12 103
4 13 103
From a database point of view this is perfectly legal; note 103 is used for both knive 12 and 13. Now if you were to delete knive 12, the database can't simply remove note 103, because it could still be used by other knives (and in fact it is).
There are 2 sollutions I can think of:
1. simplify your datamodel
2. create a trigger or rule in PostgreSQL
Expanding on the options:
- datamodel:
Why don't you create a datamodel that only has
- Knive
- Note (with a foreign key to knive)
This way a knive can have multiple notes, but a note always is related to a knive. This will not work, though, if a note is used multiple times or in multiple roles (ie. if you also have a table "guns" that also needs notes or if a specific note is used by multiple rows in the knive table ("the color of this knive is red")
- triggers or rules
With rules you can rewrite your SQL query. With triggers you can execute a function just before or after an operation on a table.
Look up triggers & rewrite rules.
Myself, I'm most used to working with triggers. I would basically create an on-delete trigger that starts a trigger function. In that function I would check if the note isn't used anywhere else and if that is the case execute a delete on the note.
CREATE FUNCTION try_to_delete_note() RETURNS trigger AS ##
DECLARE
usage_found int;
BEGIN
-- Check that note is not used anywhere else
usage_found = (select count(*) from knive_notes where note_id = OLD.note_id)
IF (usage_found = 0) THEN
DELETE from note where id = OLD.note_id
END IF;
RETURN NULL; -- result is ignored since this is an after trigger
END;
## LANGUAGE plpgsql;
CREATE TRIGGER knife_notes_deleted_trigger
AFTER DELETE ON knife_notes
FOR EACH ROW
EXECUTE PROCEDURE try_to_delete_note();
Also see this page in postgresql docs with trigger examples.
