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

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