Unique constraints with multiple NULL values

[former_SQLA_member1694886]

If I am not mistaken, under ANSI SQL standards a UNIQUE constraint must be able to disallow duplicate non-NULL values but accept multiple NULLs.

Is there a way to achieve that with SQL Anywhere 11 without using triggers?

FYI, this is how SQL Server 2008 does it:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

---

Background:

In my table below, either city_id OR country_id cannot be null. I want to avoid dupes with region_id

CREATE TABLE "DBA"."GEO_REGIONCONTENTS" (
    "REGIONCONTENT_ID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "REGION_ID" INTEGER NOT NULL,
    "CITY_ID" INTEGER NULL,
    "COUNTRY_ID" INTEGER NULL
    CONSTRAINT "PK_REGIONCONTENTS" PRIMARY KEY ( "REGIONCONTENT_ID" ASC )
);

[VolkerBarth]

"EITHER city_id OR country_id cannot be null": I would enforce such a rule with a CHECK constraint (both in SA and in MS SQL), something like "CHECK (city_id is not null and country_id is null or city_id is null and country_id is not null)". (In case MS SQL 2008 has a XOR logical operator, you could use that here, SA has none.)

[former_SQLA_member1694886]

@Volker. Thanks for the tip. Should've thought of that

[Former Member · Accepted solution]

There are two different mechanisms for this in SQL Anywhere: UNIQUE constraints and unique indexes. Indexing is not addressed by the ISO/ANSI SQL Standard.

In SQL Anywhere, a unique index over nullable columns treats each NULL value as a distinct value. So if one has

CREATE TABLE FOO (X INT NULL, Y INT NULL, Z INT NULL);
CREATE UNIQUE INDEX BAR (X, Y, Z) ON FOO;
INSERT INTO FOO VALUES (1, 2, 3);
INSERT INTO FOO VALUES (4, 5, 6);
INSERT INTO FOO VALUES (NULL, NULL, NULL);
INSERT INTO FOO VALUES (NULL, NULL, NULL);
INSERT INTO FOO VALUES (NULL, NULL, NULL);

then no uniqueness violations will be reported.

Conversely, in SQL Anywhere a UNIQUE constraint does not permit null values at all.

alter table foo add constraint unique(x)

for the above table will yield an error because the definition of column x permits null values.

For completeness, I note that in SQL Anywhere 12, CREATE INDEX supports the ability to treat NULL values as identical values, so that the index can contain at most one NULL value in that column. This functionality is provided through the WITH NULLS NOT DISTINCT clause.

[former_SQLA_member1694886 · Accepted solution]

Thanks for clarifying the distinction between unique constraints and indices