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.

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: 

Comments

[Rachcha]

Simple question- have you tried it yourself?

[Bobe]

Are you sure? Because I created the model in MySQL Workbench like that and it allowed me to export and import into phpMyAdmin. I think that it doesn't prevent you from creating it, but it just won't work when you try to delete records.

[Saharsh Shah]

@Bobe I had tried in MySQL 5.5 and I am not able to alter my table. And the error is: Error on rename of '.\rptest\#sql-694_1' to '.\rptest\city' (errno: 150)

[Bobe]

Hmm, the server I'm working with is running MySQL 5.0.96. Maybe that syntax check isn't in place in that version.