Need help setting up Foreign Key relationship in MySQL DB

Question

So I have to InnoDB tables (Products) and (Categories). I wanted to setup a relationship between the "Category" field on the Products table, and the "CategoryId" of the Categories table.

But when attempting to create a FK it will only let me select the Primary Key "ProductsId" on the Products table and map to the PK "CategoryId" on the Categories table.

Maybe I am missing the way/reason to setup a Foreign key. My thoughts and tell me if I am wrong:

1) Was to require a product be added to a category when added, the category must exist or you must create it first. You can't remove a category unless you perform some task (programmically or on the DB backend) to remove the products from a category you are wanting to remove.

2) I was expecting the CategoryId value to be stored in the "Category" field of the Products table. Then when displaying in my view, would need to look up the Categories.Name field by the CategoriesId value.

EDIT: So I understand that two fields involved in the Foreign keys must be the same, size, types...etc. However, how does linking up the ProductId and CategoryId work in context to what I mentioned above I am wanting to do. When I did create a FK between ProductId and CategoryId, I won't let me add a product record.

Also, the Category Name field and the Product Category field are the same type, size..etc, yet I don't get the option to select those in the foreign key tab?

How should I be setting it up so that the categories table will know what products are part of each category.

You have the right idea. But before we can help you, how are you trying to set up the FK? Via a SQL statement, or with some administration tool, such as phpMyAdmin? — Dan J
– Dan J, Commented Nov 23, 2010 at 17:18
I am using MySQL Workbench: -Selecting "Alter table" and choosing the "Products" table. From there under Foreign Key - setting up the Foreign key. — pghtech
– pghtech, Commented Nov 23, 2010 at 17:24
Under REferenced Table, I am selecting the Categories table. In the right section, it list all the Products column, but it won't let me check mark the "Category" field. I can only select the "ProductId" field which then gives the "CategoryId" field under "Referenced column" column. — pghtech
– pghtech, Commented Nov 23, 2010 at 17:27

pghtech · Accepted Answer · 2010-11-23 18:12:10Z

Ok, unfortunately, I must answer my own questions. The reason for most of my technical problems is because the field you are trying to make "must be indexed".

The comprehesive issue I was having was that I needed to get rid of the actual "Category" varchar field on the Products table, and create a CategoryId field that would have only a value that exists in the Category table CategoryId field.

Now I will just have to reference the Categories.Name field through the Products.CategoryId value.

At least this is what I have come to understand.

Joel A. Villarreal Bertoldi · Accepted Answer · 2010-11-23 17:39:34Z

If you're on the Workbench, verify that the types, lengths and attributes of both columns involved in the FK are the same.

Collectives™ on Stack Overflow

Need help setting up Foreign Key relationship in MySQL DB

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related