How to Prevent Duplicate Records on my MySQL table?

Databases
1

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?

2

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.
3

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.

4

Ah…Paul beat me to it.

5

Ooooooohh very much to all!! Which kind of type would have in this Unique column?

It got 39 characters including numbers, letters and hyphens

6

Actually use of NOT EXISTS can be used with INSERT

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)
7

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.

8

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);

9

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.

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.

10

[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.

11

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.

12

Just didn’t want Gerardo to split a GUID into 4 or 5 columns :wink:

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.

13

[quote=197927:@Daniel Taylor]Just didn’t want Gerardo to split a GUID into 4 or 5 columns :wink:

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