0

my foreign key columns takes values which are not present in the parent table. Why so?

Engine for both the tables are Innodb.

code used for foreign key creation. ALTER TABLE test ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (UserId) REFERENCES tbluser(UserID) ON DELETE CASCADE ON UPDATE CASCADE;

insert test values (2222222);

2222222 isn't there in the tbluser table.

user1795928
  • 19
  • 4
  • You may have disabled [foreign key checks](https://stackoverflow.com/q/15501673/6248528). Try `select @@FOREIGN_KEY_CHECKS`. – Solarflare Mar 10 '18 at 18:49
  • @Solarflare No, it's not. it's enabled. – user1795928 Mar 10 '18 at 19:26
  • Is the foreign key column indexed? – Honeyboy Wilson Mar 10 '18 at 19:37
  • There are two reasons why a foreign key does not work: you either do not have one, or they do not work on principle (are disabled or you do not use innodb). Since you now excluded the second reason, verify that you actually added them correctly (check `show create table test`). – Solarflare Mar 10 '18 at 19:43
  • @Solarflare CREATE TABLE `test` ( `UserId` int(11) NOT NULL, KEY `FK_PersonOrder` (`UserId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 – user1795928 Mar 11 '18 at 06:57
  • @HoneyboyWilson Yes it has an BTREE index with name FK_PersonOrder – user1795928 Mar 11 '18 at 06:58
  • Your table `test` does not contain a foreign key (it contains a normal index with the name `FK_PersonOrder`), so your `alter table` probably failed. Execute it again. If it does not give an errormessage, you now have a foreign key. If it gives an error, do `show engine innodb status`. There will be a section "last foreign key error", which will tell you (and us, if you need help with that) what the problem is. Most times: `tbluser.UserID` is not of type `int`, or `tbluser` (!) does not have an index on `UserID` (although it sounds like it should be the primary key, but check it) – Solarflare Mar 11 '18 at 10:37

0 Answers0