Home arrow MySQL arrow Avoiding Duplicate Records in MySQL

Avoiding Duplicate Records in MySQL

In this MySQL database tutorial, you will be learning how to avoid duplicate records using "Primary Key" and the "Insert Ignore" SQL statement.

TABLE OF CONTENTS:
  1. Avoiding Duplicate Records in MySQL
  2. MySQL Primary Keys and Insert Ignore Statement
By: Alejandro Gervasio
Rating: starstarstarstarstar / 0
July 05, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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:

DROP TABLE IF EXISTS `test`.`users`;
CREATE TABLE  `test`.`users` (
  `first_name` varchar(45) default NULL,
  `last_name` varchar(45) default NULL,
  `email` varchar(45) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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.



 
 
>>> More MySQL Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: