Composite Primary Key on mysql workbench

Question

I am generating composite primary key on cart table product_Id and User_Id. Those are primary keys on the product table and user table accordingly.

So

• Do I need to do a reference to these primary keys (foreign keys so the only existing ids are the one existing in both tables)?
• Can I leave it as two primary keys ?
• How do I add them in MySqlWorkbench?

Answer 1

It depends on what you want to achieve with your database.

Most probably you want to add them as foreign keys to your n to n table. Easiest thing is to open the workbench in the modeling view and define a n to n relation between product and user.

This will make sure that you have data integrity on your tables.

You might want to have only one connection per user - product pair. You can achieve this by either creating a unique index no those two or making them primary key for that new table. I personally would not go for a composed key as your model might change and you then are way more flexible.

Answer 2

From what I've tried in the past, the best thing to do is to make product_id and user_id as foreign keys and reference them to their respective primary keys in tables product and user. This way you can be sure that the data is not being filled wrong.