none
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
    Moderator

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
    Moderator
  • 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
    Moderator
  • 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
    Moderator
  • 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