CODEWITHSUNDEEP
mysql
ssj
ssj

Reputation: 1797

ERROR 1215: Cannot add foreign key constraint when using ON DELETE SET NULL

I'm trying to create the following tables in MySQL. The first one:

CREATE TABLE IF NOT EXISTS address(
  address_id INT NOT NULL AUTO_INCREMENT,
  address_region VARCHAR(10) NOT NULL,
  address_country VARCHAR(20) NOT NULL,
  address_city VARCHAR(30) NOT NULL,
  PRIMARY KEY(address_id))ENGINE = InnoDB;

I created it successfully,but when I try to create another table as following

CREATE TABLE IF NOT EXISTS spot(
  spot_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  spot_address INT(11) NOT NULL,
  spot_name VARCHAR(50) NOT NULL,
  spot_desc VARCHAR(500) DEFAULT ' ',
  spot_speadd VARCHAR(100) NOT NULL,
  spot_viewtime INT DEFAULT 0,
  FOREIGN KEY(spot_address)
  REFERENCES address(address_id)
  ON DELETE SET NULL
  ON UPDATE SET NULL);

I get an error:

ERROR 1215 (HY000): Cannot add foreign key constraint

Why is this create table statement failing?

Upvotes: 2

Views: 6039

Answers (2)

user2045846
user2045846

Reputation:

You can try this:

CREATE TABLE IF NOT EXISTS address
(
address_id INT NOT NULL AUTO_INCREMENT,
address_region VARCHAR(10) NOT NULL,
address_country VARCHAR(20) NOT NULL,
address_city VARCHAR(30) NOT NULL,
PRIMARY KEY(address_id)
)ENGINE = INNODB;




CREATE TABLE IF NOT EXISTS `spot`
(
  `spot_id` INT(11) NOT NULL AUTO_INCREMENT,
  `spot_address` INT(11) NOT NULL,
  `spot_name` VARCHAR(50) NOT NULL,
  `spot_desc` VARCHAR(500) DEFAULT ' ',
  `spot_speadd` VARCHAR(100) NOT NULL,
  `spot_viewtime` INT(11) DEFAULT '0',
  PRIMARY KEY (`spot_id`),
  KEY `spot_address` (`spot_address`),
  CONSTRAINT `spot_ibfk_1` FOREIGN KEY (`spot_address`) REFERENCES `address` (`address_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

Upvotes: 0

Yasir Arsanukayev
Yasir Arsanukayev

Reputation: 9676

You have NOT NULL constraint on spot_address in the spot table and then try to set it to NULL on delete or update in the parent table address.

Upvotes: 5

Related Questions