SQL Server Performance

Foreign keys/many to many tables

Discussion in 'SQL Server 2005 General DBA Questions' started by johnslg, Aug 1, 2007.

  1. johnslg New Member

    I have three tables that I want to link for a many to many relationship:
    sales:
    Type, int, PK
    Cust, int, PK
    Order, int, PK
    ....
    Order:
    Order, int, PK, identity
    Rep, int, PK
    Rep
    Rep, int, PK, identity
    ....
    I can link Rep and Order via the Rep field with no problem, but when I try to establish an FK relationship between Sales.Order and Order.Order I get an error message. If I add a Sales.Rep field I can create an FK using Order AND Rep but that defeats my purpose. There are no rows in any table. Is there a way to link the two tables with composite PK's?
    Thanks.
  2. Adriaan New Member

    Not sure that you're sure about what is what.
    A many-to-many relationship would be for instance when you have a Product table that lists your products, an Order table that lists your orders, and an OrderProduct table that for each Order lists the products on it.
    Not sure how that translates to your Sales, Order and Rep tables.
  3. johnslg New Member

    I really just need to know if there is a way to establish an FK relationship between individual columns that are both part of composit keys in their respective tables.
  4. Adriaan New Member

    You mention just 'an error message', but I would assume it is telling you more in detail why SQL cannot create the FK constraint.
    The two most common reasons are probably:
    (1) The set of columns that you're referring to on the parent table must have a unique index/constraint. This is a sine-qua-non for FK relationships.
    (2) The new FK may not introduce a circular reference, where the two tables already share FKs with a third table. A workaround for this is to implement the FK functionality in triggers.
  5. johnslg New Member

    "The columns in the table 'Order' do not match an existing primary key or UNIQUE constraint."
    Both tables have compoist primary keys.
  6. Adriaan New Member

    Let's say you have a parent table Parent with primary key on (Col1, Col2).
    You also have a child table Child with a column (Col2) and you want to have an FK between Parent and Child on (Col2).
    Check the first rule: the (set of) column(s) on the parent table that your FK is referring to must have a unique index/constraint.
    So does table Parent have a unique index/constraint on Col2? No, it doesn't: the unique constraint is the primary key, which covers both Col1 and Col2. This means there is no guarantee that Col2 is unique in and by itself.
  7. johnslg New Member

    D'OH! PK doesn't enforce uniqueness on individual cols only the combination of all cols in the composite key.
    Back to the drawing board.......
    Thanks!

Share This Page