SQL INSERT IGNORE: skip values in a special case

Question

Hi, I'm trying to skip an INSERT in a particular case, where one of two involved values is a specific value:

// this can work if Beech allready exists
INSERT IGNORE INTO my_table (col_wood_name, col_type)
VALUES ("Beech", 0)

// this should be skipped if Beech allready exists with col_type = 1
INSERT IGNORE INTO my_table (col_wood_name, col_type)
VALUES ("Beech", 1)

my_table:
id      col_wood_name     col_type       other_column_1     other_column_2     etc_cols
1       Beech             0              ...                ...                ...
2       Fir               0              ...                ...                ...
3       Beech             1              ...                ...                ...
4       Pine              1              ...                ...                ...
5       Beech             1 // here is my problem, how can I avoid to insert a tree if col_type == 1?
6       Beech             0 // it's ok because col_type == 0

how can I avoid to insert a tree name if col_type == 1?

I know I can create unique key on col_wood_name and col_type using something like:

ALTER TABLE my_table ADD UNIQUE(col_wood_name, col_type);

but this is incomplete because this should work only if col_type is 1, how can I solve it?

Answer 1

How many different col_type are possible?

I ask because of design reasons. If you only have two columns in the table, I'm not seeing how it would be beneficial not to do the unique constraint on the two fields.

Answer 2

AFAIK, there is no way of doing that in one query. Use Stored Procedures or an additional query in your application.