How to add if statements in MySQL alter command?

Question

I have a table:

CREATE TABLE IF NOT EXISTS `application` (
`appid` int(11) NOT NULL,
`appcode` varchar(250) NOT NULL,
`parentid` int(11) DEFAULT NULL,
`appname` varchar(250) NOT NULL,
`appcat` varchar(150) NOT NULL,
`appsubcat` varchar(150) NOT NULL,
`appdesc` varchar(250) NOT NULL,
`active` char(1) NOT NULL DEFAULT 'Y'   
);
TRUNCATE TABLE  ivumetastore.application;

adding alter command:

ALTER TABLE application MODIFY COLUMN `appid` int(11) NOT NULL
AUTO_INCREMENT PRIMARY KEY;

As I am running these 2 commands in separate bat files, getting error for 2nd time ,because already appid PK is present. How can I make it IF NOT EXISTS for Alter command, or truncate all indices, so that it will create again?

You can create a stored procedure – Madhur Bhaiya Oct 11 '18 at 07:46 — Madhur Bhaiya, Oct 11 '18 at 07:46

score 0 · Answer 1 · answered Oct 11 '18 at 07:48

Based on this SO question we can try doing the following from a stored procedure:

IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
        TABLE_SCHEMA = 'your_db_name' AND
        TABLE_NAME = 'application'    AND
        CONSTRAINT_NAME = 'PRIMARY') THEN

    ALTER TABLE application
    MODIFY COLUMN `appid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;

END IF;

How to add if statements in MySQL alter command?

1 Answers1