0

I have a problem with adding constraint foreign key to my table.

Below is a code I setup in my base:

CREATE TABLE IF NOT EXISTS `basename`.`Category_News` (
  `Id_Category` INT NOT NULL AUTO_INCREMENT,
  `Id_Category_Parent` INT NULL,
  `Name` VARCHAR(50) NOT NULL,
  `Desc` VARCHAR(255) NOT NULL,
  `Published` ENUM('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id_Category`),
  INDEX `fk_Category_Newsa_idx` (`Id_Category_Parent` ASC),
  CONSTRAINT `fk_Category_News`
    FOREIGN KEY (`Id_Category_Parent`)
    REFERENCES `basename`.`Category_News` (`Id_Category`)
  ) ENGINE = InnoDB;

I changed base engine from MyISAM to InnoDB but...

Can someone tell me, why when I insert new row for e.g. with atribute Id_Category_Parent = 8 there are no error even if category with this id not exists ?

Kuba
  • 1,415
  • 1
  • 18
  • 29
  • you get an error when trying to create the table with the FK or after the table is created, at insert? – Eduard Uta Jan 14 '15 at 11:37
  • NoI didn't have any problems with creating table. And inserting new rows too even if Id_Category_Parent doesn't exist in base :/ – Kuba Jan 14 '15 at 11:40
  • can you add an example of insert statement that fails? Or an example of insert statement that should fail due to the FK constraint? – Eduard Uta Jan 14 '15 at 11:43
  • 1
    [`foreign_key_checks`](http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_checks) must be disabled. (and please replace this hideous `ENUM('0','1')` with [`BOOLEAN`](http://dev.mysql.com/doc/refman/5.6/en/numeric-type-overview.html#idm139990711466176) ;) – RandomSeed Jan 14 '15 at 11:46
  • Ok, two first inserts on empty table: INSERT INTO `basename`.`komentarze_newsa` (`Id_Category`, `Id_Category_Parent`, 'Name', 'Desc', 'Published') VALUES (1, NULL, 'ex', 'ex', '0'); INSERT INTO `basename`.`komentarze_newsa` (`Id_Category`, `Id_Category_Parent`, 'Name', 'Desc', 'Published') VALUES (2, 10, 'ex', 'ex', '0'); And I think that the second insert should give an error because category with Id = 10 not exist, is not inserted. – Kuba Jan 14 '15 at 11:49
  • @Kuba, now it's clear: the insert statements are valid and second should trigger an error. You may want to check previous comment from RandomSeed. – Eduard Uta Jan 14 '15 at 11:57
  • @RandomSeed, please add an answer below with your comment that helped so that Kuba can close this item(?) – Eduard Uta Jan 14 '15 at 12:47
  • possible duplicate of [Foreign key not working in MySQL: Why can I INSERT a value that's not in the foreign column?](http://stackoverflow.com/questions/380057/foreign-key-not-working-in-mysql-why-can-i-insert-a-value-thats-not-in-the-for) – RandomSeed Jan 14 '15 at 16:11

1 Answers1

0

Solution was really simple.

  1. Change engine to InnoDB for your tables

  2. Enable FOREIGN_KEY_CHECKS in your database:

    SET FOREIGN_KEY_CHECKS=1;

Kuba
  • 1,415
  • 1
  • 18
  • 29