Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mutiple primary keys in a table

 
Harish Shivaraj
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi all.

Im not sure if im doing this right. But i wanted to clarify. I have a table USER and i have column called ID which is configured as primary key. My intention is that every user would have a username (which must be unique). And therefore I was wonder if I should have another column called username and make that as primary key rather than ID column. Since when im querying I would be looking for username rather than the ID?

Would do you suggest leaving ID as a primary key? I mean isnt the concept of PRIMARY key to be for searching purpose?

I just wanted to make sure if I understand the concept of primary key right?

Thanks very much all.

Harish
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not necessarily. Primary key is an value that doesn't change. By making the username the primary key, you are making it hard for users to change their username.

Note you can have a unique index on a column that isn't the primary key. That makes lookups fast too.
 
Ivan Jozsef Balazs
Rancher
Posts: 990
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In many programming shops it is customary that tables have a syntetic primary key column, autoincrement or with values taken from a sequence, in order to have a really constant ID value.
Fields coming from the problem space (like code, name etc.) can be used for indices, but the real primary key is a technical column.
 
Bernard Lewis
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Primary key is used to uniquely identify a row in a table and it is also used to extract the records from a table and the  primary key cannot have duplicate values. If you will try to add duplicate values in the primary key then you will get the error "primary key cannot have duplicate values". If you want to set the name attribute as the primary key then you need to add different names in each column.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic