Constraints and Indexes

Unique Constraint

Preventing Duplicates

A unique constraint says a column value may appear only once.

Program

Play the script to store accounts with unique emails.

unique_constraint.sql
Replay: real traced execution (multi-file project)
CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT UNIQUE);
INSERT INTO accounts VALUES (1, 'ada@example.com'), (2, 'lin@example.com');
SELECT email FROM accounts ORDER BY email;

  1. tables ← 1 row

    1CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT UNIQUE);2INSERT INTO accounts VALUES (1, 'ada@example.com'), (2, 'lin@example.com');
    values this step1 rowtables

  2. accounts ← 2 rows

    1CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT UNIQUE);2INSERT INTO accounts VALUES (1, 'ada@example.com'), (2, 'lin@example.com');3SELECT email FROM accounts ORDER BY email;
    values this step2 rowsaccounts

  3. result ← 2 rows

    2INSERT INTO accounts VALUES (1, 'ada@example.com'), (2, 'lin@example.com');3SELECT email FROM accounts ORDER BY email;
    values this step2 rowsresult

Follow the Emails

  1. accounts has id INTEGER PRIMARY KEY and email TEXT UNIQUE.
  2. The script inserts id 1 with ada@example.com.
  3. It inserts id 2 with lin@example.com.
  4. UNIQUE means those email values cannot be duplicated in this table.
  5. ORDER BY email returns ada@example.com before lin@example.com. | id | email | final order | | --- | --- | --- | | 1 | ada@example.com | 1 | | 2 | lin@example.com | 2 |
UNIQUE `UNIQUE` prevents duplicate values in a column.
constraint A constraint is a rule the database enforces.
data quality Unique emails avoid duplicate account identity.

Exercise: unique_constraint.sql

Reproduce the ordered emails, then identify which column the UNIQUE rule protects from duplicates.