Error 145: Cannot add or update a child row within inserting data into database .. mySQL -
i have created database in mysql , script add data database. when want add data pnumbers table, have problem.
cannot add or update child row: foreign key constraint fails (`blahari1/pnumbers`, constraint `pnumbers_ibfk_1` foreign key (`customerid`) references `customers` (`id`)) any ideas how solve issue?
implementation of database:
use blahari1; drop table if exists calls; drop table if exists pnumbers; drop table if exists customers; drop table if exists operators; drop table if exists tarifs; create table customers ( id integer unsigned not null auto_increment primary key, name varchar(30) unique ) engine=innodb default charset=utf8 collate=utf8_czech_ci; create table operators ( id integer unsigned not null auto_increment primary key, name varchar(30) unique ) engine=innodb default charset=utf8 collate=utf8_czech_ci; create table tarifs ( id integer unsigned not null auto_increment primary key, name varchar(30) unique ) engine=innodb default charset=utf8 collate=utf8_czech_ci; create table pnumbers ( id integer unsigned not null auto_increment primary key, pnumber varchar(9) not null unique, minutes integer unsigned not null, customerid integer unsigned not null, operatorid integer unsigned not null, tarifid integer unsigned not null, foreign key (customerid) references customers (id), foreign key (operatorid) references operators (id), foreign key (tarifid) references tarifs (id) ) engine=innodb default charset=utf8 collate=utf8_czech_ci; create table calls ( id integer unsigned not null auto_increment primary key, length integer unsigned not null, fk_pnfrom integer unsigned not null, fk_pnto integer unsigned not null, foreign key (fk_pnfrom) references pnumbers (id), foreign key (fk_pnto) references pnumbers (id) ) engine=innodb default charset=utf8 collate=utf8_czech_ci; the script should add data database:
#!/bin/bash # blahari1 login="mysql --user=blahari1 --password=animate --database=blahari1" #------------- insertion() { if [ "$1" == "numbers" ]; while read line; name="`echo $line | sed 's/,.*//'`"; numberv="`echo $line | awk '{print $2}' | sed 's/,.*//'`"; operatorv="`echo $line | awk '{print $3}' | sed 's/,.*//'`"; tarifv="`echo $line | awk '{print $4}' | sed 's/,.*//'`"; minutesv="`echo $line | awk '{print $5}'`"; if [ ! "`echo "select name '' customers name='$name';" | $login`" ]; echo "insert customers (name) values ('$name');" | $login fi if [ ! "`echo "select name '' operators name='$operatorv';" | $login`" ]; echo "insert operators (name) values ('$operatorv');" | $login fi if [ ! "`echo "select name '' tarifs name='$tarifv';" | $login`" ]; echo "insert tarifs (name) values ('$tarifv');" | $login fi idc="`echo "select id customers name='$name';" | $login`"; ido="`echo "select id operators name='$operatorv';" | $login`"; idt="`echo "select id tarifs name='$tarifv';" | $login`"; echo "insert pnumbers (pnumber, operatorid, tarifid, minutes, customerid) values ('$numberv', '$ido', '$idt', '$minutesv', '$idc');" | $login; done < customers.txt elif [ "$1" == "calls" ]; while read line; fromv="`echo $line | sed 's/,.*//'`"; fromv="`echo "select id pnumbers pnumber='$fromv';" | $login`"; tov="`echo $line | awk '{print $2}' | sed 's/,.*//'`"; tov="`echo "select id pnumbers pnumber='$tov';" | $login`"; lengthv="`echo $line | awk '{print $3}'`"; echo "insert calls (fk_pnfrom, fk_pnto, length) values ('$fromv', '$tov', '$lengthv');" | $login; done < calls.txt fi echo "select * customers;" | $login echo "select * operators;" | $login echo "select * pnumbers;" | $login echo "select * tarifs;" | $login echo "select * calls;" | $login } queries(){ if [ "$1" = "calls" ];then id="`echo "select numbersfrom.number pnfrom, numbersto.number pnto calls, pnumbers numbersfrom,pnumbers numbersto, operators operators.name = '$1' , operators.id = numbersfrom.operatorid , calls.fk_pnfrom = numbersfrom.id , calls.fk_pnto = numbersto.id;" | $login`" echo "$id" elif [ "$1" = "credit" ];then kredit="`echo "select (select sum(pnumbers.minutes) pnumbers, customers customers.name = '$1' , customers.id = pnumbers.customerid) - sum(calls.length) customers,pnumbers,calls customers.name = '$1' , customers.id = pnumbers.customerid , pnumbers.id = calls.fk_pnfrom;" | $login`" echo "$kredit" elif [ "$1" = "maxcalls" ];then maxcalls="`echo "select customers.name customers, pnumbers, calls customers.id = pnumbers.customerid , pnumbers.id = calls.fk_pnfrom group customers.id order sum(calls.length) desc limit 1;" | $login`" echo "$maxcalls" elif [ "$1" = "longest" ];then maxlength="`echo "select customers.name customers,pnumbers numbersfrom,pnumbers numbersto, calls, operators operators.name = '$1' , operators.id = numbersfrom.operatorid , customers.id = numbersto.customerid , numbersto.id = calls.fk_pnto , numbersfrom.id = calls.fk_pnfrom order calls.length desc limit 1;" | $login`" echo "$maxlength" #response=`echo "select person.name phone_number, calls, person person.id = phone_number.personid , phone_number.id=calls.receipienid , select calls.receipienid calls.length=max(calls.length);" | $login` fi } if [ "$1" = "--variant" ]; echo "5" elif [ "$1" = "--insert" ];then insertion "$2" "$3" elif [ "$1" = "--query" ];then queries "$2" "$3" elif [ "$1" = "--debug" ];then echo "debug" fi thank help. thank you
you need use --skip-column-names option on mysql command. otherwise, output select statement like:
id 123 and you'll try insert two-line string pnumbers table. when tries convert integer, convert 0, doesn't exist in customer table. use:
login="mysql --user=blahari1 --password=animate --database=blahari1 --skip-column-names"
Comments
Post a Comment