Managing Indexes - Alter Constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Managing Indexes - Alter Constraint

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-16 : 09:59:42

A table which I inherited has a contraint, no duplicate SerialNumber.

This needs to be adjusted.

It should be no duplicate ModelNumber AND SerialNumber.
(just discovered this after multiple failures inserting data).

How can this index be altered?

Thanks,

Zath

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-16 : 10:01:50

You can't alter. Drop and create it.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-16 : 10:09:52

Ok, drop it first.

The how would this be changed to include ModelNumber/SerialNumber combination?

CREATE UNIQUE
INDEX [IX_tblEquipments] ON [dbo].[tblEquipments] ([SerialNumber])
WITH
DROP_EXISTING
ON [PRIMARY]

Thanks again,

Zath

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-16 : 10:15:58

[code]
ON ON [dbo].[tblEquipments] ([ModelNumber], [SerialNumber])
[/code]

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-16 : 10:24:45

Thanks for the reply. Simple enough but there seems to be something
else preventing the script from executing:

Cannot re-create index 'IX_tblEquipments'. The new index definition
does not match the constraint being enforced by the existing index.

I wouldn't even let me delete it.

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-16 : 10:27:47

Ok, I got it.

Thanks again!!!!

Zath

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-03-17 : 21:32:57

Don't know about you guys, but I prefer to specify constraints as constraints not indexes. If SQL Server wants to create an index to enforce it (and it will) then fair enough.
In my book uniqueness is mostly a business rule and an index is a technology solution to an access problem.
I'd be interested to know what you gurus think.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources