Bug #57951 Can´t add primary key to existing table
Submitted: 3 Nov 2010 13:37 Modified: 18 Nov 2010 20:30
Reporter: Marian Zsemlye Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S2 (Serious)
Version:5.2.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: Alter table; add primary key

[3 Nov 2010 13:37] Marian Zsemlye 
Description:
Can´t add primary key on existing table.
Right click on table -> alter table.

How to repeat:
ERROR 1067: Invalid default value for 'WHG'

SQL Statement:

ALTER TABLE `SBS`.`Bskupiny` CHANGE COLUMN `WHG` `WHG` INT(3) NOT NULL DEFAULT NULL  

, ADD PRIMARY KEY (`WHG`)

ERROR: Error when running failback script. Details follow.

ERROR 1050: Table 'Bskupiny' already exists

SQL Statement:

CREATE TABLE `Bskupiny` (

  `WHG` int(3) DEFAULT NULL,

  `Popis` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

  `MB` int(3) DEFAULT NULL,

  `MBtext` varchar(70) COLLATE utf8_slovak_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_slovak_ci

Suggested fix:
Normally add primary key to existing table.
If I do it with SQL-script, then is everything OK.
[3 Nov 2010 13:49] Peter Laursen 
Obviously this will fail "NOT NULL DEFAULT NULL"

Peter
(not a MySQL person)
[3 Nov 2010 17:02] Valeriy Kravchuk 
Verified on Mac OS X. Obviously setting column as PK (and, thus, NOT NULL) should remove DEFAULT NULL setting, if any is present.
[18 Nov 2010 20:30] Alfredo Kojima 
duplicate of bug #55456