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

On 2011-07-23 05:18, zaqi syah wrote:
[...]
You can only reference unique or primary key constraints in the foreign
key clause. If kecamatan is unique you can add a constraint like:

ALTER TABLE dagang.kecamatan ADD CONSTRAINT ... UNIQUE ( kec_no );

and then add your foreign key

/Lennart

[...]

My (kec_no) is not unique & how to handle this?

zaqi syah wrote:
1. You could have the foreign key constraint the other way around, if the
column kec_no is unique in the other table.

2. Not having aq foreign key at all (joining works anyway).


--

//Aho

On 2011-07-23 12:33, zaqi syah wrote:
As usual, it depends. What business rules are you trying to implement?


/Lennart

ok problem solved, thanks

oh sorry my problem not solved,

I create 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_

zaqi syah <slashjak (AT) gmail (DOT) com> wrote:

That means there is conflicting data in your tables, more specifically:
in your child table. ALTER TABLE works like so:

1. create a new table according to the current structure and the
given alterations. In your case: with the FK added.

2. copy data from the original table to the new table

3. swap the original and new table (rename them)

4. drop the old copy

MySQL fails in step 2 above. When rows are copied to the new table,
MySQL checks the FK contraint and there is at least one row that
violates the constraint.


Disclaimer: the above describes the "slow" alter table method. Certain
operations (like setting a new default value for a column) can use a
fast version of that method where the table is directly modified. Per
rule of thumb: if the alteration modifies the binary row image, then
the slow method will be used. Else the fast method *might* be used.


XL

On 2011-07-25 07:36, zaqi syah wrote:
Can you post the alter table statement, works fine for me given your
table defs above:

alter table desa add constraint abc
foreign key (id_kecamatan) references kecamatan (id_kecamatan);
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

BTW, your design of the tables look a bit odd, but perhaps there is a
good reason for it?


/Lennart

[...]

I found the problem that caused # 1452 - Can not add or update a child row, the problem is.
I found id_kecamatan contained in the table desa, but not present in table kecamatan & I need to remove id_kecamatan that are not on the table kecamatan.
I try this SQL query like this to remove :

delete
from desa
where id_kecamatan = (
SELECT id_kecamatan
FROM desa
WHERE id_kecamatan <>
ALL (
SELECT id_kecamatan
FROM kecamatan
));

and try this SQL query :

delete
from desa
where id_kecamatan IN (
SELECT id_kecamatan
FROM desa
WHERE id_kecamatan NOT IN
(SELECT id_kecamatan
FROM kecamatan));

Still not working. Please help me, thanks b4