How to enforce only one NULL value per unique combination of some other columns?

All we need is an easy explanation of the problem, so here it is.

here’s the situation, I have a table A with like such

CREATE TABLE A
(
    id         UUID    NOT NULL PRIMARY KEY,
    B_id       bigint  NOT NULL,
    C_id       bigint  NOT NULL,
    some_value TIME,
    enabled    BOOLEAN NOT NULL,

    CONSTRAINT A_B_id_fkey FOREIGN KEY (B_id) REFERENCES B (id),
    CONSTRAINT A_C_id_fkey FOREIGN KEY (C_id) REFERENCES C (id)
);

And I want to have only one null value for some_value when enabled is false. Also, I want to ensure this null is for all foreign keys. So I can have x*y null values, but only one per couple of (x, y). I did something like this :

CREATE UNIQUE INDEX CONCURRENTLY idx_A_some_value
    ON A (B_id, C_id, (some_value IS NULL), (enabled IS FALSE))
    WHERE (some_value IS NULL);

CREATE UNIQUE INDEX CONCURRENTLY idx_A_some_value
    ON A (B_id, C_id, (some_value IS NOT NULL), (enabled IS TRUE))
    WHERE (some_value IS NOT NULL);

Does it seems correct? Is there a better way? Also, performance is important in my case. Thanks

Edit:

I reworked the conditions and did something much more simpler:

CREATE UNIQUE INDEX CONCURRENTLY idx_un_A
    ON A (B_id, C_type_id);

ALTER TABLE A
    ADD CONSTRAINT some_value_null_not_enabled
        CHECK ( (enabled IS FALSE AND some_value IS NULL) OR
                (enabled IS TRUE AND some_value IS NOT NULL));

Is it better?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

From this constraint:

 CHECK ( (enabled IS FALSE AND some_value IS NULL) OR
            (enabled IS TRUE AND some_value IS NOT NULL));

…the "enabled" column is redundant and simply means "some_value IS NOT NULL". If it is used in queries, it could be replaced with a generated column, or simply removed from the table.

CREATE TABLE A
(
    id         INT NOT NULL PRIMARY KEY,
    B_id       INT NOT NULL,
    C_id       INT NOT NULL,
    t          TIME
--    ,enabled    BOOL NOT NULL GENERATED ALWAYS AS (t IS NOT NULL)
);

So, for each (B_id,C_id) you want at most one null value in column t. Note "enabled" is gone, which simplifies the situation. You could do:

CREATE UNIQUE INDEX idx_A_null_t ON (B_id,C_id) WHERE t IS NULL;

Now, if "enabled" does something more than replicate "t is null" then you need both columns. This would be the case if there are rows where t is not null, and enabled is false. If what you want is: for each (B_id,C_id) at most one row with enabled=false and t is null, then:

CREATE UNIQUE INDEX idx_A_null_t ON (B_id,C_id) WHERE t IS NULL AND NOT enabled;

…and add a constraint saying t cannot be null if enabled is true.

Note, this doesn’t seem related to the question:

CREATE UNIQUE INDEX CONCURRENTLY idx_A_some_value
ON A (B_id, C_id, (some_value IS NOT NULL), (enabled IS TRUE))
WHERE (some_value IS NOT NULL);

Because this one means there can be at most one row with t not null for each value of (B_id,C_id), which doesn’t sound like what you want.

Method 2

I want to have only one null value for some_value when enabled is false.

A minimalist partial unique index can do that:

CREATE UNIQUE INDEX a_some_value_not_enabled_uni_idx ON a (1)  -- constant
WHERE some_value IS NULL AND NOT enabled;

This index will hold at most one row.

I want to ensure this null is for all foreign keys. So I can have x*y null values, but only one per couple of (x, y).

A partial multicolumn unique index:

CREATE UNIQUE INDEX a_special_uni_idx ON a (a_id, b_id)
WHERE some_value IS NULL;

This allows each combination of (a_id, b_id) only once when some_value is null. Note that rows with NULL values in either a_id or b_id evade this restriction. See:

I guess you want to allow at most one NULL value in some_value per (a_id, b_id).

I also guess your question is unclear.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply