2

I'm using Symfony 2 with Doctrine.

I have 4 classes: Country, District, County and Local. District has a foreign key of Country; County has a foreign of District; Local has a foreign key of District.

The problem is that when inserting a County (using data fixtures), I get the error

SQLSTATE[23000]: Integrity constraint violation:

I dumped the SQL to create the tables and constraints and got this:

CREATE TABLE Country (id INT AUTO_INCREMENT NOT NULL, 
name VARCHAR(255) NOT NULL, 
insertedAt DATETIME NOT NULL, 
flag LONGTEXT DEFAULT NULL COMMENT '(DC2Type:object)', 
PRIMARY KEY(id)) ENGINE = InnoDB;


CREATE TABLE County (id INT AUTO_INCREMENT NOT NULL, 
name VARCHAR(255) NOT NULL, 
insertedAt DATETIME NOT NULL, 
insertedBy INT NOT NULL, 
idDistrict INT NOT NULL, 
INDEX IDX_5F4EFA13438082DC (insertedBy), 
INDEX IDX_5F4EFA1362627EDC (idDistrict), 
PRIMARY KEY(id)) ENGINE = InnoDB;


CREATE TABLE District (id INT AUTO_INCREMENT NOT NULL, 
name VARCHAR(255) NOT NULL, 
insertedAt DATETIME NOT NULL, 
insertedBy INT NOT NULL, 
idCountry INT NOT NULL, 
INDEX IDX_C8B736D1438082DC (insertedBy), 
INDEX IDX_C8B736D143CAA294 (idCountry), 
PRIMARY KEY(id)) ENGINE = InnoDB;


CREATE TABLE LOCAL (id INT AUTO_INCREMENT NOT NULL, 
name VARCHAR(255) NOT NULL, 
insertedAt DATETIME NOT NULL, 
insertedBy INT NOT NULL, 
idCounty INT NOT NULL, 
INDEX IDX_4A17A7EC438082DC (insertedBy), 
INDEX IDX_4A17A7EC3BF357BF (idCounty), 
PRIMARY KEY(id)) ENGINE = InnoDB;


ALTER TABLE County ADD CONSTRAINT FK_5F4EFA13438082DC
FOREIGN KEY (insertedBy) REFERENCES Account(id);


ALTER TABLE County ADD CONSTRAINT FK_5F4EFA1362627EDC
FOREIGN KEY (idDistrict) REFERENCES District(id);


ALTER TABLE District ADD CONSTRAINT FK_C8B736D1438082DC
FOREIGN KEY (insertedBy) REFERENCES Account(id);


ALTER TABLE District ADD CONSTRAINT FK_C8B736D143CAA294
FOREIGN KEY (idCountry) REFERENCES Country(id);


ALTER TABLE LOCAL ADD CONSTRAINT FK_4A17A7EC438082DC
FOREIGN KEY (insertedBy) REFERENCES Account(id);


ALTER TABLE LOCAL ADD CONSTRAINT FK_4A17A7EC3BF357BF
FOREIGN KEY (idCounty) REFERENCES County(id);

The problem is not in the DataFixture itself because I tried to insert a County using PhpMyAdmin and got the same error.

All tables are created in InnoDB engine and I can successfully create a Country and a District. The error occurs only with the County entity.

Thanks

Miguel Ribeiro
  • 8,057
  • 20
  • 51
  • 74
  • This is why you should write you SQL by hand .. ehh. Anyway. Did you notice, that there are two definitions of `Country` table? Also it would be nice, if you would provide the data, that can replicate the error. – tereško Mar 29 '12 at 23:05
  • @tereško: No, one defintion is for country the other for county (no r). – Mike Purcell Mar 30 '12 at 00:16

3 Answers3

2

Your fixture file is probably attempting to insert a row for County for which there is no corresponding row matching the accountId or the districtId.

ALTER TABLE County ADD CONSTRAINT FK_5F4EFA13438082DC FOREIGN KEY (insertedBy) REFERENCES Account(id);

This key forces you to ensure that the accountid you enter into the County table has a matching id in the account table.

ALTER TABLE County ADD CONSTRAINT FK_5F4EFA1362627EDC FOREIGN KEY (idDistrict) REFERENCES District(id);

Same as accountId, except no matching id in the district table.

So double check your fixtures file and ensure that that district and account rows are inserted (and committed) before you insert a county row.

Mike Purcell
  • 19,847
  • 10
  • 52
  • 89
  • Actually no... Even writing the SQL Insert statement I got the error. I reverted back to MAMP (free) from MAMP Pro and all started working again! (????) – Miguel Ribeiro Mar 30 '12 at 01:17
  • Lol, maybe the free version runs a `SET foreign_key_checks = 0;` – Mike Purcell Mar 30 '12 at 01:19
  • I don't think you really fixed it. @Mike's answer about the need to ensure district is entered before county is probably correct. This will come back and bit you again if you don't resolve it. – Cerad Mar 30 '12 at 14:40
  • @Cerad Well... Made some stress tests and everything is up and running with no problems... I wonder what does free MAMP has that Pro doesn't... – Miguel Ribeiro Mar 30 '12 at 19:07
0

Actually there is a bug in MySQL server 5.6.33-79.0 Percona Server (GPL), Release 79.0, Revision 2084bdb in Linux (x86_64)

Artur Gołdyn
  • 1
  • Can you provide a bit more information about this bug? – Gabriel Heming Mar 24 '17 at 18:11
  • Sure I can. The problem happens if you create a Foreign Key, but you don't have autoincrement on the target table. Somehow this is doable, but I'm not able to tell exactly how, this happens in unittests. Deleting the Foreing Key helped. – Artur Gołdyn Mar 30 '17 at 09:29
-7

Unistalled MAMP Pro and installed apache, mysql and php one by one. Same project worked under this new environment

Miguel Ribeiro
  • 8,057
  • 20
  • 51
  • 74
  • 23
    I wouldn't deem that a fix, or the right answer to your problem. – Lee Davis Sep 05 '12 at 10:37
  • I agree. Just because something works doesn't mean it's correct. You probably hid the problem & like others have mentioned it will bite you in the butt later on down the road and you will be wondering what is going on. Better to solve the problem now and not later when this bug may cause corruption or other unwanted effects. Next time, you may not even be looking in this spot for the bug and spend too much time trying to hunt it down when you could have fixed it right here and now. – PerryCS Dec 16 '20 at 06:46