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?