MySQL - How to create an auto increement field in the DB?

Question

I am entering records in the MySQL DB. Now I want to have a "Serial_Number" field that increements automatically whenever a record is entered into the DB.

I don't want this "Serial_Number" field to be the primary key of the DB.

How can I create this field (with the attributes needed to be set).

I am using "SQL YOG" to access MySQL. If you are aware of the SQL YOG then tell me how to do that through SQL YOG.

Answer 1

create table mytable ( ID INT IDENTITY(1,1) PRIMARY KEY,

SN INT IDENTITY(1,1)

)

Answer 2

I don't think you can have an auto increment field:

CREATE TABLE `t` (`dd` int(11) NOT NULL) 
ALTER TABLE `t` CHANGE `dd` `dd` INT( 11 ) NOT NULL AUTO_INCREMENT

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

Answer 3

The AUTO_INCREMENT column has to have a UNIQUE KEY constraint associated to it.

For instance, this will work just fine:

CREATE TABLE AutoNotId
(
    Id INTEGER PRIMARY KEY,
    Auto INTEGER AUTO_INCREMENT,
    UNIQUE (Auto)
);

Edit:

The ALTER statement would look somewhat like this:

ALTER TABLE AutoNotId
    MODIFY COLUMN Auto INTEGER AUTO_INCREMENT,
    ADD UNIQUE (Auto);

I recommended, however the use of the long-hand syntax to specify the name of the UNIQUE constraint; But you can always refer to MySQL's Reference Manual for the exact specifications.

Answer 4

In MySQL tables can only have one auto increment field and they must be indexed.

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

Is there a reason you don't want it to be the primary key?

If you want an incrementing value, you could fudge it by running updates after each insert:

SELECT MAX(serial) + 1 FROM myTable;

UPDATE myTable SET serial = <that number> WHERE id = ...

Answer 5

You cannot do this in MySQL. From the doc:

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

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