SQL ALTER TABLE ADD PRIMARY KEY error1064

Question

For some reason my sql script fails on adding the primary key statement. I was wondering if anyone could explain why ?

Here is the table statement.

CREATE TABLE IF NOT EXISTS `wp_bwg_album` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` mediumtext NOT NULL,
  `preview_image` mediumtext NOT NULL,
  `random_preview_image` mediumtext NOT NULL,
  `order` bigint(20) NOT NULL,
  `author` bigint(20) NOT NULL,
  `published` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And here is the stement it fails on -

ALTER TABLE `wp_bwg_album`
  ADD PRIMARY KEY (`id`);

Also related to the table is this final statement.

ALTER TABLE `wp_bwg_album`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

I confused as what the problem is, the SQL is a dump from a my live server. I am just updating my development server at home. using MySql workbench I get the standard 1064 error.

16:58:40 ADD PRIMARY KEY (id) Error Code: 1064. 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 'ADD PRIMARY KEY (id)' at line 1 0.000 sec

Any help would be appreciated.

Answer 1

Try this:

ALTER TABLE `wp_bwg_album`
    ADD CONSTRAINT `pk_wp_bwg_album` PRIMARY KEY (`id`);

The constraint name can be anyone.