2

Well I wonder if I can do the following on database level (instead of server level). We are using PostgreSQL as the SQL flavour.

I have multiple columns. Now I wish to make sure each entry into the database has a unique column. Normally this would be a simple

ALTER TABLE test
ADD UNIQUE(X, Y)

However in our application "NULL" means not "no data" but rather "wildcard". (unless you think there's a better general purpose to specify this). What this means is that for unique constraint when one of the columns is not filled, it "matches everything", ie the following tables would violate unique constraints (in addition to normal constraints):

X    | Y
'a'  | 'B'
'a'  | null


X    | Y
'a'  | 'B'
null | 'B'

While this would be obviously "ok":

X    | Y
'a'  | 'B'
null | 'C'


X    | Y
'a'  | 'B'
'b'  | null

Can I put such a constraint on database level? Or do I have to guarantee this on server level?

ps: this is not the primary key, there is always a column id for primary keys. Also in this example I use strings, but actually the datatypes are different and can be anything.

  • The described scheme is contradictory I think. Assume you have records ('a','X') and ('a','Y'). You update 2nd record to ('a',null). Your constraint prevents this, assuming final record state is wrong. But it is possible that it is correct whereas 1st record is wrong. This differs from unique constraint check where one of the records which interfere not exists. – Akina Feb 20 at 10:43
  • What you seek is called an ASSERTION. But, They don't exist for any major RDBMS. – Michael Kutz Feb 20 at 10:47
  • 1
    @Akina how is that different from the idea that you have ('a', 'X') & ('a', 'Y') and change the second to ('a', 'x') - this would also be prevented by the constraint, however it might be that the first is "wrong". (Notice that this is not primary key, and there is more data attached to records so they are not equal, it's just a database state that is not allowed). – paul23 Feb 20 at 10:48
  • 1
    What do you mean by 'database level' and 'server level'? Constraints are per table... Also, I'd rather user some special value as the wildcard ('*' for text, or NaN for numeric and float, or -1 or -99999 or similar for integers and so on). – dezso Feb 20 at 11:43
  • @paul23 From the point of uniqueness their final states are duplicates. Whereas in my case - no. In your case, regardless of which record remains, the record ('a', 'Z') is valid, whereas in my case it is dependent by what record is remained. – Akina Feb 20 at 11:57
2

I suggest you fix your design. Using NULL as a wildcard is not the best in my opinion. Splitting the table into 2 or 3 so the wildcard information is stored in separate tables would be better.

If you keep this design, it will need some complex exclusion constraints, besides the UNIQUE one:

CREATE TABLE test (
    test_id serial NOT NULL PRIMARY KEY,
    x int NULL,
    y int NULL,

-- unique constraint for non-null (x,y) values 
CONSTRAINT x_y_unique
    UNIQUE (x,y),

    -- when x IS NULL, do not allow a non-null x value (with same y)
    CONSTRAINT unique_with_wildcards_x_null
        EXCLUDE USING GIST
        ( (CASE WHEN x IS NULL THEN 0 ELSE 1 END) WITH <>,
          y with =
        ),

    -- do not allow 2 NULL x values (with same y)
    CONSTRAINT unique_with_wildcards_x_one_null
        EXCLUDE USING GIST
        ( (CASE WHEN x IS NULL THEN 0 ELSE NULL END) WITH =,
          y with =
        ),

    -- when y IS NULL, do not allow a non-null y value (with same x)   
    CONSTRAINT unique_with_wildcards_y_null
        EXCLUDE USING GIST
        ( x with =,
          (CASE WHEN y IS NULL THEN 0 ELSE 1 END) WITH <>
        ),

    -- do not allow 2 NULL y values (with same x)
    CONSTRAINT unique_with_wildcards_y_one_null
        EXCLUDE USING GIST
        ( x with =,
          (CASE WHEN y IS NULL THEN 0 ELSE NULL END) WITH =
        )
) ;

Tested in dbfiddle.uk.

Notice that the (NULL, NULL) combination is allowed. If you want this to be treated as a super-wildcard, (so if it's in the table, no other rows would be allowed), you'll need some more exclusion constraints.

  • This hard to extend to 5 columns (as would splitting the table up, which would then have any combination of x_0, x_1, x_2, x_3, x_4 & x_5 column to be null). Basically it corresponds to a SELECT, with "null" removing the column from the WHERE statement. - and the server always expects a single result for such queries, otherwise certain operations are meaningless. – paul23 Feb 20 at 13:44
  • 1
    Of course it's hard to extend to 5 columns. Does your design require a 2 or 5-column unique constraint? Can you give an example of this "SELECT with null"? – ypercubeᵀᴹ Feb 20 at 13:46
  • 1
    We might be able to come up with a better design and/or a better way to write the queries. – ypercubeᵀᴹ Feb 20 at 13:58

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.