2

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 run SHOW CREATE TABLE yourtable\G for both table A and table B and post it in the question. – RolandoMySQLDBA Aug 6 '14 at 17:14
  • @RolandoMySQLDBA Answered. – Edu Aug 6 '14 at 17:33
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 !!!

  • It doesn't appear on Create Table like you said. But I've set it on "Relation view" in phpMyAdmin. I'll try creating the table using a sql query. How can I set the foreign key without a SQL query in phpMyAdmin? – Edu Aug 6 '14 at 17:37
  • It worked. SQL shows the 1452 error for the ID 0. Thank you for the answered. It seems that the Relation view is not what I'm thinking or is working wrong. – Edu Aug 6 '14 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.