Two column primary key in MySQL

Question

I have a very simple problem and a solution that will work, but I'm looking for a simpler one.

I'd like to prevent rows from being added to a database when multiple values equal existing values. For example, if a2=a1 AND b2=b1 then the data gets rejected. If only a2=a1 or only b2=b1 it is allowed. Basically I want it to act like a primary key that requires both values to match.

I know I can do this through validation before I write to the database--ex. select * where a='a2' and b='b2' and then avoid writing new data when I get results. What I'd rather do is enable a MySQL setting, like primary key, that will block data like this automatically.

I'm not very familiar with MySQL outside of basic features, so apologies if this is trivially easy. Googling for all sorts of things around unique index and primary key did not help.

Thank!

Answer 1

Create a unique index for the columns that you want to be unique.

Eg: CREATE UNIQUE INDEX index_name ON table(col1, col2);

See the MySql Create index manual.

Answer 2

You want to use the MySQL CONSTRAINT syntax when creating your table (or alter an existing table).

Answer 3

In addition to what gcores says, I'd like to add that your alternate suggestion, of validation in the application layer, is a bad thing to do. What happens if two applications are trying to insert the same data, and they're interleaved? They may both check, see the data isn't there, and then both insert. If you use a unique index, there are two benefits:

• The user doesn't have to check if the value exists anymore. They simply try the operation, and if it fails, that means the value already existed. This reduces the load on your database slightly, and makes the application logic simpler.
• The database will always be valid, because it is enforcing rules itself. It's a lot easier to work with a database when you can reason about hard-fast rules, rather than the crapshoot of "we expect there will be no duplicates, but it's technicall possible..."