0

I tried to create a table which has got 2 primary key and both of them supposed to be auto incremental by changing the Identity specification but in the property window the option (I just mentioned) is disable and I cannot change that.

The column I want to change to auto increment is the primary key of the table and type of it is INT.

What's wrong ? or What I'm wrong ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohammad yummy
  • 111
  • 1
  • 8
  • 2
    And why would you do this? Explain the problem you need to solve, not de solution you think would work (actually, you can't have more than one identity column per table). – Horaciux Apr 14 '15 at 13:36
  • 2
    Why would you want two identity columns? This is completely pointless. You also cannot change a column to be an identity column after it has been created, if you want to do this then see [this answer](http://stackoverflow.com/a/6086661/1048425) for how to achieve this. – GarethD Apr 14 '15 at 13:40

3 Answers3

2

From the definition of IDENTITY in SQL Server, you can read that a table can only have one IDENTITY column. See remarks, quote:

Only one identity column can be created per table.

I also wonder how you get two primary keys on a table, as that is not possible. See the following article on creating primary keys, quote from Limitations and Restrictions:

A table can contain only one PRIMARY KEY constraint.

TT.
  • 15,774
  • 6
  • 47
  • 88
2

As mentioned by TT, you can only have a single identity column and only a single primary key in a table.

For SQL Server 2012 and above, You can however use a SEQUENCE if you need 2 or more auto increment columns. You can then create a PRIMARY KEY on IDENTITY column and a UNIQUE constraint on the column with sequence as default

CREATE SEQUENCE mainseq START WITH 1 INCREMENT BY 1 ;
GO

create table table1 (
 id1 int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 id2 bigint not null constraint DF_table1_id2 default next value for mainseq,
 data varchar(20) not null
)
GO
INSERT INTO table1(data) VALUES('row 1');
INSERT INTO table1(data) VALUES('row 2');

SELECT * FROM table1;

Note that the column with sequence default behaves in differently than an identity. you cannot directly insert into an identity column (except when identity_insert is on) however you can manually insert / update the column being defaulted by a sequence.

ughai
  • 9,830
  • 3
  • 29
  • 47
0

If you need an additional auto incrementing column you could add a trigger to the table FOR INSERT. Something that would find the current maximum value and then add one to it.

jradich1234
  • 1,410
  • 5
  • 24
  • 29
  • This is an undesirable way to implement your own autoincrementing. It is difficult to write correctly, often performs poorly and has a tendency to lock out the whole table against updates, making it effectively single streaming for modifications. Sequences are a better option. – RBarryYoung Apr 14 '15 at 14:00
  • I never indicated that this was a good idea. In fact the whole needing two Primary keys that auto increment is outside of good practice. I'm not even sure how you would use them. – jradich1234 Apr 14 '15 at 14:15