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

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

i want to know how to manage the insertion. thanks

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.