0

There is a way to delete the referenced record when I delete the row with the foreign key? This is my db's tables and I want delete the 3 record referenced of the table "righe" when I delete the record in table "cartelle"

slack
  • 1
  • 1
  • No. `ON DELETE CASCADE` allows deletion of the rows which reference a `primary key` when the row with that `primary key` value is deleted. It would be odd to try to delete in the other direction. Maybe you want to create a FK constraint in the other table. – Jon Armstrong Oct 23 '21 at 11:24
  • 2
    Add the tables (in text form) to the question. Explain the relationship and meaning of the tables and columns. It's possible you just want the references to be reversed. – Jon Armstrong Oct 23 '21 at 11:31
  • Does this answer your question? [MySQL foreign key constraints, cascade delete](https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete) – Don Cruickshank Oct 23 '21 at 11:42

1 Answers1

0

Just add your reference in the other direction, like this:

Fiddle

-- cartelle (folder)

CREATE TABLE folders ( folder_id int primary key );

-- righe (rows, tied to a folder)

CREATE TABLE xrows   (
    row_id    int primary key
  , folder_id int
  , FOREIGN KEY (folder_id) REFERENCES folders (folder_id) ON DELETE CASCADE
);


INSERT INTO folders VALUES (1), (2), (3);

INSERT INTO xrows VALUES
    (1, 1)
  , (2, 1)
  , (3, 1)
  , (4, 2)
  , (5, 3)
  , (6, 2)
;
row_id folder_id
1 1
2 1
3 1
4 2
5 3
6 2

Now when we delete a folder, the corresponding rows will be deleted.

DELETE FROM folders WHERE folder_id = 1;

SELECT * FROM xrows;

The result:

row_id folder_id
4 2
5 3
6 2
Jon Armstrong
  • 4,654
  • 2
  • 12
  • 14