[RESOLVED] Cannot change column to auto_increment?

cupboy

This column needs to be changed to auto_increment. There is already data in the table.
I cannot change it to auto_increment without getting this error:

Error while executing query.

ALTER TABLE rec1.cd_artis MODIFY COLUMN ARTIST_NO INTEGER NOT NULL DEFAULT 0 AUTO_INCREMENT;

MySQL Error Number 1067
Invalid default value for 'ARTIST_NO'

Question #2:
What's wrong with this SQL statement?

insert into cd_artis(artist_no,artist) values (select max(artist_no)+1 from cd_artis,'FLAMING LIPS')

rincewind456

Answer 1: you can't have a default value for an auto-increment field.
Answer 2: you won't want to do that if you have an auto-increment working will you?

Lars_Berg

You need to enclose the scalar subquery in ()

insert into cd_artis(artist_no,artist) 
values ((select max(artist_no)+1 from cd_artis),'FLAMING LIPS')

It is valid SQL but I don't know if it is supported by Mysql.

It can be written as

insert into cd_artis(artist_no,artist) 
select max(artist_no)+1,'FLAMING LIPS' from cd_artis

but as said earlier, you shouldn't use that type of query.

Roger_Ramjet

Can't change a column to autoinc once it contains data.

cupboy

Roger Ramjet wrote:
Can't change a column to autoinc once it contains data.

Thanks. I dropped the table, and re-created it with auto_increment. Then re-ran the dts procedure to load the data. Works fine now.

Drakla

Roger Ramjet wrote:
Can't change a column to autoinc once it contains data.

Sort of - you can't if it contains duplicates, but can if they're all unique with

ALTER TABLE jasper
MODIFY myindex int
PRIMARY KEY
AUTO_INCREMENT
NOT NULL;

But you might as well just alter the table and drop the column, then recreate it as an auto increment and it'll fill itself up.