integrity constraint violation – yii\db\integrityexception sqlstate[23000]: integrity constraint violation: 1452 cannot add or update child row: foreign key constraint fails (`jts`.`employee`, constraint `employee_ibfk_1` foreign key (`client_code`) references `client` (`client_code`)) sql being executed was: insert `employee` (`client_code`, `company_name`, `emp_email`, `emp_mobile`, `emp_first_name`, `emp_last_name`) values ('12345678', 'pvppcoe', 'saurabhkulkarni2010@hotmail.com', '+449029792183', 'saurabh', 'kulkarni') error info: array ( [0] => 23000 [1] => 1452 [2] => cannot add or update child row: foreign key constraint fails (`jts`.`employee`, constraint `employee_ibfk_1` foreign key (`client_code`) references `client` (`client_code`)) ) i have 3 tables client, employee , create_client, out of client , employee has 2 foreign keys. problem showing when try insert data create_client employee has exact same field. should can insert 2 tables @ 1 i.e create_client , employee
update-
table structure
1)client
create table if not exists `client` ( `id` int(11) not null, `client_code` varchar(11) not null, `company_name` varchar(45) not null ) engine=innodb auto_increment=29 default charset=utf8; 2)create_client
create table if not exists `create_client` ( `id` int(11) not null, `client_code` varchar(11) not null, `company_name` varchar(45) not null, `emp_email` varchar(45) not null, `emp_mobile` varchar(45) not null, `emp_first_name` varchar(45) not null, `emp_last_name` varchar(45) not null ) engine=innodb auto_increment=43 default charset=utf8; 3)employee
create table if not exists `employee` ( `id` int(11) not null, `client_code` varchar(11) not null, `company_name` varchar(45) not null, `emp_email` varchar(45) not null, `emp_mobile` varchar(45) not null, `emp_first_name` varchar(45) not null, `emp_last_name` varchar(45) not null ) engine=innodb auto_increment=20 default charset=utf8; this table structure, first user create client means first update create_client. if user wants can add many employee under 1 client code user update employee table yii2 dynamic form widget. using widget have created 1 table call client store client_code , company name remaining data go in employee table e.g emp_mobile,emp_email, emp_first_name, emp_last_name.
now problem pop when user first enters data create_client table.
everything working between client , employee table user able enter many employee wants using yii2 dynamic form widget not working create_client
apparently have foreign key in employee table referencing client_code of client table, can use client_code values exist in client table.
don't know structure of create_client table looks , purpose is, based on info assume should first insert data in client, in employee , in create_client, every foreign key's value exists in respective table.
if not correct, please post table structures , data or queries causing this.
edit after comment: assume table structure looks this:
create table `create_client` ( `client_code` int unsigned not null, `company_name` varchar(255) not null, `emp_mobile` varchar(255) not null, `emp_email` varchar(255) not null, `emp_first_name` varchar(255) not null, `emp_last_name` varchar(255) not null, unique key `client_code` (`client_code`) ) engine=innodb default charset=utf8; create table `client` ( `client_code` int unsigned not null auto_increment, `company_name` varchar(255) not null, primary key `client_code` (`client_code`), constraint `client_ibfk_1` foreign key (`client_code`) references `create_client` (`client_code`) ) engine=innodb default charset=utf8; create table `employee` ( `client_code` int unsigned not null, `company_name` varchar(255) not null, `emp_mobile` varchar(255) not null, `emp_email` varchar(255) not null, `emp_first_name` varchar(255) not null, `emp_last_name` varchar(255) not null, key `client_code` (`client_code`), constraint `employee_ibfk_1` foreign key (`client_code`) references `client` (`client_code`) ) engine=innodb default charset=utf8; however, suggest use client parent table , move foreign key constraint create_client, both employee , create_client have foreign key of client. normalise , rid of company_name in child tables:
create table `client` ( `client_code` int unsigned not null auto_increment, `company_name` varchar(255) not null, primary key `client_code` (`client_code`) ) engine=innodb default charset=utf8; create table `create_client` ( `client_code` int unsigned not null, `emp_mobile` varchar(255) not null, `emp_email` varchar(255) not null, `emp_first_name` varchar(255) not null, `emp_last_name` varchar(255) not null, unique key `client_code` (`client_code`), constraint `create_client_ibfk_1` foreign key (`client_code`) references `client` (`client_code`) ) engine=innodb default charset=utf8; create table `employee` ( `client_code` int unsigned not null, `emp_mobile` varchar(255) not null, `emp_email` varchar(255) not null, `emp_first_name` varchar(255) not null, `emp_last_name` varchar(255) not null, key `client_code` (`client_code`), constraint `employee_ibfk_1` foreign key (`client_code`) references `client` (`client_code`) ) engine=innodb default charset=utf8; the next improvement use employee table both unique , non-unique client_code inserts. instead of using 2 identical tables, apart unique key, unique validation in yii instead of in mysql. can specify table name in tablename() method of createclient model . remain:
create table `client` ( `client_code` int unsigned not null auto_increment, `company_name` varchar(255) not null, primary key `client_code` (`client_code`) ) engine=innodb default charset=utf8; create table `employee` ( `client_code` int unsigned not null, `emp_mobile` varchar(255) not null, `emp_email` varchar(255) not null, `emp_first_name` varchar(255) not null, `emp_last_name` varchar(255) not null, key `client_code` (`client_code`), constraint `employee_ibfk_1` foreign key (`client_code`) references `client` (`client_code`) ) engine=innodb default charset=utf8; this should allow use dynamic form, client main model , employee items model.
Comments
Post a Comment