By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,250 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Unique constraint in .ADP

P: n/a
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

Jul 26 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Jul 26 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.