Primary and Foreign Keys and Relationships

1. Keys
   
   
   
   • Primary and Foreign Keys
   
   
   
    


2. Relationships
   
   
   
   • One-to-Many and Many-to-Many Relationships
   
   
   
   

I would like to start by defining some basic conventions.
Primary Keys.

• These are indicated by 'PK' alongside the field in a Table.


• They show the field that identifies the record uniquely.


• For example, the 'product_id' for a Product and an 'order_id' for an Order.


• The values are usually generated by an 'Auto-Increment' field.


• They don't have any intrinsic meaning and are simply integers generated automatically one after another.Primary/Foreign Keys.


• These are indicated by 'PF' alongside the field in a Table.


• In this case, the field also exists as a Primary Key in another Table.


• In our example, the Primary Key in the 'Customer_Orders_Products' Table is a Composite Key, made up of two Fields.


• The 'product_id' is the Primary Key in the 'Products' Table, and the 'order_id' is the Primary Key in the 'Customer_Orders' Table.Foreign Keys.


• These are indicated by 'FK' alongside the field in a Table.


• They show a reference to the Primary Key for a record in another Table.


• For example, the 'customer_id' in the Customer_Orders Table is linked to a 'customer_id' in the Customer Table (not shown).One-to-Many Relationships.


• One-to-Many Relationships are very common in the real world, and can be read that 'One' Parent can have 'Many' children.


• In this diagram, One Product can be associated with Many Customer_Orders_Products.


• The line between Products and Customer_Orders_Products is a continuous line (not a dotted line).


• This means that the 'product_id' field is a Primary Key in the Customer_Orders_Products Table.


• A dotted line would indicate the existence of a Foreign Key (which is defined above).
  'Crows-Feet'


• At the Customer_Orders_Products end, there is what's called a 'Crows-Feet' symbol.


• This indicates the 'Many' aspect of the relationship.


• The little 'o' shows that this is an Optional relationship.


• In other words, not every Product has an associated record in the Customer_Orders_Products Table.


• This applies, of course, to Products that nobody orders.


• At the other end of the line there is a small horizontal line. This means that this is essential.


• In other words, every product_id in the Customer_Orders_Products Table must match a product_id in the products Table.


• This means that a Customer cannot order a Product that does not exist.


• In passing, let me say that this is different from the Product not being in stock.


• This aspect is called Inventory Control and is covered by a different Database Schema.


• The Relationship between Customer_Orders and Customer_Orders_Products shows a short horizontal line at each end.


• This indicates that every Order must contain at least one Product.


• It also indicates that Product in every Customer_Orders_Products must be associated with an Order in Customer_Orders.Many-to-Many Relationships.


• Many-to-Many Relationships are very important in the real world, and we see them all around us every day.


• In our example, many Customers can order the same Product.


• Many Products can be ordered by the same Customer.


• This is called a Many-to-Many Relationship. It cannot be implemented directly in a Table in a Relational Database.


• The solution is to create an intermediate table that records all combinations that exist of Customer and Product.


• For example, if a Customer ordered three Products, then there would be three records for that Customer in this intermediate table.


• The combination of the Primary keys in the parent Tables become the Primary key in this intermediate table.


• We also need to allow for Comments like 'Make it extra strong, please'.

• If we get the structure to the Database right, then it's straightforward to extend it.