create and update column in MySQL

Question

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

Barmar · Accepted Answer · 2018-11-19 10:16:14Z

1

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

answered Nov 19, 2018 at 10:16

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carlos Over a year ago

Perfect, thank you. What is the name of the structure I used? (I did not know what could be done that way, I have seen the example on the internet)

Barmar Over a year ago

You had too many arguments to IF(). It's just IF(condition, trueResult, falseResult), but you had IF(condition, trueResult, falseResult, anotherFalseResult). The "name" is syntax error.

Collectives™ on Stack Overflow

create and update column in MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related