Olá Galera, Esse é meu primeiro post aqui no forum. Estou precisando de uma ajuda... Criei esse banco abaixo e não consigo criar as foreing keys para as pks rg e id_div na tabela cad_fun_ramal. Vejam a estrutura:
DROP TABLE IF EXISTS `srcbr`.`cad_fun_ramal`;
CREATE TABLE `srcbr`.`cad_fun_ramal` (
`rg` varchar(100) NOT NULL,
`nome` varchar(100) default NULL,
`e-mail` varchar(100) default NULL,
`login` varchar(100) default NULL,
`centro` varchar(100) NOT NULL,
`id_div` int(10) unsigned NOT NULL,
`atualizavel` tinyint(1) unsigned NOT NULL default '1',
Eu preciso criar duas fks na tabela cad_ramal referenciando cad_fun_ramal. Porém o seguinte erro ocorre:
MYSQL ERROR NUMBER 1452.CANNOT ADD OR UPDATE A CHILD ROW A FOREING KEY CONSTRANT FAILED.. eee mais umas coisinhas..
Eu procurei verificar se existia algum registro de rg ou id_div q existisse em cad_ramal porém não existisse em cad_fun_ramal.... Nada feito... está tudo certo...vejam as consultas que fiz:
SELECT * FROM `cad_ramal` WHERE `id_rg` NOT IN (SELECT `rg` FROM `cad_fun_ramal`)
SELECT * FROM `cad_fun_ramal` WHERE `id_div` NOT IN (SELECT `id_div` FROM `cad_fun_ramal`)
Amabas não tiveram nenhum resultado.
Tentei de tudo já na net... más nada... vejam o log do innodb
===================================== 090309 8:56:25 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 56 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 426, signal count 426 Mutex spin waits 0, rounds 8580, OS waits 23 RW-shared spins 895, OS waits 403; RW-excl spins 0, OS waits 0 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 090309 8:56:22 Transaction: TRANSACTION 0 4025860, ACTIVE 0 sec, OS thread id 5244 inserting, thread declared inside InnoDB 151 mysql tables in use 2, locked 2 26 lock struct(s), heap size 2496, undo log entries 927 MySQL thread id 166, query id 71358 localhost 127.0.0.1 root copy to tmp table ALTER TABLE `srcbr`.`cad_ramal` ADD CONSTRAINT `FK_fun` FOREIGN KEY `FK_fun` (`id_rg`, `id_div`) REFERENCES `cad_fun_ramal` (`rg`, `id_div`) ON DELETE RESTRICT ON UPDATE RESTRICT Foreign key constraint fails for table `srcbr/#sql-558_a6`: , CONSTRAINT `FK_fun` FOREIGN KEY (`id_rg`, `id_div`) REFERENCES `cad_fun_ramal` (`rg`, `id_div`) Trying to add in child table, in index `FK_fun` tuple: DATA TUPLE: 3 fields; 0: len 5; hex 4343383136; asc CC816;; 1: len 4; hex 00000002; asc ;; 2: len 4; hex 35383032; asc 5802;; But in parent table `srcbr/cad_fun_ramal`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 9; compact format; info bits 0 0: len 5; hex 4343383136; asc CC816;; 1: len 4; hex 00000004; asc ;; 2: len 6; hex 0000003cae5c; asc <®\;; 3: len 7; hex 80000001820123; asc ‚ #;; 4: len 1; hex 2d; asc -;; 5: len 30; hex 76696e69636975732e66616c6569726f407a662d6c656e6b73797374656d; asc vinicius.faleiro@zf-lenksystem;...(truncated); 6: len 0; hex ; asc ;; 7: len 7; hex 39303930383136; asc 9090816;; 8: len 1; hex 01; asc ;; ------------ TRANSACTIONS ------------ Trx id counter 0 4025864 Purge done for trx's n:o < 0 4025858 undo n:o < 0 0 History list length 13 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 4764 MySQL thread id 173, query id 71364 localhost 127.0.0.1 root show engine innodb status ---TRANSACTION 0 4025860, not started, OS thread id 5244 MySQL thread id 166, query id 71359 localhost 127.0.0.1 root ---TRANSACTION 0 4025819, not started, OS thread id 5000 MySQL thread id 163, query id 70920 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025818, not started, OS thread id 2752 MySQL thread id 162, query id 70913 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025817, not started, OS thread id 4996 MySQL thread id 161, query id 70906 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025814, not started, OS thread id 4980 MySQL thread id 158, query id 70883 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025813, not started, OS thread id 4976 MySQL thread id 157, query id 70876 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025809, not started, OS thread id 3136 MySQL thread id 153, query id 70846 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025808, not started, OS thread id 4952 MySQL thread id 152, query id 70839 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025807, not started, OS thread id 3176 MySQL thread id 151, query id 70832 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025804, not started, OS thread id 4868 MySQL thread id 148, query id 70809 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025803, not started, OS thread id 4864 MySQL thread id 147, query id 70802 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025802, not started, OS thread id 5012 MySQL thread id 146, query id 70795 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025798, not started, OS thread id 6020 MySQL thread id 142, query id 70762 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025791, not started, OS thread id 4964 MySQL thread id 135, query id 70711 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025774, not started, OS thread id 4804 MySQL thread id 134, query id 36756 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023934, not started, OS thread id 2836 MySQL thread id 124, query id 921 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023922, not started, OS thread id 3640 MySQL thread id 112, query id 832 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023921, not started, OS thread id 2456 MySQL thread id 111, query id 825 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023916, not started, OS thread id 1664 MySQL thread id 106, query id 788 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023906, not started, OS thread id 3248 MySQL thread id 97, query id 720 localhost 127.0.0.1 timeasybill -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 774 OS file reads, 235 OS file writes, 85 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.05 writes/s, 0.05 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 69257, used cells 2757, node heap has 4 buffer(s) 38.32 hash searches/s, 28.95 non-hash searches/s --- LOG --- Log sequence number 0 185259240 Log flushed up to 0 185259240 Last checkpoint at 0 185105333 0 pending log writes, 0 pending chkp writes 54 log i/o's done, 0.05 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 31744110; in additional pool allocated 1520128 Buffer pool size 1024 Free buffers 238 Database pages 782 Modified db pages 24 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 764, created 18, written 169 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 180, state: sleeping Number of rows inserted 5546, updated 0, deleted 0, read 12052021 16.54 inserts/s, 0.00 updates/s, 0.00 deletes/s, 16.55 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
Pergunta
Vinicius F.
Olá Galera, Esse é meu primeiro post aqui no forum. Estou precisando de uma ajuda... Criei esse banco abaixo e não consigo criar as foreing keys para as pks rg e id_div na tabela cad_fun_ramal. Vejam a estrutura:
DROP TABLE IF EXISTS `srcbr`.`cad_fun_ramal`;
CREATE TABLE `srcbr`.`cad_fun_ramal` (
`rg` varchar(100) NOT NULL,
`nome` varchar(100) default NULL,
`e-mail` varchar(100) default NULL,
`login` varchar(100) default NULL,
`centro` varchar(100) NOT NULL,
`id_div` int(10) unsigned NOT NULL,
`atualizavel` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`rg`,`id_div`),
KEY `FK_cc` (`centro`,`id_div`),
CONSTRAINT `FK_cc` FOREIGN KEY (`centro`, `id_div`) REFERENCES `cad_custo` (`cod`, `id_div`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `srcbr`.`cad_ramal`;
CREATE TABLE `srcbr`.`cad_ramal` (
`ramal` varchar(45) character set latin1 NOT NULL,
`id_rg` varchar(100) character set latin1 default NULL,
`id_tipo` int(10) unsigned NOT NULL,
`id_div` int(10) unsigned NOT NULL,
`atualizavel` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`ramal`),
KEY `FK_tpl` (`id_tipo`),
CONSTRAINT `FK_tpl` FOREIGN KEY (`id_tipo`) REFERENCES `tpligacao` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin7;
Eu preciso criar duas fks na tabela cad_ramal referenciando cad_fun_ramal. Porém o seguinte erro ocorre:
MYSQL ERROR NUMBER 1452.CANNOT ADD OR UPDATE A CHILD ROW A FOREING KEY CONSTRANT FAILED.. eee mais umas coisinhas..
Eu procurei verificar se existia algum registro de rg ou id_div q existisse em cad_ramal porém não existisse em cad_fun_ramal.... Nada feito... está tudo certo...vejam as consultas que fiz:
SELECT * FROM `cad_ramal` WHERE `id_rg` NOT IN (SELECT `rg` FROM `cad_fun_ramal`)
SELECT * FROM `cad_fun_ramal` WHERE `id_div` NOT IN (SELECT `id_div` FROM `cad_fun_ramal`)
Amabas não tiveram nenhum resultado.
Tentei de tudo já na net... más nada... vejam o log do innodb
===================================== 090309 8:56:25 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 56 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 426, signal count 426 Mutex spin waits 0, rounds 8580, OS waits 23 RW-shared spins 895, OS waits 403; RW-excl spins 0, OS waits 0 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 090309 8:56:22 Transaction: TRANSACTION 0 4025860, ACTIVE 0 sec, OS thread id 5244 inserting, thread declared inside InnoDB 151 mysql tables in use 2, locked 2 26 lock struct(s), heap size 2496, undo log entries 927 MySQL thread id 166, query id 71358 localhost 127.0.0.1 root copy to tmp table ALTER TABLE `srcbr`.`cad_ramal` ADD CONSTRAINT `FK_fun` FOREIGN KEY `FK_fun` (`id_rg`, `id_div`) REFERENCES `cad_fun_ramal` (`rg`, `id_div`) ON DELETE RESTRICT ON UPDATE RESTRICT Foreign key constraint fails for table `srcbr/#sql-558_a6`: , CONSTRAINT `FK_fun` FOREIGN KEY (`id_rg`, `id_div`) REFERENCES `cad_fun_ramal` (`rg`, `id_div`) Trying to add in child table, in index `FK_fun` tuple: DATA TUPLE: 3 fields; 0: len 5; hex 4343383136; asc CC816;; 1: len 4; hex 00000002; asc ;; 2: len 4; hex 35383032; asc 5802;; But in parent table `srcbr/cad_fun_ramal`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 9; compact format; info bits 0 0: len 5; hex 4343383136; asc CC816;; 1: len 4; hex 00000004; asc ;; 2: len 6; hex 0000003cae5c; asc <®\;; 3: len 7; hex 80000001820123; asc ‚ #;; 4: len 1; hex 2d; asc -;; 5: len 30; hex 76696e69636975732e66616c6569726f407a662d6c656e6b73797374656d; asc vinicius.faleiro@zf-lenksystem;...(truncated); 6: len 0; hex ; asc ;; 7: len 7; hex 39303930383136; asc 9090816;; 8: len 1; hex 01; asc ;; ------------ TRANSACTIONS ------------ Trx id counter 0 4025864 Purge done for trx's n:o < 0 4025858 undo n:o < 0 0 History list length 13 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 4764 MySQL thread id 173, query id 71364 localhost 127.0.0.1 root show engine innodb status ---TRANSACTION 0 4025860, not started, OS thread id 5244 MySQL thread id 166, query id 71359 localhost 127.0.0.1 root ---TRANSACTION 0 4025819, not started, OS thread id 5000 MySQL thread id 163, query id 70920 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025818, not started, OS thread id 2752 MySQL thread id 162, query id 70913 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025817, not started, OS thread id 4996 MySQL thread id 161, query id 70906 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025814, not started, OS thread id 4980 MySQL thread id 158, query id 70883 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025813, not started, OS thread id 4976 MySQL thread id 157, query id 70876 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025809, not started, OS thread id 3136 MySQL thread id 153, query id 70846 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025808, not started, OS thread id 4952 MySQL thread id 152, query id 70839 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025807, not started, OS thread id 3176 MySQL thread id 151, query id 70832 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025804, not started, OS thread id 4868 MySQL thread id 148, query id 70809 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025803, not started, OS thread id 4864 MySQL thread id 147, query id 70802 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025802, not started, OS thread id 5012 MySQL thread id 146, query id 70795 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025798, not started, OS thread id 6020 MySQL thread id 142, query id 70762 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025791, not started, OS thread id 4964 MySQL thread id 135, query id 70711 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4025774, not started, OS thread id 4804 MySQL thread id 134, query id 36756 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023934, not started, OS thread id 2836 MySQL thread id 124, query id 921 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023922, not started, OS thread id 3640 MySQL thread id 112, query id 832 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023921, not started, OS thread id 2456 MySQL thread id 111, query id 825 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023916, not started, OS thread id 1664 MySQL thread id 106, query id 788 localhost 127.0.0.1 timeasybill ---TRANSACTION 0 4023906, not started, OS thread id 3248 MySQL thread id 97, query id 720 localhost 127.0.0.1 timeasybill -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 774 OS file reads, 235 OS file writes, 85 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.05 writes/s, 0.05 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 69257, used cells 2757, node heap has 4 buffer(s) 38.32 hash searches/s, 28.95 non-hash searches/s --- LOG --- Log sequence number 0 185259240 Log flushed up to 0 185259240 Last checkpoint at 0 185105333 0 pending log writes, 0 pending chkp writes 54 log i/o's done, 0.05 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 31744110; in additional pool allocated 1520128 Buffer pool size 1024 Free buffers 238 Database pages 782 Modified db pages 24 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 764, created 18, written 169 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 180, state: sleeping Number of rows inserted 5546, updated 0, deleted 0, read 12052021 16.54 inserts/s, 0.00 updates/s, 0.00 deletes/s, 16.55 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
Link para o comentário
Compartilhar em outros sites
1 resposta a esta questão
Posts Recomendados