Two same foreign keys in the one table . is it possible
-
Hi Is it possible to have 2 foreign keys that ae thesame in one table ?
Create table Product
(
productID int PK
)
Create table Category
(
CategoryID int PK ,
productID1 int fk references Product (productID) ,
productID2 int fk references Product (productID)
) -
Hi Is it possible to have 2 foreign keys that ae thesame in one table ?
Create table Product
(
productID int PK
)
Create table Category
(
CategoryID int PK ,
productID1 int fk references Product (productID) ,
productID2 int fk references Product (productID)
)Yes, although your table name is misleading and it looks like a design nasty to me. Do you ALWAYS have EXACTLY 2 products in the Category (name seems wrong) table.
Never underestimate the power of human stupidity RAH
-
Hi Is it possible to have 2 foreign keys that ae thesame in one table ?
Create table Product
(
productID int PK
)
Create table Category
(
CategoryID int PK ,
productID1 int fk references Product (productID) ,
productID2 int fk references Product (productID)
)Yeah, what he said. Your design may be more maintainable if you use a separate table to hold the relationships. Here's a recent thread concerning a similar situation: http://www.codeproject.com/Forums/1649/Csharp.aspx?fid=1649&tid=4132317[^]
-
Hi Is it possible to have 2 foreign keys that ae thesame in one table ?
Create table Product
(
productID int PK
)
Create table Category
(
CategoryID int PK ,
productID1 int fk references Product (productID) ,
productID2 int fk references Product (productID)
)What you are asking is possible; however, be sure that your design is right. Off the top of my head, I thing a Category can have 1 or several products. If that is the case, then I would have my tables as follows Product(ProductID, CategoryID, ProductDesc,...) Category(CategoryID, CategoryDesc,......) Category ID would be a foregn in the product table
-
What you are asking is possible; however, be sure that your design is right. Off the top of my head, I thing a Category can have 1 or several products. If that is the case, then I would have my tables as follows Product(ProductID, CategoryID, ProductDesc,...) Category(CategoryID, CategoryDesc,......) Category ID would be a foregn in the product table
-
Hi Is it possible to have 2 foreign keys that ae thesame in one table ?
Create table Product
(
productID int PK
)
Create table Category
(
CategoryID int PK ,
productID1 int fk references Product (productID) ,
productID2 int fk references Product (productID)
)As has already been answered above, yes, it is possible. But your design is flawed. It accomodates only 2 products in a category. What if a category has more than two products? In a real world situation, a category can have hundreds of products and a product can be classified under multiple categories. You should create what is called a bridge table to address this design question.
Create table Product
(
ProductID int PK
)
Create table Category
(
CategoryID int PK
)
Create table CategoryProductBridge
(
CategoryID int FK references Category (CategoryID),
ProductID int FK references Product (ProductID)
) -
What if the product can belong to more than one category? (as is true in a real world situation).
Based on the question, it would be impossible to tell.As you can see my answer was conditional. Of course if there were many products for category and vice-versa, then a many-to-many relationship would exist which would be decomposed to two one-to-many relationships. The idea was to get the author of the question to think about their design carefully. I was not saying the solution must necessarily be what wrote. :((
-
As has already been answered above, yes, it is possible. But your design is flawed. It accomodates only 2 products in a category. What if a category has more than two products? In a real world situation, a category can have hundreds of products and a product can be classified under multiple categories. You should create what is called a bridge table to address this design question.
Create table Product
(
ProductID int PK
)
Create table Category
(
CategoryID int PK
)
Create table CategoryProductBridge
(
CategoryID int FK references Category (CategoryID),
ProductID int FK references Product (ProductID)
)