Combined Primary Key - Why?

In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:

> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql