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

Recommended Answers

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

Jump to Post

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 …

Jump to Post

All 5 Replies

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.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.