Composite key in SQL Server

Question

Hi

I am using Sql Server with Composite key. In this composite key contains one identity column i.e Auto incrment value. i want to generate in this column with duplicate values. How can i do this. Please give me a solution for this.

Thanks with Regards

Saravanan.M

Answer 1

You can't have an identity column with duplicates... Otherwise it wouldn't really be an identity column! :)

If you botched your own then use a BigInt or something similar. To increment run a query like...

Select Max(Id) From Table

Then add 1...

Answer 2

The identity column cannot(corrected based on feedback from @AlexKuznetsov) should not have duplicates within the column itself - it is generally meant to be a unique column and a provide non-identifying value for each row.

If you are asking how to put values into the identity column that already exist in another column, you have to do the following:

Set IDENTITY_INSERT Schema.TableName ON

Insert Into TableName (PK1, PK2, IdentityCol1, OtherCol1, OtherCol2)
SELECT FirstCol, SecondCol, SecondCol, OtherColumn1, OtherColumn2
FROM SomeOtherTable

Set IDENTITY_INSERT Schema.TableName OFF

note that PK2 and IdentityCol1 both get the same value

Answer 3

It would be good if you could provide more context around what you are wanting to do and why? There are some good reasons to use composite keys, but if you're already using an identity field, why not make that your primary key?

Answer 4

Drop the identity constraint on the column. Look up the ALTER TABLE, DROP CONSTRAINT syntax.

Answer 5

Your auto-incrementing identity column should be left untouched and should continue to uniquely identify your rows. It is generally good practice to always have an abstract identity column as your primary key.

If you have 2 other values in your data model which uniquely identify your row, they should be in 2 other columns. If one of them is an auto-incrementing number then you can generate the value either in a stored proc which is used for all insertions or in an insert trigger.

Answer 6

Although this is not quite an answer, several answerers have made one and the same mistake, claiming that "You cant have an identity column with duplicates". In fact, identities may easily be not unique if you do not enforce their uniqueness by an index or constraint, as follows:

CREATE TABLE identityTest(i INT IDENTITY(1,1));
GO
INSERT identityTest DEFAULT VALUES;
INSERT identityTest DEFAULT VALUES;
SET IDENTITY_INSERT identityTest ON;
INSERT INTO identityTest(i)
SELECT i FROM identityTest;
SET IDENTITY_INSERT identityTest OFF;
SELECT i FROM identityTest;

i
-----------
1
2
1
2

GO
DROP TABLE identityTest;

Answer 7

While AlexKuznetsov is technically correct, all uses of Identity I've seen also use it as (or part of) a primary key constraint, which would enforce uniqueness.