Unique constraint in .ADP

[D.Stone]

Hi, I'm getting hung up on a trivial schema change - I need to make a
column accept only null or unique values and Books Online avers 'what
you need is a UNIQUE constraint'. Fine.

I've tried doing this interactively both in Access and in Enterprise
Manager; I've deleted the column and recreated it; I've written a T-
SQL script to ALTER TABLE. All fail with messages along the lines of:

- Unable to create index 'IX_Student'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
UNIQUE INDEX terminated because a duplicate key was found for index ID
53. Most significant primary key is '<NULL>'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.

This doesn't seem sensible: the column has no data. The reference to
null primary key also makes no sense: the (different) primary key
column contains no nulls.

Anyone any idea what I could be doing wrong?

Cheers,

Dave

[Baz]

You aren't doing anything wrong. Crazy as it might seem, multiple Nulls do
indeed fall foul of a unique constraint.

If you must have this (and I agree that it seems a perfectly reasonable
thing to ask for), you'd best write a trigger to enforce it.

<D.*****@ed.ac.ukwrote in message
news:11**********************@l70g2000hse.googlegr oups.com...

Hi, I'm getting hung up on a trivial schema change - I need to make a
column accept only null or unique values and Books Online avers 'what
you need is a UNIQUE constraint'. Fine.

I've tried doing this interactively both in Access and in Enterprise
Manager; I've deleted the column and recreated it; I've written a T-
SQL script to ALTER TABLE. All fail with messages along the lines of:

- Unable to create index 'IX_Student'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
UNIQUE INDEX terminated because a duplicate key was found for index ID
53. Most significant primary key is '<NULL>'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.

This doesn't seem sensible: the column has no data. The reference to
null primary key also makes no sense: the (different) primary key
column contains no nulls.

Anyone any idea what I could be doing wrong?

Cheers,

Dave