How to Add a Column and Make It a Foreign Key in Single MySQL Statement

Add foreign key column to existing table

You have to add the column and also add the foreign key:

   ALTER TABLE `user_list_v4`
   ADD `role_id` INT NOT NULL COMMENT 'role.id', 
   ADD KEY `role_id` (`role_id`),
   ADD CONSTRAINT `developer_standup_timezone_ibfk_1` 
   FOREIGN KEY (`role_id`) REFERENCES `role_list_v4` (`id`);

How to add a foreign key column in the MySQL table?

First you need to add new column in table orderbook

ALTER TABLE orderbook
ADD payment_request_id INT(10) unsigned AFTER ID;

Then add a contraint that will define the foreign key

ALTER TABLE orderbook
ADD CONSTRAINT fk_orderbook FOREIGN KEY (payment_request_id) 
REFERENCES payment_request (id);

Reference:

• MySQL Alter Table

Add new column with foreign key constraint in one command

As so often with SQL-related question, it depends on the DBMS. Some DBMS allow you to combine ALTER TABLE operations separated by commas. For example...

Informix syntax:

ALTER TABLE one
    ADD two_id INTEGER,
    ADD CONSTRAINT FOREIGN KEY(two_id) REFERENCES two(id);

The syntax for IBM DB2 LUW is similar, repeating the keyword ADD but (if I read the diagram correctly) not requiring a comma to separate the added items.

Microsoft SQL Server syntax:

ALTER TABLE one
    ADD two_id INTEGER,
    FOREIGN KEY(two_id) REFERENCES two(id);

Some others do not allow you to combine ALTER TABLE operations like that. Standard SQL only allows a single operation in the ALTER TABLE statement, so in Standard SQL, it has to be done in two steps.

Add Foreign Key to existing table

To add a foreign key (grade_id) to an existing table (users), follow the following steps:

ALTER TABLE users ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE users ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) REFERENCES grades(id);

mySQL How to alter column to have foreign key?

Just use ALTER TABLE along with ADD CONSTRAINT:

ALTER TABLE `table` ADD CONSTRAINT fk_l_id FOREIGN KEY (id_l) REFERENCES table_b(id_l);

Adding foreign key to column in mysql does not create one

Please check below points-

1. Table engine should be innodb.
2. column properties (data type, length etc.) should be same for link column in parent/child both tables.
3. referenced column should be indexed in parent table.
4. If tables already have data then each child value in referenced column should exist in parent table also.

If all above points are fine, then share error details thrown by mysql.

Why foreign key is not created when defined at column level in MySQL?

This is a feature request in MySQL that dates back to 2004, but it has never been implemented. https://bugs.mysql.com/bug.php?id=4919

The workaround is to use table-level foreign key constraint syntax, even if your case would allow a column-level constraint because it's only for a single column.

create table Score ( 
 rollno int, 
 marks int,
 foreign key(rollno) references student(rollno) 
);

The reason for this missing feature may be unsatisfying, but here it is, from the architect of InnoDB:

MySQL does not give a syntax error, because it was the intention in the 1990's that table definitions containing FOREIGN KEY constraints should be importable to MySQL, even though MySQL at that time did not support foreign keys.

For other storage engines that don't support foreign keys at all (e.g. MyISAM), it is also the case that the foreign key syntax is accepted but ignored. No error or warning is returned, but the foreign key constraint is not saved.

---

Related Topics