MySQL “SQLSTATE[23000]: 完整性约束违反”具有有效约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9934387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"SQLSTATE[23000]: Integrity constraint violation" with valid constraint
提问by Miguel Ribeiro
I'm using Symfony 2 with Doctrine.
我正在使用 Symfony 2 和 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.
我有 4 个班级：国家、地区、县和地方。District 有一个 Country 外键；县有外区；Local有一个外键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:
我转储了 SQL 来创建表和约束并得到这个：
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.
问题不在于 DataFixture 本身，因为我尝试使用 PhpMyAdmin 插入一个县并得到相同的错误。
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.
所有表都是在 InnoDB 引擎中创建的，我可以成功创建一个国家和一个地区。该错误仅发生在县实体中。
Thanks
谢谢
采纳答案by Miguel Ribeiro
Unistalled MAMP Pro and installed apache, mysql and php one by one. Same project worked under this new environment
卸载MAMP Pro，一一安装apache、mysql和php。同一个项目在这个新环境下工作
回答by Mike Purcell
Your fixture file is probably attempting to insert a row for Countyfor which there is no corresponding row matching the accountId or the districtId.
您的夹具文件可能正在尝试插入County没有与 accountId 或 DistrictId 匹配的对应行的行。
ALTER TABLE County ADD CONSTRAINT FK_5F4EFA13438082DC FOREIGN KEY (insertedBy) REFERENCES Account(id);
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 Countytable has a matching id in the accounttable.
此键强制您确保您输入到County表中的 accountid 在表中具有匹配的 id account。
ALTER TABLE County ADD CONSTRAINT FK_5F4EFA1362627EDC FOREIGN KEY (idDistrict) REFERENCES District(id);
ALTER TABLE County ADD CONSTRAINT FK_5F4EFA1362627EDC FOREIGN KEY (idDistrict) REFERENCES District(id);
Same as accountId, except no matching id in the districttable.
与 accountId 相同，但district表中没有匹配的 id 。
So double check your fixtures file and ensure that that district and account rows are inserted (and committed) before you insert a county row.
因此，请仔细检查您的装置文件，并确保在插入县行之前插入（并提交）该地区和帐户行。
回答by Artur Go?dyn
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)
实际上 MySQL 服务器 5.6.33-79.0 Percona Server (GPL), Release 79.0, Revision 2084bdb in Linux (x86_64) 中存在一个错误
