# How to Prevent Duplicate Records on my MySQL table? **URL:** \ **Category:** Databases\ **Created:** [June 30, 2015, 1:17am UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228 "2015-06-30T01:17:52Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Gerardo\_Garca](https://forum.xojo.com/letter_avatar_proxy/v4/letter/g/e99b99/32.png) [@Gerardo\_Garca](https://forum.xojo.com/u/Gerardo_Garca)\ **Post date:** [June 30, 2015, 1:17am UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/1 "2015-06-30T01:17:52Z")
Hi all! How can I prevent Duplicate records on my MySQL Table? I’m Using a databaserecord filled with 20 columns, that I Record onto the database using InsertRecord. App.mDb.InsertRecord (“facturas\_recibidas”, dr) Where “Facturas\_recibidas” is the name of the table, and dr is the name of my Recordset. So, I’ve heard that MySQL has the IF NOT EXIST or INSERT ON DUPLICATE KEY, but how can I do it on my Insert Record? ---
**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\ **Post date:** [June 30, 2015, 2:08am UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/2 "2015-06-30T02:08:21Z")
You cannot use IF NOT EXISTS or the equivalent with InsertRecord. Here are some other options to try: - Put a UNIQUE CONSTRAINT index on the column so that a DB error is raised when you call InsertRecord with duplicate (not unique) data. - Create the SQL manually with the specific syntax you need (IF NOT EXISTS?) and send it using SQLExecute. ---
**Author:** ![Daniel\_Taylor](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/2bfe46/32.png) [@Daniel\_Taylor](https://forum.xojo.com/u/Daniel_Taylor)\ **Post date:** [June 30, 2015, 2:10am UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/3 "2015-06-30T02:10:29Z")
Generally you decide which column or columns have to be unique and create a unique index for them in MySQL. Then if you try to insert a duplicate it will generate an error. When writing your own SQL you can use various commands to ignore the error or to do something different if there’s a duplicate. Using Xojo’s object you would check the database for an error (Database.Error; Database.ErrorCode; and Database.ErrorMessage) and take appropriate action in the case of a duplicate. Depending on your app this might mean branching to code to update the existing record, or maybe doing nothing at all. ---
**Author:** ![Daniel\_Taylor](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/2bfe46/32.png) [@Daniel\_Taylor](https://forum.xojo.com/u/Daniel_Taylor)\ **Post date:** [June 30, 2015, 2:10am UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/4 "2015-06-30T02:10:43Z")
Ah…Paul beat me to it. ---
**Author:** ![Gerardo\_Garca](https://forum.xojo.com/letter_avatar_proxy/v4/letter/g/e99b99/32.png) [@Gerardo\_Garca](https://forum.xojo.com/u/Gerardo_Garca)\ **Post date:** [June 30, 2015, 3:52am UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/5 "2015-06-30T03:52:39Z")
Ooooooohh very much to all!! Which kind of type would have in this Unique column? It got 39 characters including numbers, letters and hyphens ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [June 30, 2015, 4:37am UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/6 "2015-06-30T04:37:28Z")
Actually use of NOT EXISTS can be used with INSERT ```auto INSERT INTO table2 VALUES(field1,field2) SELECT data1,data2 FROM table1 a WHERE NOT EXISTS(SELECT 8 FROM table2 b WHERE a.data1=b.field1 and a.data2=b.field2) ``` ---
**Author:** ![Beatrix\_Willius](https://forum.xojo.com/user_avatar/forum.xojo.com/beatrix_willius/32/282_2.png) [@Beatrix\_Willius](https://forum.xojo.com/u/Beatrix_Willius)\ **Post date:** [June 30, 2015, 6:05am UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/7 "2015-06-30T06:05:01Z")
I’m using Valentina so YMMV. But inserting data with a unique constraint was rather slow for Valentina. First I checked with SQL if the record was there or not. Then I did unique constraint, which raised an exception. This was way slower. Valentina allows direct database access without SQL, which was faster. This single simple change made a 10% speed increase in a complex parsing and database writing algorithm. ---
**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\ **Post date:** [June 30, 2015, 3:52pm UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/8 "2015-06-30T15:52:16Z")
> [@197809:@Gerardo García](#): > > Ooooooohh very much to all!! Which kind of type would have in this Unique column? The type is not relevant. You add a UNIQUE INDEX to the column with something like this: `ALTER TABLE facturas_recibida ADD UNIQUE INDEX ui_column (column_name);` ---
**Author:** ![Louis\_D](https://forum.xojo.com/letter_avatar_proxy/v4/letter/l/cab0a1/32.png) [@Louis\_D](https://forum.xojo.com/u/Louis_D)\ **Post date:** [June 30, 2015, 5:24pm UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/9 "2015-06-30T17:24:26Z")
A unique index can be a single column, or several columns. In the case of invoices, the typical header key is the document number. Invoice items would have the key document number + item number (I mean line, not material) It varies for every table and is a function of what must be unique for the given object. For example, a Material Master file could be separated in several tables. One table for the general data and one table for specific plant data (inventory valuation method, reorder point , warehousing parameters, etc.). The key for the general table would be the material number, while the key for plant specific data would be the material number and the plant number. > [@](#): > > It got 39 characters including numbers, letters and hyphens This assertion suggests that you are stuffing a lot of data in a single field. You should consider having a column for each data element. I strongly suggest that you read a book on SQL and relational databases. ---
**Author:** ![Daniel\_Taylor](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/2bfe46/32.png) [@Daniel\_Taylor](https://forum.xojo.com/u/Daniel_Taylor)\ **Post date:** [June 30, 2015, 6:48pm UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/10 "2015-06-30T18:48:35Z")
[quote=197906:@Louis Desjardins] It got 39 characters including numbers, letters and hyphens This assertion suggests that you are stuffing a lot of data in a single field.[/quote] It could be a completely valid, single ID or GUID. ---
**Author:** ![Louis\_D](https://forum.xojo.com/letter_avatar_proxy/v4/letter/l/cab0a1/32.png) [@Louis\_D](https://forum.xojo.com/u/Louis_D)\ **Post date:** [June 30, 2015, 7:11pm UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/11 "2015-06-30T19:11:18Z")
Fair comment . It is clearly an assumption on my part, not a fact. That is what I meant with the use of “suggests”. No certainty, just an assumption. ---
**Author:** ![Daniel\_Taylor](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/2bfe46/32.png) [@Daniel\_Taylor](https://forum.xojo.com/u/Daniel_Taylor)\ **Post date:** [June 30, 2015, 7:30pm UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/12 "2015-06-30T19:30:03Z")
Just didn’t want Gerardo to split a GUID into 4 or 5 columns 😉 If he’s merging disparate pieces of info though then your comment is spot on. Even if the goal is a “tag” to identify a unique row, it’s better to put each piece in its own column and setup the unique index that way. ---
**Author:** ![Gerardo\_Garca](https://forum.xojo.com/letter_avatar_proxy/v4/letter/g/e99b99/32.png) [@Gerardo\_Garca](https://forum.xojo.com/u/Gerardo_Garca)\ **Post date:** [July 1, 2015, 4:47pm UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/13 "2015-07-01T16:47:39Z")
[quote=197927:@Daniel Taylor]Just didn’t want Gerardo to split a GUID into 4 or 5 columns 😉 If he’s merging disparate pieces of info though then your comment is spot on. Even if the goal is a “tag” to identify a unique row, it’s better to put each piece in its own column and setup the unique index that way.[/quote] Exactly, I don’t Want to split the UUID, because it the UNIQUE Index of the Database. This is the Name of the invoice that would be unique. I choose this Kind of Data Type: VARCHAR(36). This is an example of this UUID: A6019081-79F8-48A4-8511-7B32AF684041 Now I’m gonna test to Handle the Error when a Repeated value happens. Regards ---
**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\ **Post date:** [October 29, 2020, 5:49pm UTC](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228/14 "2020-10-29T17:49:37Z")