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.