adding column to an existing unique key constraint

Question

adding column to an existing unique key constraint

Najmunnisha Popli

SSC Rookie

Points: 25
More actions
May 13, 2005 at 5:31 am

#164391

hi all,
i want to know whether the data will be affected if i add a column to an existing unique key constraint(non-clustered) ?
there is a unique key constraint (7 columns involved - a non-clustered index). i want to add an 8th column to this constraint.
also i want to know how to go about modifying this constraint in Query Analyzer as i cannot use Enterprise Manager.
please help me .. as its an urgent matter
thanx
Najmun

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I have not had an issue with this altering data. It will alter the table though and could have profound effects on existing inserts, etc..., as well as select statements which do not account for this change.

I would suggest you look up ALTER TABLE in BOL.

I wasn't born stupid - I had to study.

Najmunnisha Popli SSC Rookie Points: 25 More actions · Answer 2

thanx for the response

i have looked up BOL for ALTER TABLE syntax.. but didn't find anything which can modify the existing constraint... a constraint can be added or dropped thru ALTER TABLE...

i didn't get u.. u said that alter table will have effects on existing inserts, etc.. select statements.. what does all this mean ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

CREATE UNIQUE INDEX [IX_TypesObjAccess_TypeAccess_TypeName_Unique] ON [dbo].[TypesObjAccess] ([TypeAccess], [TypeName], [PkTypeObjAccess])

WITH

DROP_EXISTING

ON [PRIMARY]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

He meant that something that worked before might not work after this modification. But since you're adding a column instead of removing one I don't see many likely problems. The only one I can think off is that a select that was using all those 7 columns could now return something that looks like a duplicate if the 8th column is no added to it... There might be some other effects but I'm not aware of 'em.

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Thanks Remi. I was unaware of WITH DROP_EXISTING.

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

That's the one thing that makes me like EM... you can read up the code it generates and learn new stuff.