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

How to return autoincrement value in insert query in SQLite?

In my project I use System.Data.SQLite. Database has table Tags, which contains autoincrement primary field ID (type Integer). When I write: using (SQLiteCommand command = conn.CreateCommand()) { command.CommandText = "insert into Tags(name)…
Anton Kandybo
  • 3,678
  • 4
  • 23
  • 31
7
votes
1 answer

PostgreSQL's SERIAL vs MySQL's AUTO_INCREMENT?

I have this snippet of MySQL: CREATE TABLE seq_test ( id INT PRIMARY KEY AUTO_INCREMENT, name TEXT ); INSERT INTO seq_test(id, name) VALUES (1, 'one'); INSERT INTO seq_test(name) VALUES ('two'); When I try to write this in PostgreSQL: CREATE…
pathikrit
  • 32,469
  • 37
  • 142
  • 221
7
votes
2 answers

MySQL InnoDB auto_increment value increases by 2 instead of 1. Virus?

There's an InnoDB table for storing comments for blog posts used by a custom built web application. Recently I noticed that the auto incremented primary key values for the comments are incrementing by 2 instead of just 1. I also noticed that in…
Wabbitseason
  • 5,641
  • 9
  • 49
  • 60
7
votes
2 answers

How to use an auto incremented primary key as a foreign key as well?

This is what I'm trying to do: I have 2 tables... CREATE TABLE `parent` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; CREATE TABLE `child` ( `parent_id`…
Rolf
  • 5,550
  • 5
  • 41
  • 61
7
votes
3 answers

pg_dump serial datatype issues

Could someone explain to me why a PostgreSQL table created with the following scripts: CREATE TABLE users ( "id" serial NOT NULL, "name" character varying(150) NOT NULL, "surname" character varying (250) NOT NULL, "dept_id" integer NOT…
Peter
  • 181
  • 2
  • 12
7
votes
3 answers

How to set AUTO_INCREMENT from another table

How can I set the AUTO_INCREMENT on CREATE TABLE or ALTER TABLE from another table? I found this question, but not solved my issue: How to Reset an MySQL AutoIncrement using a MAX value from another table? I also tried this: CREATE TABLE IF NOT…
Evren
  • 181
  • 2
  • 11
7
votes
1 answer

MySQL returns last inserted when querying IS NULL

Whenever I do a SELECT statement with WHERE id is NULL directly after an INSERT, I get the last inserted row. I am using MySQL 5.1.73. It happens directly in the MySQL shell; here is my console: mysql> CREATE TABLE testing ( -> id int(11)…
mtricht
  • 427
  • 4
  • 16
7
votes
2 answers

Derby Auto Increment by 100 when specified as 1

I used the statement below to create a Derby database table with auto-increment primary column. CREATE TABLE \"table\" (\n" + " \"id\" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,\n" …
Bruce
  • 8,609
  • 8
  • 54
  • 83
7
votes
4 answers

Easy way to compute how close an auto_increment is to its maximum value?

So yesterday we had a table that has an auto_increment PK for a smallint that reached its maximum. We had to alter the table on an emergency basis, which is definitely not how we like to roll. Is there an easy way to report on how close each…
David M
  • 4,325
  • 2
  • 28
  • 40
7
votes
2 answers

Generating incremental numeric column values during INSERT SELECT statement

I need to copy some data from one table to another in Oracle, while generating incremental values for a numeric column in the new table. This is a once-only exercise with a trivial number of rows (100). I have an adequate solution to this problem…
Igby Largeman
  • 16,495
  • 3
  • 60
  • 86
7
votes
1 answer

Alter the LAST_INSERT_ID() from within a TRIGGER in MySQL

I have a BEFORE INSERT TRIGGER which is used to calculate the AUTO_INCREMENT value of a column (id_2). id_1 | id_2 | data 1 | 1 | 'a' 1 | 2 | 'b' 1 | 3 | 'c' 2 | 1 | 'a' 2 | 2 | 'b' 2 | 3 | 'c' 2 | 4 | 'a' 3…
Xeos
  • 5,975
  • 11
  • 50
  • 79
7
votes
1 answer

Mysql with Python can't insert record with autoincrement id, why?

I create a table 'test' with two column:(age int, name TEXT) in mysql database. Then I insert a record using the following codes(with a list): record = [12, 'Tom'] cursor.execute("insert into test values(%s,%s)", record) The above codes work in…
Deep_fox
  • 405
  • 2
  • 6
  • 14
7
votes
1 answer

How to change a primary key in SQL to auto_increment?

I have a table in MySQL that has a primary key: mysql> desc gifts; +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra…
nonopolarity
  • 146,324
  • 131
  • 460
  • 740
7
votes
2 answers

Arithmetic overflow error converting IDENTITY to data type tinyint

I am designing table in SQL Server 2008 R2 SP2 Express database. I created table with nvarchar column for data and tinyint column for auto-incrementing identity assuming there will be no more than few rows (that's why I choose 0-255 tinyint). When I…
Ondřej
  • 1,645
  • 1
  • 18
  • 29
7
votes
2 answers

MYSQL & innoDB alter dynamically AUTO_INCREMENT of a table

I have a problem, for example in my system I have the next table: CREATE TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `amount` FLOAT NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; -- is more complex table With…
fitorec
  • 1,022
  • 12
  • 10
1 2 3
…
99 100