Reputation: 3033
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.
Upvotes: 5
Views: 5361
Reputation: 12656
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.
Upvotes: 10
Reputation: 10819
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:
Upvotes: 0
Reputation: 14449
You want to use the MySQL CONSTRAINT
syntax when creating your table (or alter an existing table).
Upvotes: 0