0

I have a simple MYSQL insert query:

INSERT INTO table('col1','col2','col3') VALUES('123','456','some text')

I want to insert only if the above combination of columns does not already exist and this case is only when col1 has value of 123 and col3 has values of some text

I will try to explain with some examples of what is allowed and what not:

('123','456','some text') allowed ONLY if not exists

('123','847,'some text') allowed ONLY if not exists

Both results above can coexists together because col2 is different. However, each of the above results can exist only once

The following ones can be duplicate because they don't have the combination of col1 and col3 of the values 123 and some text respectively

('412','847','some text') allowed no matter what even if exists

('763','372','any text') allowed no matter what even if exists

To summarize, a combination of col1,col2,col3 should be unique ONLY when col1 has value 123 and col3 has value some text

The primary key of the table is an auto_increment id.

Can this be achieved by setting certain indexes on the table or only through subqueries or what are the solutions available here to achieve this?

3

MYSQL doesn't support such types of constraints. You should use stored procedure for inserting data. So you can do some checking and validation data.

| improve this answer | |
  • I believe they are called check constraints. Such checking could also be done in source code (PHP, Ruby, etc). +1 – RolandoMySQLDBA Nov 10 '14 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.