All 7 Constraints (Primary Key, Foreign Key, Check & Unique) questions
More in Constraints (Primary Key, Foreign Key, Check & Unique)
What is a primary key constraint, and why is it important for a database table? What is a foreign key constraint, and how does it help maintain data integrity between two related tables? What is a check constraint, and how do you use one to enforce a specific business rule at the database level? How do you temporarily disable a constraint to perform a bulk data load, and how do you safely re-enable it afterward? How do cascading actions work with foreign key constraints, such as ON DELETE CASCADE, and what risks come with using them? How would you add a new constraint to an existing table that already contains data, especially if some existing rows might violate that constraint?
Advertisement
SQL Server › Constraints (Primary Key, Foreign Key, Check & Unique)

How is a unique constraint different from a primary key constraint, and when would you use one instead of the other?

Intermediate

Answer

A unique constraint, like a primary key, ensures every value in a column is distinct, but unlike a primary key it does allow a single NULL value and a table can have multiple unique constraints, making it useful for enforcing uniqueness on columns like an email address that are not the table's main identifying key.
CREATE TABLE Customers (
  CustomerId INT PRIMARY KEY,
  Email VARCHAR(255) UNIQUE
);
Real-world example A customer database uses a unique constraint on the email column to prevent duplicate customer accounts from being created with the same email address, while still using CustomerId as the actual primary key.

Common follow-ups: How many unique constraints can a single table have?;Does a unique constraint automatically create an index behind the scenes?

Data Types & Schema Design;Indexes
All 7 Constraints (Primary Key, Foreign Key, Check & Unique) questions