Talk about primary keys and foreign keys. What is the difference between them, and in which scenarios would you pick each?

A primary key is a column or set of columns that uniquely identifies each row in a table. It must be unique, not null, and stable. A foreign key is a column that establishes a link between two tables by referencing the primary key of another table. The primary key defines the entity, while the foreign key defines the relationship. Use a primary key when you need a unique identifier for records, such as an employee_id for an Employees table. Use a foreign key when you need to enforce referential integrity between tables, such as a department_id in Employees that references Departments.department_id. In practice, every table should have a primary key, and foreign keys are added to model one-to-many or many-to-many relationships.