CODEWITHSUNDEEP
mysqlwebmin
Mick
Mick

Reputation: 2898

Edit data in MySQL Database with no Primary Key

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 ?

Upvotes: 1

Views: 3551

Answers (1)

Pez Cuckow
Pez Cuckow

Reputation: 14422

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.

Upvotes: 2

Related Questions