How to connect a primary key by primary key of the same table?

Question

I want to construct this database. Here in category table we give ID primary key of the category table to foreign key of the category table. I understood this and I done this part also but see in product table we want to give ID primary key of the product table to product table as a primary key so how to do that? This is my question? We can't able to use the same syntax in the foreign key. I want to done this in SQL server. So kindly anyone help me to do this part.

Answer 1

The database looks ok apart from one thing:

One table, (product) has a 1:1-relation to itself. It makes no sense in any way.

1:1 relations are used between different tables and are great at times (customizations on 3:rd part systems etc)

What you probably want is a 1:n-relation to make one product act as a collection of other products, (also available as separate products). For this to work you must add a foregin-key field (nullable) in the product-table.

But that solution would probably be too limiting for the purpose above, since one product could only belong to one product. So what you want is a n:n relation. So what you need to do is create a new table as in:

product_part
============
product_id
part_product_id

This is called a junction table and both fields are used to make up the primary key.

However, junction tables may grow over time to regular tables as fields are added, so I would advice you to use a separate PK field (makes API-requests easier etc) as in:

product_part
============
id
product_id
part_product_id
position
qty

Hope this helps.