Discussion:
[ASK] Foreign Key error
(too old to reply)
zaqi syah
2011-07-23 03:18:08 UTC
Permalink
I try to create a database with the command below:

CREATE TABLE `dagang`.`kecamatan` (
`no_kecamatan` INT( 4 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`prop_id` INT( 2 ) NOT NULL ,
`kab_no` VARCHAR( 2 ) NOT NULL ,
`kec_no` VARCHAR( 3 ) NOT NULL ,
`nama_kecamatan` VARCHAR( 50 ) NOT NULL ,
INDEX ( `kec_no` )
) ENGINE = INNODB;

CREATE TABLE `dagang`.`desa` (
`no_desa` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`prop_id` INT( 2 ) NOT NULL ,
`kab_no` VARCHAR( 2 ) NOT NULL ,
`kec_no` VARCHAR( 3 ) NOT NULL ,
`desa_no` VARCHAR( 3 ) NOT NULL ,
`desa_id` VARCHAR( 10 ) NOT NULL ,
`nama_desa` VARCHAR( 50 ) NOT NULL ,
INDEX ( `kec_no`)
) ENGINE = INNODB;

Then I add foreign key :

ALTER TABLE `desa` ADD FOREIGN KEY ( `kec_no` ) REFERENCES `umkm`.`kecamatan` (`kec_no`)

Error message & fails to add foreign key :

#1452 - Cannot add or update a child row: a foreign key constraint fails (`umkm`.<result 2 when explaining filename '#sql-bf4_10f'>, CONSTRAINT `#sql-bf4_10f_ibfk_1` FOREIGN KEY (`kec_no`) REFERENCES `kecamatan` (`kec_no`))

What causes the above error?
I've tried to fill the data first and then add the index & foreign keys but still error

Please Help me, thanks b4
zaqi syah
2011-07-25 05:38:45 UTC
Permalink
I create new database first & I've fixed database structure, to be as below:

CREATE TABLE IF NOT EXISTS `kecamatan` (
`id_propinsi` varchar(2) DEFAULT NULL,
`id_kabupaten` varchar(4) DEFAULT NULL,
`id_kecamatan` varchar(7) NOT NULL,
`nama_kecamatan` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id_kecamatan`),
KEY `id_kabupaten` (`id_kabupaten`)
) ENGINE=InnoDB

CREATE TABLE IF NOT EXISTS `desa` (
`id_propinsi` varchar(2) DEFAULT NULL,
`id_kabupaten` varchar(4) DEFAULT NULL,
`id_kecamatan` varchar(7) DEFAULT NULL,
`id_desa` varchar(10) NOT NULL,
`nama_desa` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id_desa`),
KEY `id_kecamatan` (`id_kecamatan`)
) ENGINE=InnoDB


Next I try to add foreign key and error message :

#1452 - Cannot add or update a child row: a foreign key constraint fails (`umkm`.<result 2 when explaining filename '#sql-fbc_267'>, CONSTRAINT `#sql-fbc_267_ibfk_1` FOREIGN KEY (`id_kecamatan`) REFERENCES `kecamatan` (`id_kecamatan`))

I try again, fill data first after that I add index & primary key, when I try to create a foreign key error message appears

#1452 - Cannot add or update a child row: a foreign key constraint fails (`umkm`.<result 2 when explaining filename '#sql-fbc_267'>, CONSTRAINT `#sql-fbc_267_ibfk_1` FOREIGN KEY (`id_kecamatan`) REFERENCES `kecamatan` (`id_kecamatan`))

I also check the fields used as primary key & value no duplicates

I also uploaded the database could please help me check maybe there is something wrong & missing when checked, this download link:

http://v5.indowebster.com/download/files/umkm_

Continue reading on narkive:
Loading...