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

asked 31 Mar '10, 17:51

Vincent%20Buck's gravatar image

Vincent Buck
70681520
accept rate: 15%

Comment Text Removed
1

"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.)

(01 Apr '10, 08:11) Volker Barth

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

(01 Apr '10, 11:48) Vincent Buck

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.

permanent link

answered 31 Mar '10, 19:13

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Comment Text Removed

Thanks for clarifying the distinction between unique constraints and indices

(31 Mar '10, 19:32) Vincent Buck
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×9
×5
×4

question asked: 31 Mar '10, 17:51

question was seen: 4,276 times

last updated: 31 Mar '10, 19:13