CODEWITHSUNDEEP
mysqlmysql-workbench
Carlos
Carlos

Reputation: 201

create and update column in MySQL

I need this code to create one column and then make it another foreign key.

I do not want another code, I need this for certain reasons. How can I add code to modify the created column and make it a foreign key?

-- Actualizando la tabla: action

DELIMITER $$
SET @s = (SELECT IF(
(SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'action'
AND table_schema = 'ibexsales_dev'
AND column_name = 'company_id'
) > 0,
"SELECT 1",
"ALTER TABLE action ADD company_id INT(11) NOT NULL"
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
$$ DELIMITER ;

I need add this code: 

ALTER TABLE `ibexsales_dev`.`action` 
                    ADD CONSTRAINT `fk_company_id_action` 
                    FOREIGN KEY (`company_id`) REFERENCES company(`company_id`);

I tried this but it does not work:

) > 0,
"SELECT 1",
"ALTER TABLE action ADD company_id INT(11) NOT NULL",
"    ALTER TABLE `ibexsales_dev`.`action` 
                    ADD CONSTRAINT `fk_company_id_action` 
                    FOREIGN KEY (`company_id`) REFERENCES company(`company_id`); "
));

Upvotes: 0

Views: 146

Answers (1)

Barmar
Barmar

Reputation: 780655

You don't repeat ALTER TABLE. A single ALTER TABLE query can contain multiple alterations, separated by comma.

) > 0,
"SELECT 1",
"ALTER TABLE action ADD company_id INT(11) NOT NULL,
                    ADD CONSTRAINT `fk_company_id_action` 
                        FOREIGN KEY (`company_id`) REFERENCES company(`company_id`); "
));

Upvotes: 1

Related Questions