MySQL - Foreign key on delete set null in not null field

create-table, foreign-key-relationship, mysql, notnull, sql

Solution

If you set `ON DELETE SET NULL` to your foreign key then it won't allow you to set the field as `NOT NULL`.

So you won't be able to create or alter the table with column as `NOT NULL` and `ON DELETE SET NULL` on CountryId

When I run the below statements:

CREATE TABLE `country` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `city` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `countryId` int(10) unsigned DEFAULT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_country` (`countryId`),
  CONSTRAINT `FK_country` FOREIGN KEY (`countryId`) REFERENCES `country` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
);

And I got the error in `MySQL 5.5` is:

Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_country` (`countryId`),
  CONSTRAINT `' at line 4:

Problem

This is probably a trivial question, but I'm still a little clumsy when it comes to foreign key constraints so I wanted to make sure. Let's say I have a table `countries` with the fields `country_id` (PK) and `name`, and a table `cities` with the fields `city_id` (PK), `name` and `country_id` (FK). The foreign key `cities.country_id` has the constraint `ON DELETE SET NULL`. As I understand it, this means that if a record from `countries` is deleted, any records in `cities` that reference that deleted record's `country_id` will have its `country_id` field set to NULL. What if, however, `cities.country_id` has the attribute `NOT NULL`? Will this prevent the foreign key constraint from working properly? It would make sense that it does, but I just want to check.

Original source