Mariadb Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near

Question

I am using the following mariadb code to create a table called user with some foreign keys

ALTER TABLE `user`
  ADD COLUMN IF NOT EXISTS `shipping_address` int(11) NULL,
  ADD COLUMN IF NOT EXISTS `billing_address` int(11) NULL,
  ADD CONSTRAINT IF NOT EXISTS `user_ibfk_2` FOREIGN KEY (`shipping_address`) REFERENCES `user_address` (`id`),
  ADD CONSTRAINT IF NOT EXISTS `user_ibfk_3` FOREIGN KEY (`billing_address`) REFERENCES `user_address` (`id`);

I think that this code is correct but still it generates the following error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOREIGN KEY (`shipping_address`) REFERENCES `user_address` (`id`),
  ADD CON...' at line 

Is this code actually correct? If not then how do I fix it? What would be the correct varient of this code?

Answer 1

As mariadb (not mysql!) documentation on alter table shows, the if not exists clause comes after foreign key:

ADD [CONSTRAINT [symbol]]
    FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
    reference_definition

So, your alter table should look something like

ALTER TABLE `user`
  ADD COLUMN IF NOT EXISTS `shipping_address` int(11) NULL,
  ADD COLUMN IF NOT EXISTS `billing_address` int(11) NULL,
  ADD CONSTRAINT `user_ibfk_2` FOREIGN KEY IF NOT EXISTS (`shipping_address`) REFERENCES `user_address` (`id`),
  ADD CONSTRAINT `user_ibfk_3` FOREIGN KEY IF NOT EXISTS (`billing_address`) REFERENCES `user_address` (`id`);