UNIQUE across columns in SQL

One value unique on its own is a different rule from a pair unique together.

CREATE TABLE memberships (
    id INTEGER PRIMARY KEY,
    team_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    invite_code TEXT UNIQUE,
    role TEXT NOT NULL DEFAULT 'member',
    UNIQUE (team_id, user_id)
);

How it works

1. A column UNIQUE rejects any repeat of that single value.
2. A table UNIQUE (a, b) only rejects a repeat of the whole pair.
3. In SQLite, NULLs are all distinct, so a UNIQUE column allows many.

Keywords and builtins used here

• CREATE
• DEFAULT
• INTEGER
• KEY
• NOT
• NULL
• PRIMARY
• TABLE
• TEXT
• UNIQUE
• role

The run, in numbers

Lines

8

Characters to type

193

Tokens

38

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 27 seconds.

Type this snippet

Step 2 of 5 in Constraints, step 2 of 16 in Schema & constraints.

← Previous Next →