0

I'm pretty much brand new to mysql. Something which I'm find hard to understand so hopefully someone can enlighten me. I've got a parent table with "product_id" as the primary key okay, after creating another table called "supplier_info" I used the product_id as a foreign key in this table and made it constraint, all well so far. I then created a new table called "cost_info" and wanted to use product_id as a foreign key for this table aswell, works fine. But if I want to make it constraint in this table aswell it gives me 1022 error cant duplicate keys? I'm kind of lost as to how that error is related and why it will only be allowed to be constraint once?

  • What exactly is the constraint that is failing? a unique constraint on product_ID in a cost_info table? Does the table also contain supplier and could suppliers offer the same product? you may need a Composite constraint consisting of multiple columns to generate a "UNIQUE" index. Show the code which results in the error and the table structure on which it is being applied. – xQbert Nov 24 '15 at 19:50
  • CREATE TABLE cost_id ( cost_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, product_id INT NOT NULL, CONSTRAINT list_product_id_fk FOREIGN KEY (product_id) REFERENCES list(product_id)); –  Nov 24 '15 at 20:04
  • I'm on my phone at the moment. So if I have at the moment one parent table called list and 2 tables that use the PK(product_id) as a foreign key but not as a primary key in the 2 other tables then they need to be composite? –  Nov 24 '15 at 20:07
  • ah... are you trying to use the same key name `list_product_id_f` in both the `Cost_ID` table and the `supplier_Info` table? It can't be! You can refer to product_ID as a foreign key in both, they just can't have the same name. So if the create table for Supplier_Info also has a constraint list_product_ID_FK, then that's what the error message is saying. you can't name constraints the same even for different tables. – xQbert Nov 24 '15 at 20:11
  • Possible duplicate of [Error 1022 - Can't write; duplicate key in table](http://stackoverflow.com/questions/18056786/error-1022-cant-write-duplicate-key-in-table) – xQbert Nov 24 '15 at 20:34

1 Answers1

0

You need to make product_id to Primary Key.

**

Because if you want to reference a column as primary key from a foreign key, that column must be unique.

**

Burak Karasoy
  • 1,682
  • 2
  • 21
  • 33