Adding Key to MySQL Table

and populating it

[Nick_W]

Hi all

Is it possible to add a key to a table and populate that key? - I just need a numerical key on a table and can't work out how I might do it...

Many thanks...

Nick

[andreasfriedrich]

Have a look at the ALTER TABLE [mysql.com] command.

[Nick_W]

Yep, know about that. Just wondered about the population of the key? - I'll go check it out but I suspect it won't be there...

Nick

[andreasfriedrich]

You need to populate it yourself since mysql canīt guess the values that you want it to have.

[Nick_W]

Right!

Can't work it out :(

Trying

add id (int not null auto_increment)

and

add id int not null auto_increment

Not working. Where am I going wrong? - I hate the mysql manual --------

Nick

[Nick_W]

Got it!

Thanks again andreas...

Nick

[Nick_W]

Hey! And not only that, it did populate it ;)

Nick

[andreasfriedrich]

alter table test add id int not null auto_increment primary key;

An auto_increment column needs to be defined as a key. Thatīs why I added the primary key to it. To be sure it needs not be the primary one.

I hate the mysql manual

Well, itīs not as nice as the PHP or Perl one but it tries to be precise by using some form of BNF notation [cui.unige.ch].

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

where alter_spec is defined as

alter_specification:
ADD [COLUMN] create_definition [FIRST ¦ AFTER column_name ]

Always start reading at the top of the specification and then work your way down to the next item.

Andreas

[andreasfriedrich]

it did populate it

Yes, it does populate auto_increment columns. It has to since those need to be defined as unique keys. If it would not populate them, then they wouldnīt be unique and the operation would fail.