Primary Key vs Foreign Key, Explained on an ER Diagram in Visio
A primary key makes each row unique. A foreign key points to a row in another table. Learn the difference, and how foreign keys create the relationships in a crow's foot ER diagram, drawn in Visio.
Step by step
- Primary vs foreign key. A primary key and a foreign key are 2 kinds of ID column. Here they are on a small ER diagram in Visio, with customers, orders and shipments.
- Crow's Foot stencil. The shapes come from the Crow's Foot Database Notation stencil in the Shapes pane.
- Primary key. Add a Customer entity with 3 attributes: CustomerID, Name and Email. The first row, CustomerID, is the primary key. No 2 customers can share the same CustomerID. The key icon marks that row. A primary key must be unique and never empty, and it should not change. That is why Email is a poor choice: people change their email address.
- Foreign key. Next, add an Order entity with OrderID, OrderDate and CustomerID. OrderID is its own primary key, and the last row is CustomerID again. That CustomerID is a foreign key. It holds a copy of a customer's primary key and shows which customer placed each order.
- The relationship. The foreign key is what links the 2 tables. Draw a Relationship connector from Customer to Order. It reads: 1 customer places zero or many orders.
- The many side. In a one-to-many relationship, the foreign key sits on the many side. Here that is the Order end, where the crow's foot is.
- Practice. Practice: 1 order can be shipped in several parcels. Add a Shipment entity with ShipmentID, ShippedDate and OrderID. Where does the foreign key go? It goes on the many side, in Shipment, as OrderID. Connect Order to Shipment with another Relationship connector: 1 order has zero or many shipments.
- Done. Click an empty part of the page to deselect the shapes and see the finished model: Customer, Order and Shipment, joined by 2 relationships.
- The rule. A primary key identifies each row in its own table. A foreign key points to a row in another table, and in a one-to-many relationship it sits on the many side.
Common mistakes
- Using an email or a name as the primary key. People change their email address, and 2 people can share a name. Use a separate ID, like CustomerID, that is unique and never changes.
- Putting the foreign key on the one side. If Customer held an OrderID column, each customer could only have 1 order. Put CustomerID in Order instead, at the crow's foot end.
- A relationship line with no foreign key column. The line only shows that 2 tables are linked. Add the matching column, like CustomerID in Order, so the model shows how the tables actually join.
- Forcing a many-to-many link into 2 tables. An order has many products, and a product appears on many orders, so neither table can hold the foreign key. Add a linking table, like OrderLine, with both OrderID and ProductID.
When a business analyst uses this
In a data requirements workshop, a business analyst sketches Customer, Order and Shipment in Visio before sign-off. Marking each primary key and foreign key shows the developers how the tables join, and it makes questions like "can an order exist without a customer?" easy to ask and answer.
Questions people ask
What is the difference between a primary key and a foreign key?
A primary key identifies each row in its own table, like OrderID in Order. A foreign key is a column that holds another table's primary key, like CustomerID in Order, and it links the 2 tables.
Can a table have more than one foreign key?
Yes. A table has only 1 primary key, but it can have several foreign keys, one for each link to another table. It can even have 2 foreign keys that point to the same table, like BillingAddressID and ShippingAddressID in Order.
Can a primary key also be a foreign key?
Yes. In a linking table like OrderLine, OrderID and ProductID are both foreign keys, and together they can form the primary key. A one-to-one table can also use the other table's ID as its own primary key.
