CODEWITHSUNDEEP
mysqldatabase-design
Carmageddon
Carmageddon

Reputation: 2849

How to force category to products relationship in mysql tables

I am designing a database, where I want to accomplish this sitaution: I have a Category table, and Products table. I want to make it impossible to add a product without an existing category assigned to it (I believe I did that with the FK on the Products table). I want to force DELETE on a Category, to DELETE all the children (products) assigned to that category. I DONT want a category to be deleted, if/when ALL the products belonging to it are deleted.

Currently, what I've come up with is this: http://codepad.org/eSGqtyOs

I am unsure, one guy says the FK on the Categories table, will prevent me from adding categories without a product (catch 22..), and if I add On delete CASCADE in the product table, it will automatically delete a Category, once the last product in that category is deleted...

Please help to get this straight and right.

Also, please do not try to convince me to keep old categories/products not "sold" anymore - I have my reasons, I know the requirements I have for this system.

Upvotes: 1

Views: 332

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52157

Judging by your requirements, you don't need the FK in categories (referencing products). In fact, the way you made these FKs circular, you won't be able to insert any data due to the chicken-and-egg problem.

You only need FK in products (referencing categories).

I want to make it impossible to add a product without an existing category assigned to it (I believe I did that with the FK on the Products table).

That's right.

I want to force DELETE on a Category, to DELETE all the children (products) assigned to that category.

ON DELETE CASCADE should take care of that.

I DONT want a category to be deleted, if/when ALL the products belonging to it are deleted.

A FK allows a child-less parent.

So in your case, the FK on products referencing categories will allow a category to exist without having any products.

Upvotes: 3

Related Questions