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



  • 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?



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

    A minimalist https://www.postgresql.org/docs/current/indexes-partial.html 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 https://www.postgresql.org/docs/current/indexes-multicolumn.html 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:

    • https://stackoverflow.com/a/8289253/939860

    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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2