Consider the table:

create table mytable(
id int primary key autoincrement,
key varchar(20) not null,
lang char(2) not null,
country char (2) null,
value varchar(250) not null
)

I want to add the following unique index:

create unique index on mytable (key, lang, country)

However, sybase doesn't treat NULL the same as every other
database I've ever worked with. These 2 statements work,
though I consider this completely flawed:

insert into mytable (1, 'key', 'en', null, 'A');
insert into mytable (2, 'key', 'en', null, 'B');

The obvious workaround for this is to use a unique
constraint, except that the country field can't be included
(since it's null).

Surely, there must be a way to make this work. Suggestions
are appreciated.

A combination of a unique nullable index and a trigger would
be required.

It's a 3-state thing ... (null!=null) is false in all cases. Neither is
(null = null) true. That is why you need a trigger to check for
the exisitence of a row where the ("<key/>" is null) to prevent
the duplicate inserts. Updates and deletes are just find by
themselves, it is only the potential for duplicate inserts that
your are worried about.

P.S. In fact both of these queries return empty results sets
select 1 from dummy where null!=null;
select 1 from dummy where null=null;

P.P.S. But having said that having a predicate where key=null
is actually going to fail for casual searches. So ... even
though the above index+trigger technique 'appears to
handle the issue', your application still needs to be aware
of any null equate conditions and it need to recode the
SQL for that case checking for IS NULL instead of
an equality '=' relation. Not natural at all.

<todd> wrote

NULLs will cause problems like this. I always use an empty string instead of
NULL to signify the absence of string data. Doing that would allow you to
put a UNIQUE constraint on the table:

create table mytable(
id int primary key default autoincrement,
"key" varchar(20) not null,
lang char(2) not null,
country char (2) NOT NULL DEFAULT '',
value varchar(250) not null,
UNIQUE ("KEY", LANG, COUNTRY)
)

Richard

On Mon, 26 Nov 2007 18:04:34 +0100, <todd> wrote:


different then other platforms may be, but flawed, no.

if you want to follow the strict logic of nulls, nulls
would be totally disallowed for unique indexes. since,
if null represents an unknown value, how would you know
if it clashes with an existing one?

totally consistent null behaviour is so restrictive, it
is not practical to enforce. if you accept that, the above
behaviour is not flawed. and what you want, is not less
flawed.

Not that this helps you with 9.x, but I am 90% sure that UNIQUE
constraints in SQL Anywhere used to support at most one NULL value
(essentially treating NULL as a special value in each domain, in exactly
the same way that DISTINCT and GROUP BY treat NULLs as equal values.

However, that was a long time ago, probably 12 years (and my memory
isn't what it used to be). I've gone back to Watcom SQL 4.0e, and even
in that version there is a restriction that UNIQUE constraints cannot be
created over nullable columns. I have so far been unsuccessful in
tracking down that modification, but I'm still looking.

In current software, your only method to enforce at most one NULL value
is with a trigger, as Nick has outlined. In discussing your situation
with my staff, we are considering making a change to secondary indexes
so that one can specify whether or not a single, or multiple, NULL
value(s) are permitted in an index.

Glenn

todd wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

Probably 13+ ... because I've been here 12 and I don't ever
remember that behaviour (unless it was a 3.2/4.0 GA bug)
.... but I once worked with a number of products (Dick Pick's
legacy of Pick, Revelation, Universe, ... ) that did support
a singleton null key.

Too often that simply ended up being the bit bucket for bad
code that lost it's way (and its key ... after long, late nights of
coding; no alcohol was required either).

"Glenn Paulley" <paulley (AT) ianywhere (DOT) com> wrote