primary key

[ohdang888]

i came across an error in mysql ...

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

 

so that means on the "id" column can be used a a primary key. But i want to use "link" as the key because thats what i'll be doing all my queries with....

 

does anyone know what to do with this?

[Barand]

The error message doesn't say there can be only one key and it doesn't say "id" has to be the primary key

 

Integer key fields are more efficient than varchar fields, but if link is unique, you can make that the primary key and not have the id field. But if you need link as a foreign key in other tables then it is going to be duplicated in those, taking far more space than an id would

[ohdang888]

I'm creating a commenting system. and when users go to "example.php?id=5", it will search for all comments that have "5" under the column "page", so "page" will be used the most as a search query.

 

But its not always unique, there could be 1 comment with "5" or, there could be 40..

 

so would it still be more efficient to use "page" as the key.

[Barand]

In which case I'd have

 

id INT auto_increment primary key

 

but set a a non-unique key on "page"