| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-08-18 : 08:23:54
|
| I've got an IDENTITY column that's not constrained to UNIQUE. If I remember right, it'll be possible to have duplicate values unless the UNIQUE constraint is applied.I can't seem to get ALTER TABLE MyTable ALTER COLUMN MyColumn ADD CONSTRAINT UNIQUEto work. Bad syntax or not supported?Sam |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-18 : 08:57:57
|
Hi Sam,Try the following syntax: ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> UNIQUE (<column_name>)GO |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-18 : 09:57:35
|
| Yep, that's a go.It's a maze trying to follow the BOL's BNF ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 13:47:00
|
| Ought you to make an INDEX, or is that not important?I remain confused about a UNIQUE CONSTRAINT that doesn't specifically ask for an INDEX.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-18 : 14:22:28
|
| A unique constraint will be supported by a unique index (just like a PK).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 14:31:17
|
| So is ther eany significance to the fact that you can create either a Unique Index or a Unique Constraint??Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 14:33:51
|
| We've discussed this before. Let me find the thread.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 14:40:04
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32773Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 14:54:42
|
| Thanks Tara, kind of you to look that up. But I'm sorry to say I have failed to understand the benefits, or necessities, of one over the other.The nub seems to be that a constraint implies something. I understand that, although I don't see what a constraint "implies" that an index doesn't "take care of". And I fail to see any performance benefit of one over the other (given that tehre is always an INX which the optimiser can use if it chooses).Sorry for being dense!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 15:00:17
|
| A constraint is used for referential integrity. A unique constraint is constrained via a unique index. I guess the constraint is there kinda like a foreign key is there even though maybe your application prevents orphaned data. With a constraint, you can diagram it.Somebody help me explain this better!Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 15:09:48
|
| No need, I think I've got it now.They both do the same job of keeping the data safe, but a constraint "implies" that fact to the system - so more can be inferred from it - such as the Diagramming example you give.Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 16:10:37
|
| Yes!Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 16:39:15
|
 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 16:46:20
|
| Are there any circumstances where a UNIQUE INDEX would be more, or "differently", useful than a Unique Constraint? (I presume the stuff in BOL about allows NULLs just means that ONE NULL value is allowed)Perhaps use of a Unique Clustered Index (were the PK did not need a clustered index)Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-18 : 17:54:07
|
| Yep - one null in a unique index/constraint (unlike some other databases - argue amongst yourselves)Clustering has nothing to do with uniquenes or PKs - just that a PK will default to being clustered if there is no clustered index (why is beyond me).A unique constraint is part of the table structure whereas an unique index is a separate object - not a very important distinction except with table variables which allow constraints but not indexes.Which you use is up to you - I prefer unique indexes because they are easier to drop and rebuild for bcp.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 23:08:09
|
| "I prefer unique indexes because they are easier to drop and rebuild for bcp"Yes, that's a useful point. I have scripts of DROP & CREATE statements for INDEXES (and FKs for that matter) for our databases to make importing data easier/faster.I can't think that I've ever used a UNIQUE CONSTRANT - always a UNIQUE INDEX.Seems like a UNIQUE CONSTRAINT could hide the [performance] issue of there being an index present (I mean, to the unwary or part time DBA!)Kristen |
|
|
|
|
Alter Column UNIQUE