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
50
votes
7 answers

SQL server identity column values start at 0 instead of 1

I've got a strange situation with some tables in my database starting its IDs from 0, even though TABLE CREATE has IDENTITY(1,1). This is so for some tables, but not for others. It has worked until today. I've tried resetting identity column: DBCC…
Muxa
  • 5,563
  • 6
  • 46
  • 56
50
votes
7 answers

SQL Server, How to set auto increment after creating a table without data loss?

I have a table table1 in SQL server 2008 and it has records in it. I want the primary key table1_Sno column to be an auto-incrementing column. Can this be done without any data transfer or cloning of table? I know that I can use ALTER TABLE to add…
arun
  • 781
  • 2
  • 6
  • 14
48
votes
4 answers

serial in postgres is being increased even though I added on conflict do nothing

I'm using Postgres 9.5 and seeing some wired things here. I've a cron job running ever 5 mins firing a sql statement that is adding a list of records if not existing. INSERT INTO sometable (customer, balance) VALUES (:customer, :balance) ON…
Christian
  • 6,961
  • 10
  • 54
  • 82
47
votes
5 answers

Should I implement auto-incrementing in MongoDB?

I'm making the switch to MongoDB from MySQL. A familiar architecture to me for a very basic users table would have auto-incrementing of the uid. See Mongo's own documentation for this use case. I'm wondering whether this is the best architectural…
Josh Smith
  • 14,674
  • 18
  • 72
  • 118
44
votes
6 answers

MySQL: #1075 - Incorrect table definition; autoincrement vs another key?

Here is a table in MySQL 5.3.X+ db: CREATE TABLE members` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `memberid` VARCHAR( 30 ) NOT NULL , `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `firstname` VARCHAR( 50 ) NULL , …
Haradzieniec
  • 9,086
  • 31
  • 117
  • 212
44
votes
2 answers

Postgresql Sequence vs Serial

I was wondering when it is better to choose sequence, and when it is better to use serial. What I want is returning last value after insert using SELECT LASTVAL(); I read this question PostgreSQL Autoincrement I never use serial before.
Se Song
  • 1,613
  • 2
  • 19
  • 32
44
votes
5 answers

How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME?

How can I avoid getting this MySQL error Incorrect column specifier for column topic_id ? MySQL Error... #1063 - Incorrect column specifier for column 'topic_id' SQL Schema... CREATE TABLE discussion_topics ( topic_id char(36) NOT NULL…
JasonDavis
  • 48,204
  • 100
  • 318
  • 537
44
votes
4 answers

How to AUTO_INCREMENT in db2?

I thought this would be simple, but I can't seem to use AUTO_INCREMENT in my db2 database. I did some searching and people seem to be using "Generated by Default", but this doesn't work for me. If it helps, here's the table I want to create with…
Matt
  • 5,408
  • 14
  • 52
  • 79
43
votes
1 answer

How to choose between UUIDs, autoincrement/sequence keys and sequence tables for database primary keys?

I'm looking at the pros and cons of these three primary methods of coming up with primary keys for database rows. So assuming I am using a database that supports more than one of these methods, is there a simple heuristic to determine what the best…
Tim
  • 6,851
  • 11
  • 42
  • 46
43
votes
9 answers

"Auto increment" alphabet in Java?

"Auto increment" alphabet in Java - is this possible? From A to Z without a third-party library?
Dacto
  • 2,901
  • 9
  • 45
  • 54
42
votes
9 answers

Prevent auto increment on MySQL duplicate insert

Using MySQL 5.1.49, I'm trying to implement a tagging system the problem I have is with a table with two columns: id(autoincrement), tag(unique varchar) (InnoDB) When using query, INSERT IGNORE INTO tablename SET tag="whatever", the auto increment…
robert
  • 1,523
  • 5
  • 19
  • 27
42
votes
2 answers

Changing primary key int type to serial

Is there a way to change existing primary key type from int to serial without dropping the table? I already have a lot of data in the table and I don't want to delete it.
MarisP
  • 967
  • 2
  • 10
  • 24
42
votes
6 answers

How does autoIncrement work in NodeJs's Sequelize?

Sequelize's document doesn't say a whole lot about autoIncrement. It only includes the following example: // autoIncrement can be used to create auto_incrementing integer columns incrementMe: { type: Sequelize.INTEGER, autoIncrement: true } Based…
Eric H.
  • 6,894
  • 8
  • 43
  • 62
40
votes
8 answers

How can I reset an MySQL AutoIncrement using a MAX value from another table?

I know this won't work. I tried it in various forms and failed all times. What is the simplest way to achieve the following result? ALTER TABLE XYZ AUTO_INCREMENT = (select max(ID) from ABC); This is great for automation projects. SELECT @max :=…
ThinkCode
  • 7,841
  • 21
  • 73
  • 92
39
votes
4 answers

Auto increment table column

Using Postgres, I'm trying to use AUTO_INCREMENT to number my primary key automatically in SQL. However, it gives me an error. CREATE TABLE Staff ( ID INTEGER NOT NULL AUTO_INCREMENT, Name VARCHAR(40) NOT NULL, PRIMARY KEY…
Jimmy
  • 817
  • 4
  • 13
  • 19
1 2 3
…
99 100