Re: Does it make sense to have 2 unique keys in one table?

In article <4569de$6j9@News1.mcs.net> "Burton M. Strauss III" <BStrauss@MCS.NET> writes: >>dchan@dragon.cpe.ulowell.edu (Empty) wrote: >>>if the columns are partially duplicated, like >>> >>>unique key1 unique key2 >>>column1 column3 >>>column2 column4 >>>column3 >>>column4 >>> >>>does this make sense? >> >> > >Creating 2 unique indexes on a table can make perfect sense depending on >your situation. The last company I worked for allowed certain processes >(from a client company) perform transactions against one of our tables, >and insert their own unique identifier upon (successful :-) completion. > >The client would then be allowed access to that information using their >own index structure/account id. > >Each installation has unique requirements - and if yours requires 2 >unique indexes to properly identify a record, so be it. > >Chris It seems to me that the point isn't whether both indexes are necessary. I think they must be, otherwise why would the question have been asked. It seems to me the question is whether both indexes need to be declared to be unique. Since key 2 is unique and since key1 is a superset of key2 then key1 must be unique. Although I am no expert in this area, it seems to me that you could gain some performance by not requiring key1 to be unique. Key2 being unique is going to insure that key1 is unique anyway so why waste time having the database check for something that is impossible. Ken Denny Insight Industries, Inc. All opinions expressed are my own.