| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2002-06-26 : 22:48:07
|
| <CODE>CREATE TABLE Widget ( [WidgetID] uniqueidentifier ROWGUIDCOL NOT NULL , [WidgetName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[Widget] WITH NOCHECK ADD CONSTRAINT [PK_Widget] PRIMARY KEY CLUSTERED ( [WidgetID] ) ON [PRIMARY] GOALTER TABLE [dbo].[Widget] WITH NOCHECK ADD CONSTRAINT [DF_Widget_WidgetID] DEFAULT (newid()) FOR [WidgetID], CONSTRAINT [IX_Widget_WidgetName] UNIQUE NONCLUSTERED ( [WidgetName] ) ON [PRIMARY] GO</CODE>I want to make sure that the Widget names are unique across the entire table. Should I create an Index like this, or should I let my VB App Call a "DoesNameExist" stored proc before inserting any records? What's the best way to impliment something like this?Any insights are welcomed :)Thanks all!Michael--------------------<Yoda>Use the Search page you must.Edited by - michaelp on 06/26/2002 22:51:09 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-26 : 23:27:19
|
| I do both.Put the constraint in there.Then only use a stored proc to insert records. Have it check the table before it inserts. If it doesn't find a match, then it inserts, otherwise it returns an error.Damian |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-06-26 : 23:36:56
|
| Both? What advantage do you get by doing that? I'm not pointing fingers, just trying to figure out the Pros and Cons.I did a quick mock-up test, and I think that just putting the unique contraint index thing on there works for me. BTW, thx for replying so late :)Michael--------------------<Yoda>Use the Search page you must. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-26 : 23:41:10
|
| It's daytime here. It's not late.There is no huge advantage to using both. I use stored procs for nearly all data access but the constraint will stop someone from getting at it in Query Analyzer or something like that.Damian |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-06-27 : 00:44:07
|
| Ahh I forgot you are on the other side of the planet..It sounds like in a production environment where there is no QA, that raising errors in Stored procs is the best solution.Easy to deploy,Easy to maintain,No index to create.I think I'll give that way a few tests. Thanks man!Michael<Yoda>Use the Search page you must. Find answer you will. |
|
|
|
Teroman
Posting Yak Master
115 Posts |
Posted - 2002-06-27 : 06:58:30
|
| Hi,I would definatley go for the index approach, and just make sure that you have propper error handling in your VB when you call the proc, let it fail if its a duplicate.The main reason is data integrity, what if someone writes another proc to insert Widgets and forgets the check, or more likely, someone does data manipluation in the backend with inserts and updates. You wont cath these problems without a unique index.I would also say that the index is also easier to maintain, as in you dont need to. Just create it and thats it.Finally, performance could be an issue. If you check for the values yourself you will do a table/index scan every time there is an insert, and if this is a busy table you could have a problem there.I guess there is also a conceptual argument for the index, data integrity should be handled in the data base, not the access tier, but this kind of goes back to my first point.Hope this helps, even if you disagree :)col |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-06-27 : 11:59:59
|
| Hmmm good point Teroman.I'm still testing / debating the best solution for my case.Thanks for the insights guys!Michael<Yoda>Use the Search page you must. Find answer you will. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-27 : 13:15:26
|
| I design with a data-centric approach in mind, and to that end I make sure the data layer is properly self-enforcing. If the widget name is to be unique, then you need a unique index. With regard to what ought to happen when some entity attempts to violate that uniqueness, you can either allow the constraint violation to occur and trap that or add a WHERE NOT EXISTS() clause to any INSERT statements you might have in stored procedures.Jonathan Boott, MCDBA |
|
|
|
|
Force Unique fields