What is a key in a table?

A key in a table is one or more columns whose values are used for uniquely identifying rows, linking to other tables, or optimizing queries.

Main types of keys:

• Primary Key:
  • Uniquely identifies each row in the table.
  • Cannot contain NULL values.
  • There can be only one primary key in a table.
• Foreign Key:
  • References a primary key in another (or the same) table.
  • Establishes a relationship between tables, ensuring referential integrity.
  • Can contain NULL values (unless specified otherwise).
• Candidate Key:
  • A set of columns that can serve as a primary key because it uniquely identifies each row.
  • One candidate key is selected to become the primary key.
• Unique Key:
  • Ensures the uniqueness of values in a column, but can contain NULL values (usually one).
  • Differs from the primary key in that it allows NULLs and can be multiple in a table.

Example:

Table users:

user_id (Primary Key)	username	email (Unique Key)
1	alice	alice@example.com
2	bob	bob@example.com

Table orders:

order_id (Primary Key)	user_id (Foreign Key referencing users.user_id)	total_amount
101	1	100.00
102	2	50.00

In users, user_id is the primary key, and email is a unique key. In orders, order_id is the primary key, and user_id is a foreign key linking orders to users.