0

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?

2 Answers 2

0

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.