MYSQL query question

I have a varchar field in table Table1 called pid that is set to primary key.  There is supposed to not be any duplicates here.

what syntax do I use to:

1.)  find and list any duplicates
2.)  is there a field type (int etc.) that will throw an error if you try to insert a duplicate #?
pda4meAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
knightEknightConnect With a Mentor Commented:
IMO yes, int would be better, assuming there are no values bigger than 2 billion -- in which case use bigint instead.
0
 
käµfm³d 👽Commented:
Because you are using a varchar, keep in mind that the DB will not look solely at the numbers (or letters) to determine duplicity. Just a single strategic space can make the difference between two keys. For example, the following are all different values:

123456
123 456
 123456(leading space)
123456 (trailing space)

Open in new window

0
 
knightEknightCommented:
If there is a primary key constraint on the field, there won't be any duplicates.  Otherwise, this query will find the dupes:

select pid
from Table1
group by pid
having count(*)>1
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
pda4meAuthor Commented:
ah, makes sense...would int be a better choice?  the values are strictly numerical...
0
 
käµfm³d 👽Connect With a Mentor Commented:
would int be a better choice
It would probably be the easiest to implement. If you *really* wanted (or had a business reason) to keep the varchar, then you could probably write an update/insert trigger, but I think changing the type would be a lot simpler.
0
 
pda4meAuthor Commented:
Thanks!  Awesome help!
0
 
käµfm³d 👽Commented:
NP. Glad to help  = )
0
All Courses

From novice to tech pro — start learning today.