2

I want to create a foreign key for a table using ON DELETE SET NULL, but the ALTER command is rejected. 

CREATE TABLE `Locations`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) NOT NULL,
  `image_id` int(0) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `Pictures`  (
  `id` int(0) NOT NULL,
  `url` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `Locations` 
  ADD CONSTRAINT `fk1` FOREIGN KEY (`image_id`) REFERENCES `Pictures` (`id`) ON DELETE SET NULL;

I found out so far, that there's already a BugReport in MySQL, but got closed as "expected behaviour": https://bugs.mysql.com/bug.php?id=80052 referenced here: https://stackoverflow.com/a/35042002/2889265

My question is now, how can I implement a field in Locations which might by filled with a reference to a row in Pictures using the primary key. But it's not required to be filled. The field might stay empty or is updated to be empty later on. (The location does no need to be pictured)

If that row in Pictures is deleted, the column in Locations should be resetted to NULL automatically (like i would expect the ON DELETE SET NULL function)? Do i have to build a trigger or something like that to achieve my expected behaviour.

Environments:
Dev: MariaDB 10.3.15
Prod: MySQL 8.0.2

MIB
  • 337
  • 2
  • 15
  • What exactly do you mean? I tried SET DEFAULT instead of SET NULL, but gets rejected too. – MIB Nov 08 '19 at 11:59
  • ah okay. Dont mind that. Its just a example/excerpt of the table to explain the situation. (0) has no effect on the values inside of that field (as far as i know). could have used int(11). I'll update the code to avoid misleading. – MIB Nov 08 '19 at 12:41
  • That would be the result, yes. There would be a reference ID to a not existing entry. So you make a lookup, only to find out, the ID is dead. This request could be spared, if the dead ID it would be removed from the cell. And thats the target, I want to achieve with on ON DELETE SET NULL. – MIB Nov 08 '19 at 17:56
  • http://sqlfiddle.com/#!9/d4c54b/1 You'll see 5 locations (different storages) and 3 images. 4 of the 5 locations have assigned a picture, but #2 is not yet pictured. So in a webpage there would be an empty space for the picture of this location. If someone decides to delete picture #1 there would not be a picture for location #0 and #1. The goal of my question is, how to remove the reference from the locations-table automatically on row deletion, to set the cell with the matching references to NULL. – MIB Nov 11 '19 at 06:44
  • also a blank image. – MIB Nov 11 '19 at 08:08
  • Now i dont get your point. I want NULL instead of a broken reference in the database. Thats completly different. At the moment, i have to make sure in the sourcecode that the locations table gets updated after a deletion in the picture table. And i want it to be automated through a foreign key! Is that such a unnormal approach? – MIB Nov 11 '19 at 09:38
  • This code: https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=5585131fc2c050d43ad3c853bc807557 works. – forpas Nov 11 '19 at 11:54
  • 1
    Both columns must have the same data type. So add UNSIGNED to the column id of Pictures. – forpas Nov 11 '19 at 11:55
  • That's it!!! Thank you @forpas ! Do you want to post it as an answer? – MIB Nov 11 '19 at 15:22
  • Fine if it is solved. – forpas Nov 11 '19 at 15:27

0 Answers0