[Resolved] How to prevent the build up of identical (or similar) records

Davidc316

Does anyone know the raw SQL commands for preventing identical records from being reentered into a Mysql database???

Thanks.

(I've checked these forums and the Mysql manual but have had no luck)

ahundiak

CREATE UNIQUE INDEX index_name ON table_name(col_name1,col_name2...);

This prevents the table from having more than one record for each unique combination of col_nameX's.

Davidc316

Ahaaaaaaaaaa!

That could well be the answer I've been looking for!

But before I hit the resolved button, can I just ask for a little clarification about what that piece of code does:

So, if I understand it right- that piece of code you gave can be set to compare two column values (say "Name" and "Email_address") and IF someone tries to add a record which contains those same two values, then the database won't allow the record to be added. Have I got that right?

ahundiak

That is correct. It also prevents updating an existing record in such a way that it becomes a duplicate.

A specific error code is returned in mysl_errno() when an attempt to make a duplicate record is made. I forget what the exact code is but you can detect it and notify the user accordingly.

There are some restrictions on what can be indexed so you might want to review the mysql manual on indexing.

Davidc316

Thank you very much indeed!

I could not have hoped for a better response to my post.

You're a credit to this forum!

PFerrara

Alright, while we're on the subject of duplicates, I have a question.
I have a database full of data (go figure) but the data I used had some duplicates in it, now, I inserted everything in fine, and want to get rid of the dupes. The only problem is, the fields that can't be dupes, are text fields, so I can't index them. Any ideas?

TheIceman5

Originally posted by PFerrara
Alright, while we're on the subject of duplicates, I have a question.
I have a database full of data (go figure) but the data I used had some duplicates in it, now, I inserted everything in fine, and want to get rid of the dupes. The only problem is, the fields that can't be dupes, are text fields, so I can't index them. Any ideas?

i suggest you post your own thread as this will never get answered while it in here n00b.