[SOLVED] Index Field

[toxictoad]

Hi,

 

I've created a Db using phpMyAdmin I've added the index field as the first field, with varchar(5),

 

Index:

Keyname  Type      Cardinality      Field

PRIMARY PRIMARY 1      index

 

I thought it would need to be set to auto_increment but when I tried I got this error

 

SQL query:

 

ALTER TABLE `dvds` CHANGE `index` `index` VARCHAR( 5 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL AUTO_INCREMENT

 

MySQL said:

#1063 - Incorrect column specifier for column 'index'

 

So without auto_increment I added  new record but the index field was blank when I was expecting it to show 1

 

Can anyone see what I'm doing wrong?

 

Also what should the 'Collation' be set to or doesn't it matter?

[GingerRobot]

That's because its a VARCHAR field. An auto-increment field should be an INT field.

[fenway]

And don't call an index "index".

[toxictoad]

ok changed the field name to filmID and auto_increment is working thanks 

 

for the text fields in the db what Collation should I use?

[jaymc]

latin_general_ci I use, pretty much standard I think, no hard and fast rule

[toxictoad]

nice one jaymc