Khabibb Mubarakk Khabibb Mubarakk - 3 years ago 157
MySQL Question

why i Cannot add foreign key constraint in MySQL Workbench

i have customer table with nid_c,nama_customer, and more field ..

second table I have kendaraan with nopol,nid_c,nama_customer, and more field ..

I try make relation between this table..

I want update data nid_c and nama_customer on kendaraan table when I update customer table.
I got error message here.

Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
-- -----------------------------------------------------
-- Table `BengkelBiru`.`kendaraan`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `BengkelBiru`.`kendaraan` (
`NOPOL` VARCHAR(12) NOT NULL,
`NID_C` VARCHAR(7) NULL DEFAULT NULL,
`NAMA_CUSTOMER` VARCHAR(25) NULL DEFAULT NULL,
`MERK` VARCHAR(15) NULL DEFAULT NULL,
`TYPE` VARCHAR(25) NULL DEFAULT NULL,
`CC` VARCHAR(4) NULL DEFAULT NULL,
`TAHUN` VARCHAR(4) NULL DEFAULT NULL,
`WARNA` VARCHAR(10) NULL DEFAULT NULL,
`STATUS` VARCHAR(7) NULL DEFAULT NULL,
PRIMARY KEY (`NOPOL`),
INDEX `pkk_idx` (`NAMA_CUSTOMER` ASC, `NID_C` ASC),
CONSTRAINT `FK_NID_C`
FOREIGN KEY (`NAMA_CUSTOMER` , `NID_C`)
REFERENCES `BengkelBiru`.`customer` (`NID_C` , `NID_C`)
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8

SQL script execution finished: statements: 14 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

Answer Source

Your problem is on one or both of these lines:

FOREIGN KEY (`NAMA_CUSTOMER` , `NID_C`)
REFERENCES `BengkelBiru`.`customer` (`NID_C` , `NID_C`)
                                     ^^^^^^^ Looks wrong s/b NAMA_CUSTOMER

I think you want this line:

REFERENCES `BengkelBiru`.`customer` (`NID_C` , `NID_C`)

to be

REFERENCES `BengkelBiru`.`customer` (`NAMA_CUSTOMER`, `NID_C`)

Why are you referring to NID_C twice in the reference? I say this because you define the foreign key as:

FOREIGN KEY (`NAMA_CUSTOMER` , `NID_C`)

and your descriptions at the top shows customer having NID_C and NAMA_CUSTOMER as columns.

However, fundamentally, why do you have Nama_customer in the kendaraan (vehicle) table at all? This doesn't seem to be 3rd normal form. You've repeated the customer name in a second table; which isn't part of the Customer's PK. Now, this may be acceptable if you want to keep the name of the customer at the time the entry is made into kendaraan; but since you're making it part of the FK... and doing cascade update/delete... it's very odd.

So maybe you just want:

FOREIGN KEY (`NID_C`)
REFERENCES `BengkelBiru`.`customer` (`NID_C`)

Assuming the Primary Key of Customer is NID_C

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download