Add foreign key column to existing table

Question

I am just adding a column to a table and making that new column a foreign key.

I have tried removing the back-ticks from the statement.

Here is the statement:

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

Here is the Table: // column names have been turned into letters for, reasons...:

CREATE TABLE `user_list_v4` (
     `a` int(11) NOT NULL,
     `b` varchar(50) DEFAULT NULL,
     `c` varchar(50) DEFAULT NULL,
     `d` varchar(50) NOT NULL DEFAULT '',
     `e` varchar(255) NOT NULL DEFAULT '',
     `f` varchar(5) NOT NULL DEFAULT '',
     `g` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `h` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I run the statement I expect it to say: Query OK, 0 rows affected (0.00 sec)

But I get this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right 
syntax to use near '`role.id`
  KEY `role_id` (`role_id`),
  CONSTRAINT `developer_standup_timezone_' at line 2

I'm sure its a simple syntax error but I'm not finding it

Put your COMMENT in single quotes (not backticks). – PM 77-1 Apr 11 '19 at 21:34 — PM 77-1, Apr 11 '19 at 21:34

Kaiser · Accepted Answer · 2019-04-11T21:38:54.070

1

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

edited Apr 11 '19 at 21:38

answered Apr 11 '19 at 21:32

Kaiser

1,957
1
19
28

spencer7593 · Answer 2 · 2019-04-11T21:38:43.287

If we want to make a new column NOT NULL, I think we need to assign a DEFAULT value to be assigned to the existing rows.

It's easier to allow the column to be null, and have default value of NULL assigned.

Something like this:

ALTER TABLE `user_list_v4`
  ADD `role_id` int(11) DEFAULT 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`)
;

The syntax makes more sense if we understand that we are adding each component separately. We are adding a column. We are adding an index. And we are adding a foreign key constraint. We can achieve the same result with three separate statements:

ALTER TABLE `user_list_v4`
  ADD `role_id` int(11) DEFAULT NULL COMMENT 'role.id' 
; 

ALTER TABLE `user_list_v4`
  ADD KEY `role_id` (`role_id`)
;

ALTER TABLE `user_list_v4`
  ADD CONSTRAINT `developer_standup_timezone_ibfk_1` 
      FOREIGN KEY (`role_id`) REFERENCES `role_list_v4` (`id`)
;

Note that with InnoDB, we don't have to create the index; InnoDB will automatically create a suitable index when we add the foreign key constraint. Adding the index in a separate step gives us more control over the name, and we can add additional columns, et al.

If we need the new column to be non-NULL, and we want to implement the foreign key constraint, then every row in the table has to be assigned a value that references a valid row in the target table. (Assuming we aren't going to muck with disabling foreign keys.)

Yeah, NOT NULL didn't make sense – Mitch Kroska Apr 11 '19 at 22:03 — Mitch Kroska, Apr 11 '19 at 22:03

Add foreign key column to existing table

2 Answers2