Home » RDBMS Server » Performance Tuning » Creating PK in Each table
Creating PK in Each table [message #201715] Mon, 06 November 2006 08:28 Go to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
Hi Friends,

If i create Primary Key in each table, it will be useful for future modification or maintainence.

because in one project i have seen primary key for all the tables.that project DB designer told me that
it will be more useful for future enhancements....

kindly i need answer from all experts

thanx and regards
Selva
Re: Creating PK in Each table [message #201723 is a reply to message #201715] Mon, 06 November 2006 09:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
They're more than useful - if you don't have Primary and Foreign key constraints, you don't have a relational database - all you've got is a collection of data and a fond belief that it is in some way related.

Yes. You need Primary key constraints, and you also need Foreign key constraints.
Re: Creating PK in Each table [message #201819 is a reply to message #201723] Mon, 06 November 2006 22:10 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
Thanks a lot man
Re: Creating PK in Each table [message #202029 is a reply to message #201819] Tue, 07 November 2006 22:39 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
consider the following table:

User_id User_name password

In this case i can use USERNAME as a pk,
b coz username will be unique and not null.

So no need to create user_id right...?

In this kind of situation, what i have to do ....

or i have to keep Some ID as a PK in this table....?

Regards
Selva
Re: Creating PK in Each table [message #202137 is a reply to message #202029] Wed, 08 November 2006 06:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But, suppose user 'Amanda Smith' gets married and changes her name to 'Amanda Jones'?

If you have user_id column as the primary key, then you can just update the user_name and there are no problems.
If you are using user_name as the PK, then you need to either create a new user (in which case you need to record that these are the same person) or you need to update every place that has a FK link to this PK.

I'd use a user_id.
Re: Creating PK in Each table [message #202142 is a reply to message #202137] Wed, 08 November 2006 06:31 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
ok,

Suppose in yahoo, we are not using user_id as a login.

more over we cant change user_name ,

If u want to change user_name u have to create another user_name only, we can change the password .

in this case what u will do.....? u will keep user_id as a PK or User_Name as a PK.....?

thanx and regards
Selva
Re: Creating PK in Each table [message #202215 is a reply to message #202142] Wed, 08 November 2006 14:58 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd use USER_ID.

Besides, I don't think the idea of NOT allowing passwords to be changed is a good one. Longer it is in use, better are the chances that someone, someday, will crack it. Not necessarily in malicious way, but nevertheless.
Re: Creating PK in Each table [message #202327 is a reply to message #202142] Thu, 09 November 2006 02:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'll have a User_id column as PK
Using fields that store information as primary keys is a generally bad idea.
"Users can't change their usernames" is just a business rule, and can be changed by the business.

My way - if it changes then there's no real problems.
Your way - if it changes then you've got an awful lot of work ahead of you.

Who is this 'u' bloke anyway?
Previous Topic: help need in optimizing a query....
Next Topic: Perfomace issues on complex query
Goto Forum:
  


Current Time: Sun Dec 04 04:12:40 CST 2016

Total time taken to generate the page: 0.28031 seconds