Is it possible to change the value of an auto-increment integer primary key field?

Question

If I set a database column as an autonumber primary key field, will the autonumbering feature prevent those values from being modifiable?

Or does the primary key nature of the column make it unmodifiable?

Both? Neither?

Answer 1

Neither. You can modify it all you want. Setting a column as autoincrement just specifies the default value for new rows, that's it. After that it acts just like a normal column.

EDIT: Apparently this isn't true for SQL Server, but you didn't specify so I just went with the popular answer.

Answer 2

As always, it depends on your database. However, the answer is probably neither.

In SQL Server, you are restricted from inserting primary keys manually into an identity column, unless you use the following:

SET IDENTITY_INSERT [dbo].[MyTable] ON
INSERT INTO [dbo].[MyTable] (id, name) VALUES (1000, 'Foo')
SET IDENTITY_INSERT [dbo].[MyTable] OFF

Answer 3

Did you mean auto-increment?

In MS-Sql no,
In mysql yes (may depend on engine)

Answer 4

It's not DB-agnostic. However, MySQL and SQLite (for example) allow you to modify autoincrement primary keys:

mysql:

create table foo (id INTEGER AUTO_INCREMENT, PRIMARY KEY(id));
insert into foo values (null);
select * from foo;
update foo set id = 2 where id = 1;
select * from foo;

sqlite (see http://www.sqlite.org/faq.html#q1 for what AUTOINCREMENT actually means):

create table foo(id INTEGER PRIMARY KEY AUTOINCREMENT);
insert into foo VALUES(null);
select * from foo;
update foo set id = 2 where id = 1;
select * from foo;