why i Cannot add foreign key constraint in MySQL Workbench

Question

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

The Composite foreign key isn't a problem from what I can see. However, it is odd that you want NAMA_CUSOTMER and NID_C to both reference NID_C? in customer. — xQbert, Sep 28 '17 at 16:31

xQbert · Accepted Answer · 2017-09-28T16:51:21.687

1

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

edited Sep 28 '17 at 16:51

answered Sep 28 '17 at 16:34

xQbert

34,733
2
41
62

i want keep the nama customer in kendaraan table.. so how to update nama customer in kendaraan table when i update data on customer table? – Khabibb Mubarakk Sep 28 '17 at 16:48
then your references line should be REFERENCES `BengkelBiru`.`customer` (`NAMA_CUSTOMER`, `NID_C`) (notice I'm not referencing NID_C twice as you have) – xQbert Sep 28 '17 at 16:50
i think need break like @riyangason say – Khabibb Mubarakk Sep 28 '17 at 16:52
I disagree you can have composite keys' as a FK: Example: https://stackoverflow.com/questions/9780163/composite-key-as-foreign-key-sql However, I still dont' know WHY customer has to be in kendaraan. You're making the database 2NF instead of 3NF by having customer in kendaraan. The name is just a join away! as a rule of thumb 3NF is the lowest you should have a Relational DB in a production environment. – xQbert Sep 28 '17 at 16:53
so what is u suggestion i must delete nama_customer in table kendaraan? – Khabibb Mubarakk Sep 28 '17 at 16:59
You have two options as I see it: 1) Make the adjustments 1st suggested which allows you to keep the column in kendaraan (but I don't know why you need to its duplicated data violating 3NF db design (thus I can't in good faith really recommend it )) 2) remove the column and the FK association making this a 3NF+ relationship. Since the NID_C ties back to a specific customer, you can always join the tables customer and kendaraan to get the name from customer. the "More errors" may be due to additional problems and we'd need to know what those errors are. – xQbert Sep 28 '17 at 17:05
You could setup the schema using http://rextester.com with sample data in customer and kendaraan that way we can see the errors you have and verify we have a working structure before providing an answer. – xQbert Sep 28 '17 at 17:06
i think i still need nid_c for identity who have the kendaraan (vehicle) with nopol (plate number).. how about this ? – Khabibb Mubarakk Sep 28 '17 at 17:10
Yes. I never suggested removing nid_C just the NAMA_CUSTOMER (See last Foreign Key & references in answer) The first answer I gave addressed the error you were having. The 2nd answer reviewed the question as a whole and I perceived a design flaw on even having customer_name in the kendaraan table. This ASSUMES NID_C is the Primary Key of Customer. Generally FKs are on the related tables PK. (or at times unique indexes) – xQbert Sep 28 '17 at 17:42
THANKS .. btw you have CRUD code (vb.net with MySQL using parameter )? – Khabibb Mubarakk Sep 28 '17 at 18:27
I'm not sure what you mean. is this a different question or are you asking what I do as a matter of design/coding practice? – xQbert Sep 28 '17 at 21:11

score 0 · Answer 2 · answered Sep 28 '17 at 16:28

0

I dont think you can declare both at the same time. Try doing them separately.

        CONSTRAINT `FK_NAMA_CUSTOMER`
        FOREIGN KEY (`NAMA_CUSTOMER`)
        REFERENCES `BengkelBiru`.`customer` (`NID_C`)


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

answered Sep 28 '17 at 16:28

Ryan Gadsdon

2,272
4
31
56

sorry I'm new about my sql .. and this code generate from MySQL workbench (forward engineer) ..must using different name for CONSTRAIT name? – Khabibb Mubarakk Sep 28 '17 at 16:35
Doesn't it seem odd to have two from one table reference the same key of a different table? I can see this if you have a From_Person and To_person; but in this case it appears to be the ID of the customer and the name. I can't imagine both columns are even of the same data type! – xQbert Sep 28 '17 at 16:46
i think so .. i need suggestion . – Khabibb Mubarakk Sep 28 '17 at 17:03

score 0 · Answer 3 · answered Sep 28 '17 at 16:32

There could be any possible scenario :-

1.Columns in the parent tables Can be INT UNSIGNED?

2.Data type in both tables should be same.

3.You are trying to reference a nonexistent key on the target table. Make sure that it is a key on the other table (it can be a primary or unique key).

Foregin Key Constaints

why i Cannot add foreign key constraint in MySQL Workbench

3 Answers3