Linking table entries to a foreign key

[dhani09]

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

[|-|x]

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

[dhani09]

i want to know how to manage the insertion. thanks

[|-|x]

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

[dhani09]

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

[dhani09]

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