• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to make the MySQL db to avoid duplicate entry?

 
Namitha karupaiya
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there any way to make the MySQL database aumatically reject duplicate entry in a column other than Primary Key column?

Thank you very much.
 
Renato Losio
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, using unique constrain.

Make a unique column so you get no dupplicates.

alter table yourTable add unique yourColumn;

You may have one primary key but many unique columns...

Cheers,

Renato
 
Namitha karupaiya
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much Renato Losio !

My requirement is to store email addresses in a column and what you have stated seems a great idea.

Now, say, for example the following email address is already in the database column:
someone@example.com

Will the unique column resist the following entry:
SOMEONE@EXAMPLE.COM

If not are there any feature built in the database to achieve this?

Thank you very much.
 
Renato Losio
Ranch Hand
Posts: 99
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Generally speaking, no.

But you may always store the email as lowercase (or uppercase) and the unique constraint will work.

I mean if you have the column email, change the:

email = 'test@email.com'

to something like:

email = upper('test@email.com')

Cheers,

Renato
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic