MySQL InnoDB table accepts Non-Existent Foreign Key Using EasyPHP 14.1 VC11 and phpMyAdmin (4.1.4) to create a website project in my home computer. The MySQL version is 5.6.15 according to the EasyPHP administration page.

Working on Windows 7 64-bit.



I've created the tables I needed and specified each foreign key in the "Relation view" (on Structure tab). All the tables are InnoDB and the default engine is set to InnoDB.



Assuming I have the tables A and B, and B has a foreign key to the column ID of the table A.

When inserting a line in table B, if I write 0 (zero) on the foreign key column and submit, it accepts.

In the Browse tab it shows the line with the foreign key column with the value 0 (zero).

When I click on the value (link to the that line on the table A), I go to that table with zero results.



In the variables tab, the "foreign key checks" variable is "ON".

But the foreign keys are not checked or the insert would fail since there is no ID 0 on table A. What have I done wrong or forget?




  Please run SHOW CREATE TABLE yourtable\G for both table A and table
  B and post it in the question. –  RolandoMySQLDBA


Table A

CREATE TABLE `competicaogeral` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


Table B

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Please check that the FOREIGN KEY is in the table design of your table B.

EXAMPLE

CREATE TABLE projects (
    StaffID INT NOT NULL,
    ProjectName VARCHAR(20) NOT NULL,
    Allocation INT NOT NULL,
    PRIMARY KEY (StaffID, ProjectName) ,
    FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
);


Facts


Table A is the table Staff
Table B is the table projects
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE requires StaffID in projects to reference a row in Staff with the same StaffID


Your Original Question

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


You need a foreign key reference

CREATE TABLE `competicao` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `Nome` varchar(190) NOT NULL,
 `CompeticaoGeral_ID` int(10) unsigned NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nome` (`Nome`),
 FOREIGN KEY (CompeticaoGeral_ID) REFERENCES competicaogeral(ID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


The SQL to do this would be

ALTER TABLE competicao ADD FOREIGN KEY (CompeticaoGeral_ID)
REFERENCES competicaogeral(ID) ON DELETE CASCADE;


GIVE IT A TRY !!!