A simple question for today: how does one go about preventing duplicate rows based on the value held by more than one column being inserted into an existing MySQL table?
Well the solution lies in the use of a unique compound key which is basically a way of specifing unique rows based on a value given by a combination of one or more columns.
So for example, if we said we wanted to prevent people sharing the same firstname, surname and title from being added to a persons table, we are in fact specifying a unique compound key to act upon the columns firstname, surname and title!
With this in mind, now adding this key constraint to a table is as simple as running:
ALTER TABLE people ADD UNIQUE KEY (firstname,surname,title)
You’ll note that running this SQL statement on a table containing data already breaking this new rule that we are imposing will fail, meaning that one will first manually have to remove all the offending data before running the ALTER statement again.
And that’s it, a pretty simple little tip for today’s CodeUnit SQL tutorial entry! :)
You might also enjoy:
-
Locating duplicate rows based on multiple column values with SQL turns out to be a fairly simple exercise. If we take the usual method for locating dupli ... -
There exists in this world a nice little SQL statement know as the SELECT INTO statement, one that works beautifully well in most database systems for when ... -
From day one, the problem with database-driven applications is of course that the potential for two separate scripts to overwrite data being used by one ano ... -
Just a quick note on how to permanently change your MySQL server's configuration to allow for a bigger max_allowed_packet limit, basically the variable that ... -
Annoyingly, I got saddled with a database for a system which contained a whole lot of "uncleaned data", in other words data with a lot of trailing and leadi ...
