With such an impressive number of programs using MySQL as the underlying persistence mechanism, it seems that the development of database-driven applications is a fairly straightforward process that can be tackled with little or no hassles and having only a minimal background in database architecture design.
While this concept can be applied to relatively simple cases, the truth is that the process can be pretty challenging (and eventually daunting) when it comes to persisting and maintaining large amounts of data whose integrity and consistency must be preserved in the most extreme conditions. In such situations, not only is it necessary to deal with the intrinsic complexities exposed by the database schema itself, but it’s mandatory to implement mechanisms that ensure the integrity will be maintained over time, regardless of the operations performed by the application(s) that interact with the storage layer.
Of course, there are many factors that can affect (unintentionally or deliberately) the integrity of persisted data, which are way too extensive for being covered in a single tutorial (you can buy a book from the many available out there and dig deeper into the topic). However, a common issue that all database-driven programs must address is record duplication. Even in trivial and banal cases, like a personal blog database, record duplication can be a real nightmare that can end up bringing the efficiency and reliability of an application to its knees.
The good news is that preventing your MySQL database from suffering the side effects of record duplication isn’t as complicated and intimidating as it sounds. What’s more, the problem can be tackled both at application and database-level, with each approach owning its own pros and cons. However, the latter gives you more independency, as the solution isn’t strictly coupled to the language on which the application is seated upon.
In the lines to come I’ll be discussing a few straightforward approaches that you can use, in order to maintain your valuable data.
Having said that, now it’s time to get rid of the preliminaries and start looking into the first two methods that you can utilize for preventing record duplication: the “PRIMARY KEY” clause and the “INSERT IGNORE” SQL statement. Let’s get started!
Taking the first step: creating a sample MySQL table
Since my intention here is to showcase some simple - yet effective - methods that can be used for avoiding duplication of records in MySQL databases, the first logical step that must be taken is to create a sample table that permits us to demonstrate the efficiency of the methods in question.
To suit this basic requirement, below I defined a testing table called “users”, which has the following structure. Check it out:
As you can see, the above table is comprised of only three columns, which store the first and last name of some users, and their email addresses as well. In addition, you may have noticed that the table uses the MyISAM database engine; if you feel more comfortable with other engines like “InnoDB” (which supports transactions), feel free to amend the previous code sample to fit your needs.
So far, I managed to create this simple table, which in its current state is pretty useless, as it doesn’t contain any meaningful data. Thus, it’s time to fix this issue and add to it some fictional users. The following code bit does exactly that:
INSERT INTO users (first_name, last_name, email) VALUES ('Sandra', 'Smith', 'sandra@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Daniel', 'Norton', 'daniel@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Susan', 'Wilson', 'susan@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Jenny', 'Adams', 'jenny@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Thomas', 'Everson', 'thomas@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Mary', 'Brown', 'mary@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Julie', 'Owen', 'julie@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Matthew', 'Burke', 'matthew@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Alfred', 'Wayne', 'alfred@domain.com'); INSERT INTO users (first_name, last_name, email) VALUES ('Margaret', 'White', 'margaret@domain.com');
At this point things are starting to look a bit more interesting, as our “users” table now has been populated with some sample data. Unfortunately, not all is so pleasant and delightful in this seemingly harmless scenario, as if for some unknown reason the previous set of statements are executed again, each row corresponding to every fictional user will be effectively duplicated.
As noted in the introduction, preventing this from happening is much easier than you might think. In the following segment I’ll be altering the definition of the “users” table, which will make use of the “PRIMARY KEY” clause to avoid any possible record duplication.
