SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    suffix TEXT,
    UNIQUE (first_name, last_name, suffix)
);
✓
CREATE UNIQUE INDEX idx_people ON people(first_name, last_name) WHERE suffix IS NULL;
✓
INSERT INTO people (first_name, last_name, suffix)
VALUES
    ('Joe', 'Dirt', 'Sr'),
    ('Joe', 'Dirt', 'Jr'),
    ('Joe', 'Dirt', 'Sr');

{
  "code": "SQLITE_CONSTRAINT_UNIQUE"
}


SELECT * FROM people;


id
first_name
last_name
suffix


INSERT INTO people (first_name, last_name)
VALUES
    ('Chris', 'Farley'),
    ('Chris', 'Farley');

{
  "code": "SQLITE_CONSTRAINT_UNIQUE"
}


SELECT * FROM people;


id
first_name
last_name
suffix