0

I am trying to add a new table to MySQL but I am getting the cannot add foreign key constraint error.

The SQL i am trying to execute is :

CREATE TABLE `local_news` (
  `local_news_id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` int(11) NOT NULL,
  `org_contact_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL COMMENT 'title',
  `message` text DEFAULT NULL,
  `published` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0-draft, 1-published',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`local_news_id`),
  KEY `FK_Reference_56` (`org_id`),
  KEY `FK_Reference_57` (`org_contact_id`),
  CONSTRAINT `FK_Reference_56` FOREIGN KEY (`org_id`) REFERENCES `organization` (`org_id`),
  CONSTRAINT `FK_Reference_57` FOREIGN KEY (`org_contact_id`) REFERENCES `org_contacts` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='All local site content in the news tab';

My 'organization' table has 'org_id' as int(11) and set as its primary key.

My 'org_contacts' table has 'contact_id' as int(11) and set as its primary key.

The complete error I found (while using show engine innodb status) is:

------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2017-02-15 22:28:27 0x2b64 Error in foreign key constraint of table giveaday/local_news:

FOREIGN KEY (`org_id`) REFERENCES `organization` (`org_id`),
 CONSTRAINT `FK_Reference_57` FOREIGN KEY (`org_contact_id`) REFERENCES `org_contacts` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='All local site content in the news tab':
Cannot resolve table name close to:
(`org_id`),
 CONSTRAINT `FK_Reference_57` FOREIGN KEY (`org_contact_id`) REFERENCES `org_contacts` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='All local site content in the news tab'

However the table "organization" does exist and is exactly called "organization", no typos made.

Dennis
  • 3,044
  • 2
  • 33
  • 52
  • post the full error – Mithilesh Gupta Feb 15 '17 at 21:30
  • @MithileshGupta I edited my post showing the full error stack – Dennis Feb 15 '17 at 21:34
  • 2
    Does organization.org_id exist and is of type int(11)? Does org_contacts.contact_id exist, and is of type int(11)? Are both referenced tables INNODB? http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint – Ehz Feb 15 '17 at 21:38
  • Have you tried giving the keys and constraints different names? – Uueerdo Feb 15 '17 at 21:39
  • Please post table structure for the referenced tables. – r0xette Feb 15 '17 at 21:40
  • @Ehz changing the referenced tables to InnoDB did the trick! Thank you so much. Had no idea they were not set as InnoDB, but rather as MyISAM. Does this have any consequences when changing it to InnoDB? – Dennis Feb 15 '17 at 21:43
  • 1
    I am not aware of specific issues that can appear during the conversion process. This article has some differences between the two after the conversion has happened though: https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html – Ehz Feb 15 '17 at 21:52

0 Answers0