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.
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.
Joined: Sep 10, 2004
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.
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 ]