Answered by:
Foreign key to composite key

Question
-
Well...I have to create a sale detail table consisted of quantity and product and has to have reference to the product table and the order table as well. so Create table [sales detail] ( OrderNo int, ProductId char (5), add constraint pk_sales_detail primary key(OrderNo, ProductId), Quantity int not null, ProductName varchar(50) not null ) alter table [sales detail] add constraint fk_OrderNo_sales_detail foreign key (OrderNo) references Orders (OrderNo) ///This works fine alter table [sales detail] add constraint fk_ProductId_sale_detail foreign key (ProductId) references Products (ProductId) ///This gives me errors about no ///indicate primary key or something Also note that: the ProductId in the Products Table is part of the composite key. Not sure if that is the cause of the problem. create table orders ( OrderNo Int, constraint pk_OrderNo primary key (OrderNo), OrderDate Date not null, CustomerNo char(4) not null, SalesRep char(3) not null ) create table products ( ManufacturerId char(3), Check(ManufacturerId like '[A-Z][A-Z][A-Z]'), ProductId char(5), Check(productId like '[A-Z][A-Z][0-9][0-9][0-9]'), constraint pk_ManufactureId primary key (ManufacturerID, ProductID), Description varchar(50) not null, Price money not null, QtyOnHand int not null ) Here is what I would like to do: Add a “Sales Detail” table to your database. This table is related to the Orders and Products tables. It shows the product and quantity ordered at least
- Edited by jinzuya Tuesday, November 16, 2010 5:07 AM
Monday, November 15, 2010 9:51 PM
Answers
-
You have a couple of options here. One is to add the ManufacturerId to the "Sales Detail" table. Another is to use a surrogate key as the products table primary key. I'll leave the remainder of the exercise for you to complete (appropriate constraints and why).
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/- Marked as answer by KJian_ Monday, November 22, 2010 7:36 AM
Monday, November 15, 2010 11:54 PM
All replies
-
You have a couple of options here. One is to add the ManufacturerId to the "Sales Detail" table. Another is to use a surrogate key as the products table primary key. I'll leave the remainder of the exercise for you to complete (appropriate constraints and why).
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/- Marked as answer by KJian_ Monday, November 22, 2010 7:36 AM
Monday, November 15, 2010 11:54 PM -
so adding the ManufacturerId means that the manufactureId is just a link or connection to the product table right?Tuesday, November 16, 2010 5:14 AM
-
Adding ManufacturerId to Order Details allows the composite key of ManufactureId and ProductID to be used as the foreign key referencing the products table.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/Tuesday, November 16, 2010 1:05 PM -
That's what I don't really understand. If I create a composite key with ManufactureId and ProductId as a primary key then what about the OrderNo? I think it is the OrderNo and the ProductId that makes up the composite key. This is what most of the detail tables do.
Otherwise I have to make the OrderNo a foreign key referencing the order table?
Tuesday, November 16, 2010 2:38 PM -
I meant to say to add ManufacturerId to Sales Details. Then add foreign key (ManufactureId and ProductId) referencing the product table and a foreign key OrderNo referencing the order table.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/Wednesday, November 17, 2010 2:49 AM -
Yes...This is what I did and the tables are linked and worked fine.Thursday, November 18, 2010 12:32 AM
-
Also the question is the primary key. Would (ManufactureId, ProductId and OrderNo) be a primary key or only ProductId and OrderNo a primary key? Because both ways work so I am just wondering which one is more appropriate?
- Proposed as answer by afrodita_ust Thursday, November 17, 2011 2:46 PM
Friday, December 3, 2010 2:09 AM -
Hi. I have a little bit the same problem. I'm building a database for a school system and I need to make the link between some tables. First I have a Student table with a StudentID, then Course table with Course ID, and a Semester table with SemesterID. I made the relationship between Course table and Semester table and there is a composite key between them. (SemesterID, CourseID). Now I want to create a new table that will keep record of all students according to the courses in which they are registered for a particular semester. I created a new table that has a composite key(SemesterID, StudentID, CourseID). But when I enter data into it, it allows to register a new student in courses that are not offere for a particular semester.
Please can you help me, how can I build this relationship correctly.
Thanks.
Thursday, November 17, 2011 2:54 PM