A unique constraint is a database rule that prevents duplicate values in one column or in a specified combination of columns. Use it for nonkey data that must be distinct in the real world, such as a username, phone number, or identifier. Choose the constraint from the requirement: ask whether two different rows should ever be allowed to share that value or combination. section 6.14 section 6.17
A primary key already identifies each row, but it may not prevent duplication in other important fields. For example, an automatically incrementing customer ID can identify records while separate unique constraints on email_address and phone_number prevent repeated account information. section 8.5 section 6.14
Add a unique constraint in three practical steps:
- Identify nonkey attributes where duplicates make no sense from the stated requirements. section 6.14
- Use one constraint per independently unique column, such as username or phone_number. section 6.14
- Use one composite constraint when only the combined values must be distinct, such as a product name together with its manufacturer. section 6.14
Unique usernames and phone numbers
A user table can keep email as its primary key while requiring each username and phone number to occur only once. Naming constraints, such as unq_username and unq_phone_number, makes later restructuring or migration easier to manage. section 6.14 section 6.17
-- comment: works for MySQL, MariaDB and PostgreSQL
-- comment: see the code repo for other RDBMS
CREATE TABLE user (
email VARCHAR(320) PRIMARY KEY,
username VARCHAR(30) NOT NULL,
password VARCHAR(20) NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
phone_number VARCHAR(15),
last_login_name TIMESTAMP NOT NULL,
CONSTRAINT unq_username UNIQUE(username),
CONSTRAINT unq_phone_number UNIQUE(phone_number)
);
This SQL definition shows two named single-column UNIQUE constraints alongside a primary key and NOT NULL constraints. section 6.14
Preventing duplicate addresses
An address table can use a simple surrogate address_id as its primary key and a composite unique constraint across street address, optional address line, postal code, city, and state. This keeps one address record from being stored repeatedly while allowing other tables to reference it through one foreign-key column. section 7.6
-- comment: works for MySQL and MariaDB
-- comment: see the code repo for other RDBMS
CREATE TABLE IF NOT EXISTS address (
address_id INT AUTO_INCREMENT PRIMARY KEY,
street_address VARCHAR(255) NOT NULL,
address_line_optional VARCHAR(100),
city VARCHAR(100) NOT NULL,
state VARCHAR(20) NOT NULL,
postal_code CHAR(5) NOT NULL,
CONSTRAINT unique_address_constraint
UNIQUE (street_address, address_line_optional,
postal_code, city, state)
);
This SQL listing pairs an auto-incrementing address primary key with a named composite UNIQUE constraint over the address fields. section 7.6
