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

Question

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.

Answer 1

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: