I have the following tables
tbl_cat -- id(pk), category (PRODUCT CATEGORIES)
tbl_subcat - id, catid, subcat (catid is a foregin key referencing id in tbl_cat ) - SUB CATEGORIES
I want to prepopulate my tables with categories and sub-categories of products. it's quite straightforward to insert categories in tbl_cat but each entry in tbl_subcat must reference a category. For example if i have a category called Acessories, then all items in the subcategory like wristbands, necklace and rings should all have thesame catid that references Acessories in the tbl_cat table.
How can i implement this. Thanks

4 Years
Discussion Span
Last Post by dhani09

Do you want to know how to add the actual foreign key constraint? or how to manage the data insertion?


You will need to populate the tbl_cat first. (I am going to assume your id fields for both tables are auto_increment)

INSERT INTO tbl_cat (category) VALUES ('Accessories');

The easiest way to do the child table insertions is to manually check the id generated in the previous table.

SELECT * FROM tbl_cat;
| id | category    |
| 1  | Accessories |

You can then use this id in the foreign key field for your child table insert like so...

INSERT INTO tbl_subcat (catid,subcat) VALUES (1,'some subcat');
INSERT INTO tbl_subcat (catid,subcat) VALUES (1,'another subcat');

Okay thanks but i'm recieving an error that says "Duplicate entry 1 for key catid"


Okay got it. Chnanged the index from "Unique" to "index" and it works. Thanks

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.