| Author |
Topic |
|
sqlbug
Posting Yak Master
201 Posts |
 Posted - 2009-07-08 : 14:31:25
|
| In an existing application where I don't want to change the table structures/relationships - I need to create a unique key on some columns to fix a bug.But didn't find any direct way to do it. The columns are :FirstName, LastName, MiddleInitial, DegreeTitle and Organization.Need help please.Thanks. |
|
|
Skorch
Constraint Violating Yak Guru
300 Posts |
Posted - 2009-07-08 : 14:33:35
|
| You could add a YourTableNameID column and set it as an IDENTITY.Some days you're the dog, and some days you're the fire hydrant. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-08 : 14:41:52
|
| you can use ALTER TABLE yourtable ADD NewCol int IDENTITY(1,1) and make it primary key |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-08 : 14:49:38
|
It might be the correct solution, but why does everone always jump to surrogation to make rows unique?Try this maybe?CREATE UNIQUE INDEX IndexName ON TableName ( FirstName, LastName, MiddleInitial, DegreeTitle, Organization ) |
|
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2009-07-08 : 14:50:00
|
| Skorch and Visakh, I already have a primary key and I don't want to change it.Lamprey...I think thats what I should use.Thanks to all of you. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-08 : 15:46:22
|
Apparently, just some of the columns not all.quote:
Originally posted by X002548
What do you need to be unique?
quote:
Originally posted by sqlbug
<snip> I need to create a unique key on some columns to fix a bug.But didn't find any direct way to do it. The columns are :FirstName, LastName, MiddleInitial, DegreeTitle and Organization.Need help please.Thanks.
|
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-08 : 16:52:00
|
| So it would seem.. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-09 : 10:21:17
|
| Although it seems you are dealing with a whole bunch of crap and this will sound very petty, but may I suggest that you replace Lamprey's suggestion of a unique index with a unique constraint. It's a small point, but semantically what you asked for. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-07-09 : 11:03:20
|
quote:
Originally posted by LoztInSpace
Although it seems you are dealing with a whole bunch of crap and this will sound very petty, but may I suggest that you replace Lamprey's suggestion of a unique index with a unique constraint. It's a small point, but semantically what you asked for.
What's the difference? (besides the syntax to create)I think the result is the same, isn't it?Be One with the OptimizerTG |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-07-09 : 11:32:13
|
| They are the same, SQL Server manafests Unique Constraints and Priamry Keys as Indexes. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-07-11 : 09:12:04
|
| Yeah I'm being anal I know. For me, unique constraints are for business rules (thou must have only one X per Y) and indexes are to increase performance on joins and WHERE clauses etc.As you say, they are all implemented the same, it's just how the intent is communicated. |
|
|
|
|
Unique Key