Composite unique contstraint with null values

Question

How can I set a composite unique constraint that can properly handle null values in SQLite? This is likely better expressed by example:

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)
);

The constraint works as expected when a person with a suffix is entered more than once.

-- Last insert is denied
INSERT INTO people (first_name, last_name, suffix)
VALUES
    ('Joe', 'Dirt', 'Sr'),
    ('Joe', 'Dirt', 'Jr'),
    ('Joe', 'Dirt', 'Sr');

However, the unique constraint is not accommodating when a person without a suffix is inserted more than once.

-- Both are permitted
INSERT INTO people (first_name, last_name)
VALUES
    ('Chris', 'Farley'),
    ('Chris', 'Farley');

I understand SQLite treats null values as independent from one another, but is there a way to get around this?

Answer 1

You can create a unique partial index for first_name and last_name only when suffix is null:

CREATE UNIQUE INDEX idx_people ON people(first_name, last_name) 
WHERE suffix IS NULL;

See a simplified demo.

If your version of SQLite is 3.31.0+, you could create a generated column which returns an empty string when suffix is null and use it in the unique constraint instead of suffix:

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    suffix TEXT,
    str_suffix GENERATED ALWAYS AS (COALESCE(suffix, '')),
    UNIQUE (first_name, last_name, str_suffix)
);