mysql cannot add foreign key for unknown reason

Question

I cannot a add a foreign key constraint. the sql i m running is -

 ALTER TABLE image_shout ADD CONSTRAINT `fk_image` FOREIGN KEY (image_id) 
REFERENCES images(image_id);

the collation and the data types( int(10) ) are same in the two tables.

mysql says -

Error Code: 1215. Cannot add foreign key constraint

The images table structure -

CREATE TABLE `images` (
  `image_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `image_name` varchar(100) CHARACTER SET latin1 NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `caption` varchar(450) CHARACTER SET latin1 DEFAULT NULL,
  `image_visibility` int(10) unsigned NOT NULL,
  `album_id` int(10) unsigned NOT NULL,
  `album_view` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `album_thumb_view` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`image_id`),
  KEY `Index_2` (`album_id`),
  CONSTRAINT `FK_images_1` FOREIGN KEY (`album_id`) REFERENCES `photo_album` (`Album_ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4314 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The image_shout table -

CREATE TABLE `image_shout` (
  `auto_id` int(11) NOT NULL AUTO_INCREMENT,
  `shout_id` int(11) DEFAULT NULL,
  `image_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1132 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE -

The new error after changing the image_id column to unsigned is -

 Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails
 (`void`.`#sql-36b_7285`, CONSTRAINT `fk_image` FOREIGN KEY (`image_id`) 
  REFERENCES `images` (`image_id`) ON DELETE SET NULL ON UPDATE CASCADE)

Regards

Can you add the table create statement of both tables please? — Jens, Jul 23 '14 at 12:57
Does images have a composite key and you're only referencing one element of that composite key? — xQbert, Jul 23 '14 at 12:59
@abalos nope it has data, xQbert- nope I m just editing the question with the table structure — Pradyut Bhattacharya, Jul 23 '14 at 13:01
Unsigned vs signed. need to be EXACTLY the same data types. See: http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint — xQbert, Jul 23 '14 at 13:03

score 0 · Answer 1 · answered Jul 23 '14 at 13:07

The problem is below. According to the documentation, a foreign key must be indexed. Further, a foreign key should reference the KEY of another table. Not just a column. Try using auto_id as the constraint if you don't wish to change your table structure.

Please see MySQL Documentation for the list of requirements of a Foreign Key constraint.

CREATE TABLE `image_shout` (
  `auto_id` int(11) NOT NULL AUTO_INCREMENT,
  `shout_id` int(11) DEFAULT NULL,
  `image_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`auto_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1132 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

score 0 · Accepted Answer · answered Jul 23 '14 at 13:13

0

Is because the image_id in table images is defined as unsigned

`image_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

and in image_shout not

`image_id` int(10) DEFAULT NULL,

Change both columns to the same data type and it should work.

answered Jul 23 '14 at 13:13

Jens

67,715
15
98
113

@PradyutBhattacharya Look [here](http://stackoverflow.com/questions/22210461/mysql-error-code-1452-foreign-key-constraint) looks like the same problem. – Jens Jul 23 '14 at 13:22
1

I suppose there are values in child table which dont exist in the primary table. right? – Pradyut Bhattacharya Jul 23 '14 at 13:39
Yes it sounds like that. – Jens Jul 23 '14 at 13:43
So you either have to purge the child records, or add parent records to compensate. Context of the situation should drive your action. If parents were removed, it's likely the children can too, unless there's a coding flaw and the parents shouldn't have been removed... thus we can't say what's right as we're unaware of complete context. – xQbert Jul 23 '14 at 13:48
I removed the child rows with null rows in the parent and its done. In the constraint i mentioned cascade on delete. But then If i mentioned set null on delete in constraint then why should i require to remove the nulls in the child table. Seems a mysql bug to me. – Pradyut Bhattacharya Jul 23 '14 at 14:12
if you set the caacade delete to existing data will not changed. It is only for new actions in the database. – Jens Jul 23 '14 at 14:17

mysql cannot add foreign key for unknown reason

2 Answers2