mutiple primary keys in a table

[Harish Shivaraj]

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]

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]

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]

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.