Primary and foreign keys
A primary key names each row uniquely; a foreign key points to a row in another table.
In a relational database, a primary key is the column (or set of columns) that identifies each row uniquely. In a customers table it is usually an id: customer 42 is always that customer and no one else, even if two people share the same name. The database refuses a second row with the same primary key and does not allow it to be empty.
A foreign key is a column in one table that holds the primary key of a row in another table. An orders table has a customer_id column, and each value in it points to a row in customers. That is how a database links data without copying it: the order does not repeat the customer's name and address, it just says "customer 42", and a SQL JOIN brings the two together when you need them.
Foreign keys also protect consistency. If they are declared in the database schema, the database will not accept an order for customer 999 when there is no customer 999, and it can stop you from deleting a customer who still has orders. Many analytics systems skip these checks for speed, which is one reason data cleaning so often finds orphan rows.
Keep in mind
- Primary key: unique and never empty, one per row.
- Foreign key: a column that refers to a primary key in another table.
- Lets tables connect without duplicating data; joins follow these links.
- Declared keys stop orphan records, like an order for a customer who does not exist.
