File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

2 primary keys with mysql

 
Peter Primrose
Ranch Hand
Posts: 755
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 518
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 755
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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 ]
     
    It is sorta covered in the JavaRanch Style Guide.
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic