MySql IF statement in query

Question

I just finished picking up my jaw off the floor after learning that you can't use an IF statement in a query in MySql. How can anyone get anything done without an IF statement?!

What I'm trying to do is write a DML script that adds a constraint to a table, if it doesn't exist. Something like this:

if (select count(*) from information_schema.table_constraints
    where constraint_name='fk_user_user_status') = 0
then
    alter table `user`
        add constraint fk_user_user_status foreign key (status_id)
            references user_status(id);
end if;

How would one do this in MySql?

Thanks in advance!

related: http://stackoverflow.com/questions/3919226/mysql-add-constraint-if-not-exists — Marat Salikhov, Mar 26 '11 at 08:54

score 3 · Answer 1 · answered Mar 26 '11 at 08:53

You can only run IF statements in stored procedures and functions.

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

If you are executing standard scripts, then IF statements only work with SELECT queries.

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

score 2 · Accepted Answer · answered Mar 26 '11 at 09:54

2

Procedural batch, but within the below, a proc is created and dropped after the operation completes.

delimiter $$

drop procedure if exists tmp_add_fk $$

create procedure tmp_add_fk()
begin
if (select count(*) from information_schema.table_constraints
    where constraint_name='fk_user_user_status') = 0
then
    alter table `user`
        add constraint fk_user_user_status foreign key (status_id)
            references user_status(id);
end if;
end$$

delimiter ;

call tmp_add_fk;

drop procedure tmp_add_fk;

answered Mar 26 '11 at 09:54

RichardTheKiwi

105,798
26
196
262

Awesome! Thanks, Richard! I was just thinking about doing the same. One question, though: what are those $$'s and delimiters for? – alekop Mar 27 '11 at 23:29
@alekop You could almost get away without changing the delimiter since the proc finishes in one statement. But it allows you to have multi-statement procs (containing `;`) which would otherwise end the proc definition as soon as it sees `;`. e.g. `delimiter $$` changes it so that statements only stop at `$$`. – RichardTheKiwi Mar 27 '11 at 23:56
Got it, thanks! This is only necessary when working through the `mysql` shell. – alekop Mar 27 '11 at 23:57

MySql IF statement in query

2 Answers2