mysql - 在 MySQL 中创建和更新列

Question

我需要此代码来创建一列，然后将其设为另一个外键。

我不需要另一个代码，出于某些原因我需要这个。如何添加代码来修改创建的列并将其设为外键？

-- 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 ;

我需要添加此代码:

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

我尝试了这个，但它不起作用:

) > 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`); "
));

Answer 1

您不要重复ALTER TABLE。单个 ALTER TABLE 查询可以包含多个更改，以逗号分隔。

) > 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`); "
));