Stack Overflow
Stack Overflow

Questions tagged [auto-increment]

a database constraint that automatically increases from the last record when making an INSERT

AUTO_INCREMENT is a flag set on a field in a database table that forces the RDBMS to create a unique identifier for the record. This is useful when no other obvious primary key field is apparent in a record.

Useful questions

Related tags

2677 questions
1594
votes
25 answers

How to reset AUTO_INCREMENT in MySQL

How can I reset the AUTO_INCREMENT of a field? I want it to start counting from 1 again.
homerun
  • 19,837
  • 15
  • 45
  • 70
695
votes
11 answers

What's the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?

I'm switching from MySQL to PostgreSQL and I was wondering how can I have an INT column with AUTO INCREMENT. I saw in the PostgreSQL docs a datatype called SERIAL, but I get syntax errors when using it.
Ian
  • 24,116
  • 22
  • 58
  • 96
515
votes
18 answers

How to create id with AUTO_INCREMENT on Oracle?

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g. How can I create a column that behaves like auto increment in Oracle 11g?
Sushan Ghimire
  • 7,307
  • 16
  • 38
  • 65
404
votes
13 answers

Get the new record primary key ID from MySQL insert query?

Let's say I am doing a MySQL INSERT into one of my tables and the table has the column item_id which is set to autoincrement and primary key. How do I get the query to output the value of the newly generated primary key item_id in the same…
Amy Neville
  • 10,067
  • 13
  • 58
  • 94
396
votes
18 answers

Reset auto increment counter in postgres

I would like to force the auto increment field of a table to some value, I tried with this: ALTER TABLE product AUTO_INCREMENT = 1453 AND ALTER SEQUENCE product RESTART WITH 1453; ERROR: relation "your_sequence_name" does not exist I have a…
Rad
  • 4,403
  • 4
  • 24
  • 25
381
votes
10 answers

How to set initial value and auto increment in MySQL?

How do I set the initial value for an "id" column in a MySQL table that start from 1001? I want to do an insert "INSERT INTO users (name, email) VALUES ('{$name}', '{$email}')"; Without specifying the initial value for the id column.
bbtang
  • 5,665
  • 8
  • 33
  • 27
372
votes
9 answers

Get current AUTO_INCREMENT value for any table

How do I get the current AUTO_INCREMENT value for a table in MySQL?
bparise
  • 3,809
  • 2
  • 13
  • 5
333
votes
14 answers

Reset AutoIncrement in SQL Server after Delete

I've deleted some records from a table in a SQL Server database. The IDs in the table look like this: 99 100 101 1200 1201... I want to delete the later records (IDs >1200), then I want to reset the auto increment so the next autogenerated ID will…
jumbojs
  • 4,768
  • 9
  • 38
  • 50
317
votes
10 answers

How to set auto increment primary key in PostgreSQL?

I have a table in PostgreSQL with many columns, and I want to add an auto increment primary key. I tried to create a column called id of type BIGSERIAL but pgadmin responded with an error: ERROR: sequence must have same owner as table it is linked…
mkn
  • 12,024
  • 17
  • 49
  • 62
313
votes
5 answers

ERROR: permission denied for sequence cities_id_seq using Postgres

I ran following sql script on my database: create table cities ( id serial primary key, name text not null ); create table reports ( id serial primary key, cityid integer not null references cities(id), reportdate date not null, reporttext text not…
Tõnis Ojandu
  • 3,486
  • 4
  • 20
  • 28
286
votes
5 answers

How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?
xRobot
  • 25,579
  • 69
  • 184
  • 304
170
votes
9 answers

Is there AUTO INCREMENT in SQLite?

I am trying to create a table with an auto-incrementing primary key in Sqlite3. I am not sure if this is really possible, but I am hoping to only have to designate the other fields. For example: CREATE TABLE people (id integer primary key auto…
ewok
  • 20,148
  • 51
  • 149
  • 254
125
votes
7 answers

How to get last inserted row ID from WordPress database?

My WordPress plugin has a table with a AUTO_INCREMENT primary key field called ID. When a new row is inserted into the table, I'd like to get the ID value of the insertion. The feature is to using AJAX to post data to server to insert into DB. The…
Morgan Cheng
  • 73,950
  • 66
  • 171
  • 230
117
votes
17 answers

Add Auto-Increment ID to existing table?

I have a pre-existing table, containing 'fname', 'lname', 'email', 'password' and 'ip'. But now I want an auto-increment column. However, when I enter: ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT I get the following: #1075 - Incorrect…
Charles Jenkins
  • 1,179
  • 2
  • 8
  • 4
109
votes
7 answers

How to insert data to MySQL with auto-incremented column(field)?

I've created a table with a primary key and enabled AUTO_INCREMENT: CREATE TABLE IF NOT EXISTS test.authors ( hostcheck_id INT PRIMARY KEY AUTO_INCREMENT, instance_id INT, host_object_id INT, check_type INT, is_raw_check INT, …
Salman Raza
  • 1,635
  • 6
  • 18
  • 18
1
2 3
…
99 100