Adding auto increment column other than primary key

Question

I have table in which has key is primary key. I want to add seqNo key which should be auto incremented but it does not allow to make it as auto increment.

Because there is already one primary key,

Is it possible to make seqNo auto increment? currently seqNo is not present. I want to add it

Answer 1

You can't have two identity columns in a SQL table but you can still create sequences. Here's the link http://technet.microsoft.com/en-us/library/ff878091.aspx

Answer 2

You have the following options.

1. Make a trigger that increments your column value on every insert statement
2. Use a sequence, but once a sequence value is generated it will never be generated again (meaning, you would get a gap in your values if your insert fails for some reason)

Answer 3

Vignesh, consider the following...

 DROP TABLE IF EXISTS test;

 CREATE TABLE test 
 ( testID int(11) NOT NULL
 , string varchar(45) DEFAULT NULL
 , testInc int(11) NOT NULL AUTO_INCREMENT
 , PRIMARY KEY (testID)
 , KEY testInc (testInc)
 );

 INSERT INTO test
 (testID
 , string
 ) values 
 (1
 ,'Hello'
  );

 INSERT INTO test (testid,string) SELECT x.testid + y.max_test,string FROM test x JOIN (SELECT MAX(testid) max_test FROM test)y;
 INSERT INTO test (testid,string) SELECT x.testid + y.max_test,string FROM test x JOIN (SELECT MAX(testid) max_test FROM test)y;
 INSERT INTO test (testid,string) SELECT x.testid + y.max_test,string FROM test x JOIN (SELECT MAX(testid) max_test FROM test)y;
 Query OK, 4 rows affected (0.03 sec)

 SELECT * FROM test;
 +--------+--------+---------+
 | testID | string | testInc |
 +--------+--------+---------+
 |      1 | Hello  |       1 |
 |      2 | Hello  |       2 |
 |      3 | Hello  |       3 |
 |      4 | Hello  |       4 |
 |      5 | Hello  |       6 |
 |      6 | Hello  |       7 |
 |      7 | Hello  |       8 |
 |      8 | Hello  |       9 |
 +--------+--------+---------+

Note that the number of rows (8), and the value of testinc (9) are different. This is not what the OP wants. The MAX() trick I've used for generating the PK is also no good, because it's subject to runtime errors.

fiddle of same http://www.sqlfiddle.com/#!2/d29a5b/1

The point is... storing a sequential id is pointless.