|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Multiple column primary keyI'd like to know what is a good reason to use a primary key on more than one field. In a many-to-many relationship, where I have these tables : ITEMS item_id (pk) .... .... PROPS prop_id (pk) .... .... ITEMS_PROPS item_id prop_id For the table ITEMS_PROPS, is it a good idea to use a primary key on both fields? Or is it better not to have any primary key at all? Thanks a lot Definitely give the ITEMS_PROPS table a primary key and item_id, prop_id
would be a good choice as long as for any given item_id and prop_id pair, there is at most one row in the ITEMS_PROPS table. Tom Show quote "ibiza" <lambe***@gmail.com> wrote in message news:1154876785.418981.192170@m73g2000cwd.googlegroups.com... > Hi, > > I'd like to know what is a good reason to use a primary key on more > than one field. In a many-to-many relationship, where I have these > tables : > > ITEMS > item_id (pk) > ... > ... > > PROPS > prop_id (pk) > ... > ... > > ITEMS_PROPS > item_id > prop_id > > For the table ITEMS_PROPS, is it a good idea to use a primary key on > both fields? Or is it better not to have any primary key at all? > > Thanks a lot > ibiza (lambe***@gmail.com) writes:
> I'd like to know what is a good reason to use a primary key on more Whenever the table describes an entity that is identified by multiple> than one field. attributes. For instance, a table like OrderDetails will always have a two-column key, the order id and then either just a running number within the order, or something else like the product id. > ITEMS_PROPS It's not only a good idea, it's the only reasonable design.> item_id > prop_id > > For the table ITEMS_PROPS, is it a good idea to use a primary key on > both fields? > Or is it better not to have any primary key at all? Absolutely not!-- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx well thanks a lot :)
what about if I need a multiple-field primary key, but one of the field is varchar? is it better to create an identity field (int) and put the primary on that column or it is still better to take the int and varchar fields as primary key? thanks again! Erland Sommarskog wrote: Show quote > ibiza (lambe***@gmail.com) writes: > > I'd like to know what is a good reason to use a primary key on more > > than one field. > > Whenever the table describes an entity that is identified by multiple > attributes. For instance, a table like OrderDetails will always have > a two-column key, the order id and then either just a running number > within the order, or something else like the product id. > > > ITEMS_PROPS > > item_id > > prop_id > > > > For the table ITEMS_PROPS, is it a good idea to use a primary key on > > both fields? > > It's not only a good idea, it's the only reasonable design. > > > Or is it better not to have any primary key at all? > > Absolutely not! > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx It would be better to use the int and varchar columns as the primary key.
Tom Show quote "ibiza" <lambe***@gmail.com> wrote in message news:1154916496.605703.200710@m73g2000cwd.googlegroups.com... > well thanks a lot :) > > what about if I need a multiple-field primary key, but one of the field > is varchar? > > is it better to create an identity field (int) and put the primary on > that column or it is still better to take the int and varchar fields as > primary key? > > thanks again! > > Erland Sommarskog wrote: >> ibiza (lambe***@gmail.com) writes: >> > I'd like to know what is a good reason to use a primary key on more >> > than one field. >> >> Whenever the table describes an entity that is identified by multiple >> attributes. For instance, a table like OrderDetails will always have >> a two-column key, the order id and then either just a running number >> within the order, or something else like the product id. >> >> > ITEMS_PROPS >> > item_id >> > prop_id >> > >> > For the table ITEMS_PROPS, is it a good idea to use a primary key on >> > both fields? >> >> It's not only a good idea, it's the only reasonable design. >> >> > Or is it better not to have any primary key at all? >> >> Absolutely not! >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > hum, may I know the reason why? That's what interests me :P
Tom Cooper wrote: Show quote > It would be better to use the int and varchar columns as the primary key. > > Tom > > "ibiza" <lambe***@gmail.com> wrote in message > news:1154916496.605703.200710@m73g2000cwd.googlegroups.com... > > well thanks a lot :) > > > > what about if I need a multiple-field primary key, but one of the field > > is varchar? > > > > is it better to create an identity field (int) and put the primary on > > that column or it is still better to take the int and varchar fields as > > primary key? > > > > thanks again! > > > > Erland Sommarskog wrote: > >> ibiza (lambe***@gmail.com) writes: > >> > I'd like to know what is a good reason to use a primary key on more > >> > than one field. > >> > >> Whenever the table describes an entity that is identified by multiple > >> attributes. For instance, a table like OrderDetails will always have > >> a two-column key, the order id and then either just a running number > >> within the order, or something else like the product id. > >> > >> > ITEMS_PROPS > >> > item_id > >> > prop_id > >> > > >> > For the table ITEMS_PROPS, is it a good idea to use a primary key on > >> > both fields? > >> > >> It's not only a good idea, it's the only reasonable design. > >> > >> > Or is it better not to have any primary key at all? > >> > >> Absolutely not! > >> > >> > >> -- > >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >> > >> Books Online for SQL Server 2005 at > >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > >> Books Online for SQL Server 2000 at > >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > A better question is why would you want to use an identity column as the
primary key in this case (that is, one where you are implmenting a linking table)? SQL Server is perfectly capable of effciently handling composite keys. If you add an identity, you are making the table bigger for no purpose that I can see. Then, since it's your primary key, you will now have an index on the identity column which makes your table even bigger. You will still need indexes on the two columns which are primary keys to the other tables since you will need to be able to access this table by the primary key of either of the two tables it is linking. So I can't see any reason you would want an identity on this table. These two columns are definitely the natural key of this table. Some people on this list who are famous and written many books would tell you that you should ALWAYS use a natural key and should NEVER use an identity column as the primary key. Other equally famous people who have also written many books would say that there are times when you should create an identity column and make it the primary key. But I don't think anyone who would argue that you should create an identity column for a simple linking table like this case. If you are talking about a more general case, where you have a natural key and you want to know when you should use it and when you should create an identity column or some other surrogate key, that's a more difficult question with a less clear answer. A good place to read up on this is http://www.aspfaq.com/show.asp?id=2504 HTH Tom Show quote "ibiza" <lambe***@gmail.com> wrote in message news:1154926178.161025.93860@h48g2000cwc.googlegroups.com... > hum, may I know the reason why? That's what interests me :P > > Tom Cooper wrote: >> It would be better to use the int and varchar columns as the primary key. >> >> Tom >> >> "ibiza" <lambe***@gmail.com> wrote in message >> news:1154916496.605703.200710@m73g2000cwd.googlegroups.com... >> > well thanks a lot :) >> > >> > what about if I need a multiple-field primary key, but one of the field >> > is varchar? >> > >> > is it better to create an identity field (int) and put the primary on >> > that column or it is still better to take the int and varchar fields as >> > primary key? >> > >> > thanks again! >> > >> > Erland Sommarskog wrote: >> >> ibiza (lambe***@gmail.com) writes: >> >> > I'd like to know what is a good reason to use a primary key on more >> >> > than one field. >> >> >> >> Whenever the table describes an entity that is identified by multiple >> >> attributes. For instance, a table like OrderDetails will always have >> >> a two-column key, the order id and then either just a running number >> >> within the order, or something else like the product id. >> >> >> >> > ITEMS_PROPS >> >> > item_id >> >> > prop_id >> >> > >> >> > For the table ITEMS_PROPS, is it a good idea to use a primary key on >> >> > both fields? >> >> >> >> It's not only a good idea, it's the only reasonable design. >> >> >> >> > Or is it better not to have any primary key at all? >> >> >> >> Absolutely not! >> >> >> >> >> >> -- >> >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> >> >> Books Online for SQL Server 2005 at >> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> >> Books Online for SQL Server 2000 at >> >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> > > ibiza (lambe***@gmail.com) writes:
> well thanks a lot :) Same answer. There may be situations where it makes sense to supplment> > what about if I need a multiple-field primary key, but one of the field > is varchar? > > is it better to create an identity field (int) and put the primary on > that column or it is still better to take the int and varchar fields as > primary key? a multi-column key with a suurrogate, but you may also find that this buys you lot of extra problems. And, by the way, there is no reason why a surrogate key has to be an IDENTITY colunm. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Ok, many thanks to both of you.
The thing that made me thought it may have been better to use an identity primary key was the varchar issue. I thought it would be quicker than a primary key with a varchar column. As I see, it doesn't change anything? thanks! :) Erland Sommarskog wrote: Show quote > ibiza (lambe***@gmail.com) writes: > > well thanks a lot :) > > > > what about if I need a multiple-field primary key, but one of the field > > is varchar? > > > > is it better to create an identity field (int) and put the primary on > > that column or it is still better to take the int and varchar fields as > > primary key? > > Same answer. There may be situations where it makes sense to supplment > a multi-column key with a suurrogate, but you may also find that this buys > you lot of extra problems. > > And, by the way, there is no reason why a surrogate key has to be an > IDENTITY colunm. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Yes, I have the same confusion/question... if you had a primary key
which included a varchar, nvarchar, char or text type field, wouldn't it be less efficient than a surrogate that is of an integer type? Isn't SQL Server much faster at retrieving from indexes on numbers than on strings? And the storage space needed to store an integer in an index is much less than that needed to store strings, which would have an impact on index sizes-- with millions of rows in a table that are, say, char(30) or varchar(100), it would be a huge impact. I learned years ago (and it may be different now) that SQL Server is terrible at handling string or string manipulation and that you should try to avoid that necessity at all costs. Has that changed? If so, how? What is the latest "rule of thumb"? ibiza wrote: Show quote > Ok, many thanks to both of you. > > The thing that made me thought it may have been better to use an > identity primary key was the varchar issue. I thought it would be > quicker than a primary key with a varchar column. > Mme Glitchbane (fortuk***@hotmail.com) writes:
> Yes, I have the same confusion/question... if you had a primary key Indeed a char(30) is not something I would like to have a PK on. But> which included a varchar, nvarchar, char or text type field, wouldn't > it be less efficient than a surrogate that is of an integer type? > Isn't SQL Server much faster at retrieving from indexes on numbers than > on strings? And the storage space needed to store an integer in an > index is much less than that needed to store strings, which would have > an impact on index sizes-- with millions of rows in a table that are, > say, char(30) or varchar(100), it would be a huge impact. a char(3) is no problem. > I learned years ago (and it may be different now) that SQL Server is For really complex string massaging a client language or a .Net stored> terrible at handling string or string manipulation and that you should > try to avoid that necessity at all costs. Has that changed? If so, > how? What is the latest "rule of thumb"? procedure is better. But T-SQL has acquired some more string function over the years. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Mme Glitchbane wrote:
> Yes, I have the same confusion/question... if you had a primary key How will you prevent duplicates on the natural key columns without> which included a varchar, nvarchar, char or text type field, wouldn't > it be less efficient than a surrogate that is of an integer type? > Isn't SQL Server much faster at retrieving from indexes on numbers than > on strings? And the storage space needed to store an integer in an > index is much less than that needed to store strings, which would have > an impact on index sizes-- with millions of rows in a table that are, > say, char(30) or varchar(100), it would be a huge impact. > using an index? A surrogate won't do that for you. So if you add a surrogate you'll need TWO indexes to support the same requirement. Therefore the one with a surrogate is very likely to require MORE storage rather than less. A surrogate key or any other key only impacts performance when it is referenced somewhere (by a foreign key or by a query or update). So to make a performance comparison between two sets of indexes on a single table is meaningless. There is no basis for saying that one is "faster" than the other. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- ibiza (lambe***@gmail.com) writes:
> Ok, many thanks to both of you. It depends a little on how long the varchar column is. But as I understood> > The thing that made me thought it may have been better to use an > identity primary key was the varchar issue. I thought it would be > quicker than a primary key with a varchar column. > > As I see, it doesn't change anything? it, your question was about the table to cater for the many-to-many relationship. The place to introduce a surrogate would be in the base table. Generally, you would not have a primary key on any longer text string like customer name or email address. In this case it's better to have a surrogate, in my opinion. You keep down key length, and updating the name or email address is much easier. But you could have a character column which is a short code, or a few characters long, for instance a currency code like SEK or USD. In this case there is no need for a surrogate key. Now, if you use a Windows collation or use nvarchar/nchar there is an overhead for applying the Unicode rules for comparison. This can be circumvented by specifying a binary collation. But for plain comparison the overhead is moderate, and the extra complexity by involving multiple collations may not be worth it. The foremost thing you should concentrate on when designing the database is to get the functional aspect of it right. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics