SQL: Add a Unique Compound Key to an Existing MySQL Table CodeUnit 21 JUN 2010

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

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. I don't have time for myself any more.