Is It Possible to have a primary key and auto increment key associated to two fields respectively?

Question

Is It Possible to have a primary key and auto increment key associated to two fields respectively?

Like wise, In a table there are 3 fields, say, ID, name and age.. Then is it possible that "ID is autoincrment key and name is primary key"?

Answer 1

There can be only one primary index in the table.
if your name field is really has primary index purpose, you don't need an auto increment field. But I really doubt that. What's the real name field purpose?

Answer 2

According to the MySQL documentation:

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value.

So as long as you satisfy those conditions, there's no mention that it has to also be the primary key. So you should be able to have the primary key and a separate AUTO_INCREMENT column (though I'm not sure why you'd want to).

Answer 3

Also according to the MySQL Documentation:

For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. See Section 3.6.9, “Using AUTO_INCREMENT”.

So if you're using MyISAM, you can do something close to what you've asked (if I'm interpreting your question correctly): you can use a composite PRIMARY KEY, and one of them can be an INT type and use the AUTO_INCREMENT property.

Best thing, read the documentation and examples.

Answer 4

see my response here http://stackoverflow.com/questions/2516828/mysql-second-auto-increment-col-with-diff-behaviour/2520799#2520799

Answer 5

You should use the autoincrement key as the PK and put a unique index on the name field.