SAP SQL Anywhere

Question

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 )
);

Accepted Answer

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.

SAP SQL Anywhere

Unique constraints with multiple NULL values

Follow this question

Related questions