Primary Key vs. Unique Key: Everything You Need to Know (Basic 1 - 25)

Primary key and unique key are two important database constraints that are used to ensure the integrity of data. Both constraints are used to prevent duplicate values in a table, but there are some key differences between the two.

Primary Key

A primary key is a unique identifier for each row in a table. It cannot be null and must be unique. Primary keys are used to enforce entity integrity, which means that each row in a table represents a unique entity in the real world.

For example, the primary key of a table that stores customer information might be the customer_id column. This ensures that each customer in the table has a unique identifier and that no two customers have the same identifier.

Unique Key

A unique key is a column or group of columns in a table that cannot contain duplicate values. However, unique keys can contain null values. Unique keys are used to enforce referential integrity, which means that the values in one table can be referenced by the values in another table without ambiguity.

For example, the order_id column in an orders table might be a unique key. This ensures that each order in the table has a unique identifier and that no two orders have the same identifier. This also allows other tables, such as the order_items table, to reference the orders table using the order_id column.

Differences between Primary Key and Unique Key

The following table summarizes the key differences between primary key and unique key:

Feature	Primary Key	Unique Key
Purpose	To uniquely identify each row in a table	To prevent duplicate values in a column or group of columns
Null values	Not allowed	Allowed
Number of constraints per table	One	Multiple
Referential integrity	Enforced	Not enforced
Clustered index	Automatically created	Can be created manually

Examples

The following SQL statements create a table with a primary key and a unique key:

SQL

CREATE TABLE customers (
  customer_id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY (customer_id)
);

The customer_id column is the primary key, and the email column is a unique key. The customer_id column cannot be null and must be unique, while the email column can be null but cannot contain duplicate values.

Conclusion

Primary key and unique key are two important database constraints that are used to ensure the integrity of data. Primary keys are used to uniquely identify each row in a table, while unique keys are used to prevent duplicate values in a column or group of columns.

When choosing which constraint to use, it is important to consider the specific needs of your database. If you need to uniquely identify each row in a table, then you should use a primary key. If you need to prevent duplicate values in a column or group of columns, but you also need to allow null values, then you should use a unique key.