Two n x m relationships with the same table in mysql

Question

I want to create a database in which there's an n x m relationship between the table drug and the table article and an n x m relationship between the table target and the table article.

I get the error: Cannot delete or update a parent row: a foreign key constraint fails What do I have to change in my code?

DROP TABLE IF EXISTS `textmine`.`article`;
CREATE TABLE  `textmine`.`article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Pubmed ID',
  `abstract` blob NOT NULL,
  `authors` blob NOT NULL,
  `journal` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `textmine`.`drugs`;
CREATE TABLE  `textmine`.`drugs` (
  `id` int(10) unsigned NOT NULL COMMENT 'This ID is taken from the biosemantics dictionary',
  `primaryName` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `textmine`.`targets`;
CREATE TABLE  `textmine`.`targets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `primaryName` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `textmine`.`containstarget`;
CREATE TABLE  `textmine`.`containstarget` (
  `targetid` int(10) unsigned NOT NULL,
  `articleid` int(10) unsigned NOT NULL,
  KEY `target` (`targetid`),
  KEY `article` (`articleid`),
  CONSTRAINT `article` FOREIGN KEY (`articleid`) REFERENCES `article` (`id`),
  CONSTRAINT `target` FOREIGN KEY (`targetid`) REFERENCES `targets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `textmine`.`contiansdrug`;
CREATE TABLE  `textmine`.`contiansdrug` (
  `drugid` int(10) unsigned NOT NULL,
  `articleid` int(10) unsigned NOT NULL,
  KEY `drug` (`drugid`),
  KEY `article` (`articleid`),
  CONSTRAINT `article` FOREIGN KEY (`articleid`) REFERENCES `article` (`id`),
  CONSTRAINT `drug` FOREIGN KEY (`drugid`) REFERENCES `drugs` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

You are trying to create tables out of order.

For example you are trying to create contiansdrug table which refers to table drugs before drugs table.

Remember that any SQL, even DDL, tries to leave database in consistent state.

I would recommend putting the commands in proper order. Alternatively you have options to turn off the checks temporarily and run the creation scrip inside transaction, see the instructions here

Relevant section is

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your script..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

EDIT:

OK, try not to have same names for constraints. Reading the fine manual enlightens:

If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

EDIT2:

To spell it out, you have duplicate constrain symbol article, rename it and all will be fine.

Standard practice is if you name your constrains to use names that describe what is related, for example containsdrug_acticelid_article_id (firsttablename_column_secondtablename_column) would be unique and descriptive.

Answer 2

I solved the problem by not declaring the Foreign Key inside of MySql but simply declaring them as ints.