Insert record only if a combination of column values doesn't exist

Question

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?

Answer 1

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.

Answer 2

I ran across a similar problem wanting to input data into a table only if the two columns I was using for indexes did not exist in combination, in this case, "uid_num" and "date".

What I did is alter both the source and destination tables adding a new column "index_string". Then I performed:

UPDATE table SET index_string = CONCAT(uid_num,"_",date) which in my case produced a column of unique values appearing as:

12990_2020-01-01

which is essentially building a hash value.

Now I can do

INSERT INTO destination_table(uid_num, date, index_string) 
    SELECT uid_num, date, index_string 
    FROM source_table 
    WHERE index_string NOT IN(SELECT index_string FROM destination_table);

and if that ONE index_string value does not exist, it gets inserted.

You can repeat with as many columns as you want, as long as one of them is absolutely unique.

Answer 3

If I understand your requirement correctly, you can use a BEFORE INSERT trigger to validate the row. The solution that @Akina presented in How to not allow to insert a new record if count of current records exceeds a specified limit can be adapted to your problem. This solution will perform a full-table scan, so a relevant index would be a good idea. You also should implement a BEFORE UPDATE trigger.

While using a stored procedure or performing the check in the application does work, I recommend this solution because it prevents bad data from getting into your database even if someone does an INSERT statement directly. In my experience, a database that protects itself is more maintainable.