0

For example, I have these tables and data:

TABLE: logo_user
+----+---------+
| id | logo_id |
+----+---------+
| 1  | 1       |
| 2  | 2       |
+----+---------+

TABLE: logo
+----+
| id |
+----+
| 1  |
| 2  |
+----+

What I want is to delete every rows in logo table, and nullify the values that refers to it. Example:

TABLE: logo_user
+----+---------+
| id | logo_id |
+----+---------+
| 1  | NULL    |
| 2  | NULL    |
+----+---------+

TABLE: logo (now emptied)
  • I tried using TRUNCATE ... CASCADE but it also deleted every rows in the logo_user table.
  • I also consider altering the foreign key constraint of the logo_user to cascade on delete, but it's too much work. In reality I have many tables referring to logo table.
jarlh
  • 42,561
  • 8
  • 45
  • 63
sweet suman
  • 1,031
  • 8
  • 18
  • 2
    Using a FK with `on delete set null` is the only "automatic" way of doing this. It will be a lot less work then changing each every delete statement to deal with all related tables. –  Mar 29 '16 at 09:04

2 Answers2

0

you can use the below solution for your problem. 

ON DELETE SET NULL

on a foreign key in your main table will solve the problem in one step. What it basically does is : if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null (the columns that you would be mentioning in the key). The records in the child table will not be deleted, the corresponding values would be updated to null as per your requirement. For example you can refer to the below syntax : 

CREATE TABLE table_name
 (
 column1 datatype null/not null,
 column2 datatype null/not null,
  ...

 CONSTRAINT fk_col
 FOREIGN KEY (column1, column2, ... column_n)
 REFERENCES parent_table (column1, column2, ... column_n)
 ON DELETE SET NULL
 );
Aritra Bhattacharya
  • 720
  • 1
  • 7
  • 18
  • While this code may answer the question, providing additional context regarding _why_ and/or _how_ this code answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Mar 29 '16 at 10:37
  • added an explanation :) @TobySpeight – Aritra Bhattacharya Mar 29 '16 at 10:45
  • Thanks for this! I have altered your solution a little. Instead of creating a table, I updated the `logo_user` tables by dropping its foreign key constraint and adding it again with `ON DELETE SET NULL`. – sweet suman Mar 29 '16 at 11:40
0

I don't know why it's "too much work" to modify the foreign keys to ON DELETE SET NULL - it's not just the easiest way, you will have to do it since the default NO ACTION will prevent you from deleting without a cascade.

Dmitri
  • 8,999
  • 5
  • 36
  • 43