1

Here's the part of my ERD:

enter image description here

OrderID from the Orders table relates to the OrderID/ProductID primary key in the Order Details table. I don't think primary keys can relate to other primary keys, but I'm not sure how else to relate the tables. Make them both primary and foreign keys?

philipxy
  • 14,867
  • 6
  • 39
  • 83
xmaniscx
  • 21
  • 1
  • 4
  • This look suspiciously like this question in which the questino was answered http://stackoverflow.com/questions/34319747/how-can-i-make-2-fields-in-one-primary-key-with-sql – paparazzo Dec 17 '15 at 00:29
  • you can relate any keys you want, whether they're primary or not. just because they're primary doesn't mean they can't also be a foreign key. all it means is that you're enforcing a 1:1 relationship, since you only ever have one record with that key in either table. – Marc B Dec 17 '15 at 13:51
  • In your last edit it looks like you tried to change to a new image link, but you didn't enter it properly, so all you did was remove the previous image. You don't have enough rep to put an image inline but if you put the link in we can do it for you. – philipxy Dec 21 '15 at 22:09

5 Answers5

1

You seem to be missing some basic ideas behind PKs (primary keys), UNIQUE NOT NULL, FKs (foreign keys), superkeys, CKs (candidate keys), simple vs composite PKs/UNIQUEs and ERDs (Entity-Relationship Diagrams). From this recent answer:

PKs & FKs are erroneously called "relationships" in some methods and products. Application relationships are represented by tables. (Base tables and query results.) PKs & FKs are constraints: they tell the DBMS that only certain situations can arise, so it can notice when you make certain errors. They are not relationships, they are statements true in & of every database state and application situation. You do not need to know constraints to update and query a database.

Just declare per what is true of your relationships/tables:

  • a PK or UNIQUE NOT NULL declaration says that every subrow value in a column set is unique. Ie that the column set is a superkey. (A PK is just a distinguished UNIQUE NOT NULL.)
  • a FK declaration says that a column list subrow value in referencing columns must also be in referenced superkey columns.

I don't think primary keys can relate to other primary keys,

They can: A primary key can be a FK referencing another superkey. (You seem to be using "relates to" to mean "is referenced by a FK in").

But note: Here you have two PKs Order OrderID & Product ProductID referenced as FKs in ("relating to") OrderLine. But they are each FKs referencing ("relating from"?) part of OrderLine composite PK {OrderID,ProductID}.

but I'm not sure how else to relate the tables.

First declare CKs (candidate keys): Superkeys that don't contain smaller superkeys. Then declare FKs. (Then for a SQL DBMS declare any undeclared superkeys referenced by FKs.)

Make them both primary and foreign keys?

Yes: They are PKs in Order & Product. They are FKs in OrderLine referencing Order & Product. And the PK of OrderLine happens to be {OrderID,ProductID}.

PS In your style of ERD the lines are (apparently) merely FKs, with all the entities and relationships having tables. In some forms of ERDs there are entity tables, labeled lines representing relationships/tables (each end involving a FK) and unlabeled lines representing just FKs. When you see a diagram style always be sure you understand how to determine what icons represent relationships/tables and just what those relationships are in terms of the application. (Not just their cardinalities.)

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
0

I don't think primary keys can relate to other primary keys, but I'm not sure how else to relate the tables.

This is perfectly fine, and it is quite commonly done.

What you are doing is referred to as a Compound Key. Compound keys don't have to link to other tables, but they can.

kemiller2002
  • 113,795
  • 27
  • 197
  • 251
  • Hey I was wondering if you could help me out. In SQL Server, I tried adding the relationship but I get this error: However, I get this error: https://c1.staticflickr.com/1/680/23808976805_dfa7af3d56_o.png Explorer View: https://c2.staticflickr.com/6/5750/23513358240_17f9afb83d_o.png – xmaniscx Dec 17 '15 at 14:05
0

The relationships indicated by your diagram are not relationships between two primary keys. It looks like the attribute OrderID in Order Details references the Orders table. The attribute ProductID in Order Details references the Products table. These are sometimes called identifying relationships because the referencing attributes happen to be part of a key.

Relationships between keys are perfectly valid but that isn't what is shown in your diagram.

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

What is shown on the diagram is a FK relationship and is common

Orders and Products have PK so OrderID and ProductID are unique

So now you have OrderDetails so an order can have multiple products
The FKs assure valid values for OrderID and ProductID

In addition you should add a composite PK of OrderID, ProductID on OrderDetails
This way you don't have duplicate ProductID for the same OrderID
You have Quantity for dealing with multiple

UnitPrice is a catch
If you want to have multiple unit price for a ProductID in an OrderID then you would not be able to declare OrderID, ProductID as a PK
I would avoid going down that path if you can

paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

Some of the answers conflate the concepts of relationships and foreign key constraints, as do many (most?) ER diagramming tools.

For clarification, there are 6 relationships (in the sense that Chen used the word) visible in the diagram:

  • Orders.OrderID, Orders.CustomerID
  • Orders.OrderID, Orders.ShipperID
  • Orders.OrderID, Orders.PaymentTypeID
  • OrderDetails.OrderID, OrderDetails.ProductID
  • Products.ProductID, Products.ProductTypeID
  • Products.ProductID, Products.LocationID

and 7 foreign key constraints (I assumed some table names):

  • OrderDetails.OrderID ⊆ Orders.OrderID
  • OrderDetails.ProductID ⊆ Products.ProductID
  • Orders.CustomerID ⊆ Customers.CustomerID
  • Orders.ShipperID ⊆ Shippers.ShipperID
  • Orders.PaymentTypeID ⊆ PaymentTypes.PaymentTypeID
  • Products.ProductTypeID ⊆ ProductTypes.ProductTypeID
  • Products.LocationID ⊆ Locations.LocationID

They aren't the same thing at all.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • This does not answer the stated question – paparazzo Dec 17 '15 at 13:37
  • Chen's "relationships" are Codd's "relationships" of arity >1 and are represented by relations/tables and drawn as diamonds. The lines in his ER diagrams are FKs. Later misrepresentations/misappropriations called ER use "relationship" for "FK". – philipxy May 17 '16 at 21:58