Re: duplicate values for primary keys

[Chris Bassett]

OK, well here's another question, then (it deals with primary keys):

if I create something like this:

Create Table User (
username varchar(16) not null,
password varchar(32) not null,
birthdate date not null,
email_addr varchar(100) not null,
status int not null,
primary key (username, email_addr));

I've noticed that even if I try to insert the following rows of data:
"me","somepassword",'2010-01-01',"[e-mail]",1

and...
"me","mypassword",'2010-03-01',"[e-mail]",1

and...
"johndoe","doepassword",'2010-02-01',"[e-mail]",1

that Mysql will accept all three, even though there is already a record that was added in the first line with the username "me" and the second record already references an email address "[e-mail]" so supposedly the third line would be not be added either.

But I've noticed that MySQL will add it anyway, even if one of the primary key fields is different. Is there away to have it so that if the USERNAME OR the EMAIL_ADDR exists, don't add the record? Because from what I can see, it will only refuse to add the record if only the USERNAME AND EMAIL_ADDR exist, and this is not how I want it.

Suggestions for this?