1

I am using Webmin on an Idaq hosting package to manage my MySQL database. I have imported the database from a previous host and now find that I cannot alter any data that is in a particular table because of an error "Data in this table cannot be edited because it has no primary key."

In a php script I can alter data but really need to alter it from the control panel.

When I try and create a primary key field I get the following error "Failed to save field : SQL alter table userbase add referenceNumber smallint not null auto_increment failed : Incorrect table definition; there can be only one auto column and it must be defined as a key"

I am well aware that any table should contain a primary key and this is my mistake , but can anyone offer some help please ?

Mick
  • 2,840
  • 10
  • 45
  • 61

1 Answers1

2

It's because you aren't setting it as a key at the same time so it fails the and it must be defined as a key part, put key on the end.

Use

ALTER TABLE tableName ADD autoIncrementColumn MEDIUMINT NOT NULL AUTO_INCREMENT KEY

Extra Info from Johan in comments

@Mick, To add to PEZ's answer, if you do not provide a primary key, MySQL adds a hidden primary integer autoincrement key. In order to override the hidden key, the alter table statement must specify the addition of a new field and the assignment of the new PK in the same line, otherwise the hidden PK will block the alteration.

Pez Cuckow
  • 14,048
  • 16
  • 80
  • 130
  • @Mick, To add to PEZ's answer, if you do not provide a primary key, MySQL adds a hidden primary integer autoincrement key. In order to override the hidden key, the `alter table` statement must specify the addition of a new field and the assignment of the new PK in the same line, otherwise the hidden PK will block the alteration. – Johan Aug 25 '11 at 14:47
  • Thanks for the extra detail Johan, I didn't realise this! – Pez Cuckow Aug 25 '11 at 15:01