0

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

2
Contributors
5
Replies
16
Views
4 Years
Discussion Span
Last Post by dhani09
0

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

0

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');
0

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

0

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.