Can't add Primary Key and Auto Increment field to table

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
Sandy
Posts: 53
Joined: Fri 07 Feb 2014 18:11

Can't add Primary Key and Auto Increment field to table

Post by Sandy » Fri 25 May 2018 00:48

I may have found a problem adding primary key fields -

I had a simple table with a few fields, no keys, no auto increment fields (definition below). I opened the table from the explorer, and went into the `column` tab to add a new field `id`, checked the key column and it checked auto increment and not null.

Saved the updates, get a message that 0 is a duplicate key. Column gets added and all are set to 0.

Something seems broken here as I'm pretty sure I could add a primary key after the fact.

Running 7.4.201 / Windows 10 Home
Db version - 10.0.34-MariaDB-0ubuntu0.16.04.1 on a Ubuntu Linux box

Sandy

------

Table structure prior to trying to add the id col, (xxxxx to remove vendor name) -

Code: Select all

CREATE TABLE xxxxx.MX_postal (
  zipcode varchar(20) DEFAULT NULL,
  city varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  state varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  state_code varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  county varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  county_code varchar(20) DEFAULT NULL,
  community varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  community_code varchar(255) DEFAULT NULL,
  latitude double DEFAULT NULL,
  longitude double DEFAULT NULL,
  accuracy_lat_long int(11) DEFAULT NULL
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 120,
CHARACTER SET utf8,
COLLATE utf8_unicode_ci;

ALTER TABLE xxxxx.MX_postal
ADD INDEX IDX_MX_postal_city (city);

ALTER TABLE xxxxx.MX_postal
ADD INDEX IDX_MX_postal_state (state);

ALTER TABLE xxxxx.MX_postal
ADD INDEX IDX_MX_postal_zipcode (zipcode);

alexa
Devart Team
Posts: 2798
Joined: Fri 24 Jun 2011 14:17

Re: Can't add Primary Key and Auto Increment field to table

Post by alexa » Mon 04 Jun 2018 08:33

We are aware of this issue and will fix it in the next product build. We will notify you once it's available for downloading.

Post Reply