MySQL InnoDB table accepts Non-Existent Foreign Key

Question

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

Answer 1

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 !!!