CODEWITHSUNDEEP
databaseprimary-key
eggdrop
eggdrop

Reputation: 3366

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

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?

Upvotes: 1

Views: 3974

Answers (4)

Matthew Flaschen
Matthew Flaschen

Reputation: 284816

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;

Upvotes: 1

Sasha Chedygov
Sasha Chedygov

Reputation: 130817

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.

Upvotes: 2

Sharique
Sharique

Reputation: 4219

Did you mean auto-increment?

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

Upvotes: 2

Dave Bauman
Dave Bauman

Reputation: 9678

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

Upvotes: 4

Related Questions