CODEWITHSUNDEEP
sql-server
Saravanan
Saravanan

Reputation:

Composite key in SQL Server

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

Upvotes: 0

Views: 3646

Answers (4)

Raj More
Raj More

Reputation: 48018

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

Upvotes: 1

A-K
A-K

Reputation: 17080

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;

Upvotes: 0

Adam Ralph
Adam Ralph

Reputation: 29956

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.

Upvotes: 0

jn29098
jn29098

Reputation: 1415

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?

Upvotes: 0

Related Questions