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?
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.
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)
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.
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.
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=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.