Page 1 of 1

insering data with existing primary key

Posted: Tue Sep 08, 2009 8:26 am
by delhiris
What will happen if user insert data with same primary key?
Will it thhrow error ?
how can i solve this?

Re: insering data with existing primary key

Posted: Tue Sep 08, 2009 8:33 am
by AlanG
It will return an error. You solve it by assigning a new unique primary key to the new data. A primary key is a unique index used to identify a row. It CANNOT be duplicated and it cannot be set to NULL, and there is no way around this.

(Note: I can only vouch for MySQL and Oracle as they are the only database systems I am familiar with.)

Re: insering data with existing primary key

Posted: Tue Sep 08, 2009 8:52 am
by onion2k
Users shouldn't be choosing primary keys.

Re: insering data with existing primary key

Posted: Tue Sep 08, 2009 9:05 am
by AlanG
onion2k wrote:Users shouldn't be choosing primary keys.
Assuming your using an id, no harm in using a string as a primary key. For example, what about a username for a users table. The username would work perfectly as a primary key, and would remove the overhead of having an id.

Re: insering data with existing primary key

Posted: Tue Sep 08, 2009 10:24 am
by onion2k
AlanG wrote:
onion2k wrote:Users shouldn't be choosing primary keys.
Assuming your using an id, no harm in using a string as a primary key. For example, what about a username for a users table. The username would work perfectly as a primary key, and would remove the overhead of having an id.
And would break everything if the user wanted to change their username. :)

I understand what you mean though. Natural language primary keys are a good idea in some, limited circumstances. They should be restricted to cases where they won't change though (ISO language codes for example). You can't guarantee that to the case if the user is choosing something.

Re: insering data with existing primary key

Posted: Tue Sep 08, 2009 10:54 am
by AlanG
onion2k wrote:And would break everything if the user wanted to change their username. :)
Hmm would it? If it did I would put that down to bad development or database design. Personally I use an id all the time. It makes it easier when passing it around such as in urls and such, no need to worry about url encoding it. :) I was just making a point though. :)

Actually, just for my own curiosity, can you give me one example where an id change would "break" the system? Other than hard coding id's into the code, which is just plain silly.