CODEWITHSUNDEEP
sql-serverdatabase
jayz
jayz

Reputation: 401

is it possible that an auto increment field duplicates?

I have a table which has several field including:

  1. contact_id
  2. phone
  3. phone_id

contact_id and phone are primary keys and phone_id is an auto increment field. I want to use it to recognize a certain entry. So I want to know that is it possible to duplicate that non primary field when I'm entering data.

Upvotes: 2

Views: 2888

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

The short answer is Yes, it's possible.
SQL Server does not force a unique constraint on identity columns, meaning that the can have duplicated values, however, Sql server will not generate duplicate values itself in an identity column.

Identity columns in sql server are populated by the sql server itself when you insert a row to the table.

However, you can specify a value to them by using SET IDENTITY_INSERT before the insert statement.

There are a couple of things that you should be aware of:

  1. Setting identity_insert on is per table. you can only set it for one table at the time.
  2. Until you set the identity_insert back off, any insert statement to that table will have to specify a value for the identity column.
  3. you can't use set identity insert on for more then one table on a single session. therefor after you've done inserting records to the table you must set the identity_insert back off on that table.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Unless there is no constraint, some unique index, you can duplicate values in that column, because 1) you can turn identity_insert on, 2) you can reseed increments.

Here is a proof:

CREATE TABLE #test(id INT IDENTITY(1, 1))

INSERT INTO #test DEFAULT VALUES
INSERT INTO #test DEFAULT VALUES
INSERT INTO #test DEFAULT VALUES

SET IDENTITY_INSERT #test ON

INSERT INTO #test(id) VALUES(1)

SET IDENTITY_INSERT #test OFF

INSERT INTO #test DEFAULT VALUES
INSERT INTO #test DEFAULT VALUES

DBCC CHECKIDENT ('#test', RESEED, 1);

INSERT INTO #test DEFAULT VALUES
INSERT INTO #test DEFAULT VALUES

SELECT * FROM #test

DROP TABLE #test

Output:

id
1
2
3
1
4
5
2
3

Upvotes: 1

Related Questions