This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes 2 primary keys with mysql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "2 primary keys with mysql" Watch "2 primary keys with mysql" New topic
Author

2 primary keys with mysql

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi guys,

I have a table with the following columns:

1. Id (auto-inc) and primary
2. Email - primary
3. Password

(When looking at the table i can see 2 keys symbolizing the primary keys)
Anyway, how come, when I INSERT 2 equal emails - the system write it. Doesn�t it suppose to throw an exception? I'm trying to have unique emails in the table.

Any suggestion how to correct it?
Thanks
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
From your description, I believe that you have one primary key consisting of two columns: id + email. As defined, those two columns together must be unique.

But the id will be unique because of the auto-incrementing nature of the column. So regardless of the value of the email, the primary key is unique.

I suggest removing email from the primary key and creating a separate unique constraint on email.
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Got it!

Questions:
1. is it safe to remove the primary key on the id (since it�s auto increment)
2. Say I have a table with 3 columns (a,b,c) and i wish that every column will be unique - how should I do that.

Thanks again
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


1. is it safe to remove the primary key on the id (since it�s auto increment)

No. Your entity needs a primary key, otherwise it is not relational data. You can't guarentee any referential integrity without a primary key. MySQL can be a bit slack in tolerating this sort of design, but you shouldn't if you want to avoid possible problems down the line.

As a side note, Scott's recommendation of removing the email address from your composite key is a good one. The definition of a primary key is:
  • It is unique
  • It is not null
  • It is unchanging

  • I've highlighted the last rule because it suggests a problem with using an email address as part of a primary key: will your users never change their email address?


    Say I have a table with 3 columns (a,b,c) and i wish that every column will be unique - how should I do that


    You can use unique constraints. Check the documentation.
    [ October 02, 2006: Message edited by: Paul Sturrock ]

    JavaRanch FAQ HowToAskQuestionsOnJavaRanch
     
    jQuery in Action, 2nd edition
     
    subject: 2 primary keys with mysql
     
    Similar Threads
    To identify Primary Keys in a table
    SEVERE: Duplicate entry '1' for key 'PRIMARY'
    How to implement OneToOne entity without explicit id field
    retriving two primary keys
    Compound map doubt