I&#39;ve deleted some records from a table in a SQL Server database. 

The IDs in the table look like this:

&gt; 99
&gt; 100
&gt; 101
&gt; 1200
&gt; 1201...

I want to delete the later records (IDs &gt;1200), then I want to reset the auto increment so the next autogenerated ID will be 102. So my records are sequential, Is there a way to do this in SQL Server?

Reset AutoIncrement in SQL Server after Delete

I have been using int(10) and just noticed that Wordpress uses bigint(20) - What is different to use bigint(20) and int(10) for id auto increment? Which one should I use for id column?

    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

Vs
    
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

Thanks.

MySQL: bigint Vs int

I need to insert a long row with 32 fields into a MySQL table.

I&#39;d like to do something like this:

    $sql=&quot;insert into tblname values (... 32 fields ...)&quot;;

Obviously it works fine if the fields are in the same order as the MySQL table fields. But, my table has an auto-increment id as it&#39;s first field.

What I want is to fill in all table names but the first (id) one.

Suggestions?

PHP MYSQL - Insert into without using column names but with autoincrement field

A co-worker just made me aware of a very strange MySQL behavior.

Assuming you have a table with an auto_increment field and another field that is set to unique (e.g. a username-field). When trying to insert a row with a username thats already in the table the insert fails, as expected. Yet the auto_increment value is increased as can be seen when you insert a valid new entry after several failed attempts.

For example, when our last entry looks like this...

    ID: 10
    Username: myname

...and we try five new entries with the same username value on our next insert we will have created a new row like so:

    ID: 16
    Username: mynewname

While this is not a big problem in itself it seems like a very silly attack vector to kill a table by flooding it with failed insert requests, as the MySQL Reference Manual states:
 &gt;&quot;The behavior of the auto-increment mechanism is not defined if [...] the value becomes bigger than the maximum integer that can be stored in the specified integer type.&quot;

Is this expected behavior?

Why does MySQL autoincrement increase on failed inserts?

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:

&gt;     ERROR: sequence must have same owner as table it is linked to.

Does anyone know how to fix this issue?  How do I add or create an auto-incrementing primary key in PostgreSQL without recreating the table?

How to set auto increment primary key in PostgreSQL?

Wondering if there is a shorthand version to insert a new record into a table that has the primary key enabled? (i.e. not having to include the key column in the query)
Lets say the key column is called ID, and the other columns are Fname, Lname, and Website

    $query = &quot;INSERT INTO myTable VALUES (&#39;Fname&#39;, &#39;Lname&#39;, &#39;Website&#39;)&quot;;

PHP mySQL - Insert new record into table with auto-increment on primary key

I&#39;m new at postgres (and at database info systems all in all). 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 null
    );

    create user www with password &#39;www&#39;;

    grant select on cities to www;
    grant insert on cities to www;
    grant delete on cities to www;

    grant select on reports to www;
    grant insert on reports to www;
    grant delete on reports to www;

    grant select on cities_id_seq to www;
    grant insert on cities_id_seq to www;
    grant delete on cities_id_seq to www;

    grant select on reports_id_seq to www;
    grant insert on reports_id_seq to www;
    grant delete on reports_id_seq to www;

When, as the user www, trying to:

    insert into cities (name) values (&#39;London&#39;);

I get the following error:

    ERROR: permission denied for sequence cities_id_seq

I get that the problem lies with the serial type. That&#39;s why I grant select, insert and delete rights for the *_id_seq to www. Yet this does not fix my problem. What am I missing?

ERROR: permission denied for sequence cities_id_seq using Postgres

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?

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

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&#39;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 new row ID is returned in the AJAX response to update client status. It is possible that multiple clients are posting data to server at the same time. So, I have to make sure that each AJAX request get the EXACT new row ID in response.

In PHP, there is a method called ***mysql_insert_id*** for this feature.But, it is valid for race condition only if the argument is ***link_identifier*** of last operation. My operation with database is on $wpdb. How to extract the ***link_identifier*** from $wpdb to make sure mysql_insert_id work? Is there any other way to get the last-inserted-row id from $wpdb?

Thanks.

How to get last inserted row ID from WordPress database?

I have a pre-existing table, containing &#39;fname&#39;, &#39;lname&#39;, &#39;email&#39;, &#39;password&#39; and &#39;ip&#39;. 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 table definition; there can be only one auto column and it must be defined as a key

Any advice?:)

Add Auto-Increment ID to existing table?

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?


How to create id with AUTO_INCREMENT on Oracle?

I have a j2ee application using hibernate with annotation. How do I annotate the Id field in my pojo class to set it as auto increment or auto generated. and in adding the bean do I leave that field in my bean null?

Hibernate Auto Increment ID

How do I add `auto_increment` to an existing column of a MySQL table?


How to add AUTO_INCREMENT to an existing column?

I have a table Messages with columns ID (primary key, autoincrement) and Content (text).&lt;br/&gt;
I have a table Users with columns username (primary key, text) and Hash.&lt;br/&gt;
A message is sent by one Sender (user) to many recipients (user) and a recipient (user) can have many messages.&lt;br/&gt;
I created a table Messages_Recipients with two columns: MessageID (referring to the ID column of the Messages table and Recipient (referring to the username column in the Users table). This table represents the many to many relation between recipients and messages.&lt;p&gt;
So, the question I have is this. The ID of a new message will be created after it has been stored in the database. But how can I hold a reference to the MessageRow I just added in order to retrieve this new MessageID? &lt;br/&gt; I can always search the database for the last row added of course, but that could possibly return a different row in a multithreaded environment?

EDIT: As I understand it for SQLite you can use the `SELECT last_insert_rowid()`. But how do I call this statement from ADO.Net?

My Persistence code (messages and messagesRecipients are DataTables):

&lt;!-- language: lang-csharp --&gt;

    public void Persist(Message message)
    {
        pm_databaseDataSet.MessagesRow messagerow;
        messagerow=messages.AddMessagesRow(message.Sender,
                                message.TimeSent.ToFileTime(),
                                message.Content,
                                message.TimeCreated.ToFileTime());
        UpdateMessages();
        var x = messagerow;//I hoped the messagerow would hold a
        //reference to the new row in the Messages table, but it does not.
        foreach (var recipient in message.Recipients)
        {
            var row = messagesRecipients.NewMessages_RecipientsRow();
            row.Recipient = recipient;
            //row.MessageID= How do I find this??
            messagesRecipients.AddMessages_RecipientsRow(row);
            UpdateMessagesRecipients();//method not shown
        } 
 
    }

    private void UpdateMessages()
    {
        messagesAdapter.Update(messages);
        messagesAdapter.Fill(messages);
    }

How to retrieve the last autoincremented ID from a SQLite table?

For example I have a table with 2 columns, `first_name` and `last_name` with these values

    Ali           Khani
    Elizabette    Amini
    Britney       Spears
    ,...

I want to write a `select` query that generate a table like this:

    1     Ali           Khani
    2     Elizabette    Amini
    3     Britney       Spears
    ,...

Thanks for your help.



How to generate auto increment field in select query

I have a MySQL database and I am trying to find a way to export its structure only, without the auto increment values. `mysqldump --no-data` would almost do the job, but it keeps the auto_increment values. Is there any way to do it without using PHPMyAdmin (that I know it can do it)?

mysqldump - Export structure only without autoincrement

I have table like this

    table
    id Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(30) NOT NULL,

I want to increment my id field like `&#39;LHPL001&#39;,&#39;LHPL002&#39;,&#39;LHPL003&#39;`... etc.
What should I have to do for that? Please let me know any possible way.

How to make MySQL table primary key auto increment with some prefix

I have a MySQL table with a primary key field that has AUTO_INCREMENT on.
After reading other posts on here I&#39;ve noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can&#39;t be &#39;fixed&#39;.

I have:

    table: course
    fields: courseID, courseName

Example: number of records in the table: 18. If I delete records 16, 17 and 18 - I would expect the next record entered to have the courseID of 16, however it will be 19 because the last entered courseID was 18.

My SQL knowledge isn&#39;t amazing but is there anyway to refresh or update this count with a query (or a setting in the phpMyAdmin interface)?

This table will relate to others in a database.

---

Given all the advice, I have decided to ignore this &#39;problem&#39;. I will simply delete and add records whilst letting the auto increment do it&#39;s job. I guess it doesn&#39;t really matter what the number is since it&#39;s only being used as a unique identifier and doesn&#39;t have a (as mentioned above) *business* meaning.

For those who I may have confused with my original post: I do not wish to use this field to know how many records I have. I just wanted the database to look neat and have a bit more consistency.


Auto Increment after delete in MySQL

I&#39;m using MySQL&#39;s AUTO_INCREMENT field and InnoDB to support transactions. I noticed when I rollback the transaction, the AUTO_INCREMENT field is not rollbacked? I found out that it was designed this way but are there any workarounds to this? 

MySQL AUTO_INCREMENT does not ROLLBACK

I have the following table in postgres:

    CREATE TABLE &quot;test&quot; (
        &quot;id&quot; serial NOT NULL PRIMARY KEY,
        &quot;value&quot; text
    )

I am doing following insertions:

    insert into test (id, value) values (1, &#39;alpha&#39;)
    insert into test (id, value) values (2, &#39;beta&#39;)

    insert into test (value) values (&#39;gamma&#39;)

In the first 2 inserts I am explicitly mentioning the id. However the table&#39;s auto increment pointer is not updated in this case. Hence in the 3rd insert I get the error:

    ERROR:  duplicate key value violates unique constraint &quot;test_pkey&quot;
    DETAIL:  Key (id)=(1) already exists.

I never faced this problem in Mysql in both MyISAM and INNODB engines. Explicit or not, mysql always update autoincrement pointer based on the max row id.

What is the workaround for this problem in postgres? I need it because I want a tighter control for some ids in my table.

**UPDATE:**
I need it because for some values I need to have a fixed id. For other new entries I dont mind creating new ones.

I think it may be possible by manually incrementing the `nextval` pointer to `max(id) + 1` whenever I am explicitly inserting the ids. But I am not sure how to do that.

postgres autoincrement not updated on explicit id inserts

Long story short, I have a SQL file that I want to import as a `skel` style file, so this will be done repeatedly, programmatically. I can edit the SQL file however I want, but I&#39;d rather not touch the application itself.

This application uses `userid = 0` to represent the anonymous user. It also has a relevant (blank) entry in the database to represent this &#39;user&#39;. Hence, the line in my `skel.sql` looks something like this:

    INSERT INTO `{{TABLE_PREFIX}}users` VALUES (0, &#39;&#39;, &#39;&#39;, &#39;&#39;, 0, 0, 0, &#39;&#39;, &#39;&#39;, 0, 0, 0, 0, 0, NULL, &#39;&#39;, &#39;&#39;, &#39;&#39;, NULL);

The problem with this is that `uid` is a `auto_increment` field, for which, technically, `0` is an invalid value. Or atleast, if you set it to 0, you&#39;re basically telling MySQL, &quot;Please insert the next id into this field.&quot;

Now, I suppose I could put an `INSERT` then an `UPDATE` query into my SQL file, but is there a way of telling MySQL in general that yes, I actually want to insert `0` into this field?

How to force MySQL to take 0 as a valid auto-increment value

I&#39;m experimenting with new Android build system based on Gradle and I&#39;m thinking, what is the best way to autoincrease versionCode with it. I am thinking about two options

 1. create versionCode file, read number from it, increase it and write it back to the file
 2. parse AndroidManifest.xml, read versionCode from it, increase it and write it back to the AndroidManifest.xml

Is there any more simple or suitable solution? 

Has anyone used one of mentiod options and could share it with me?


How to autoincrement versionCode in Android Gradle

I am implementing a database application and I will use both JavaDB and MySQL as database. I have an ID column in my tables that has integer as type and I use the databases auto_increment-function for the value.

But what happens when I get more than 2 (or 4) billion posts and integer is not enough? Is the integer overflowed and continues or is an exception thrown that I can handle?

Yes, I could change to long as datatype, but how do I check when that is needed? And I think there is problem with getting the last_inserted_id()-functions if I use long as datatype for the ID-column.

What happens when auto_increment on integer column reaches the max_value in databases?

How can I ***reset*** the `AUTO_INCREMENT` of a field?

I want it to start counting from `1` again.




How to reset AUTO_INCREMENT in MySQL

Is there a way to have an auto_incrementing BIGINT ID for a table.
It can be defined like so

    id bigint auto_increment

but that has no effect (it does not increment automatically).
I would like to insert all fields but the ID field - the ID field should be provided by the DBMS.
Or do I need to call something to increment the ID counter?

auto increment ID in H2 database

I have an database that is rapidly filled with data we talk about 10-20k rows per day.

What is a limit of an ID with and autoincrement option? If ID is created as INTEGER then I can do max value of 2,147,483,647 for unsigned values?

But what when autoincrement goes above this? Does it all collapses? What would be solution then?

I am sure that a lot of people have big databases, and I would like to hear them.

Thank you.

What is the biggest ID number that autoincrement can produce in mysql

Let&#39;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 query?**

Currently I am running a second query to retrieve the id but this hardly seems like good practice considering this might produce the wrong result...

If this is not possible then what is the best practice to ensure I retrieve the correct id?

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

How do I set the initial value for an &quot;id&quot; column in a MySQL table that start from 1001?

I want to do an insert `&quot;INSERT INTO users (name, email) VALUES (&#39;{$name}&#39;, &#39;{$email}&#39;)&quot;;`

Without specifying the initial value for the id column.

How to set initial value and auto increment in MySQL?

In a loop in C++, I usually encounter situations to use `++` or `+=1`, but I can&#39;t tell their difference. For instance, if I have an integer

    int num = 0;

and then in a loop I do:

    num ++;

or
 
    num += 1;

they both increase the value of `num`, but what is their difference? I doubt `num++` could work faster than `num+=1`, but how? Is this difference subtle enough to be ignored? 

What is the difference between &quot;++&quot; and &quot;+= 1 &quot; operators?

How do I get the current AUTO_INCREMENT value for a table in MySQL?

Get current AUTO_INCREMENT value for any table

Is there a way to retrieve the auto generated key from a DB query when using a java query with prepared statements. 

For example, I know AutoGeneratedKeys can work as follows. 

    stmt = conn.createStatement();

    stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
    if(returnLastInsertId) {
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        auto_id = rs.getInt(1);
    } 

However. What if I want to do an insert with a prepared Statement. 

    String sql = &quot;INSERT INTO table (column1, column2) values(?, ?)&quot;;
    stmt = conn.prepareStatement(sql);
            
    //this is an error
    stmt.executeUpdate(Statement.RETURN_GENERATED_KEYS);
    if(returnLastInsertId) {
        //this is an error since the above is an error
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        auto_id = rs.getInt(1);
    } 

Is there a way to do this that I don&#39;t know about. It seems from the javadoc that PreparedStatements can&#39;t return the Auto Generated ID.

Is there a way to retrieve the autoincrement ID from a prepared statement

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 &quot;your_sequence_name&quot; does not exist

I&#39;m new to postgres :( 

I have a table `product` with `Id` and `name` field



Reset auto increment counter in postgres

I acquired a database from another developer.  He didn&#39;t use auto_incrementers on any tables.  They all have primary key ID&#39;s, but he did all the incrementing manually, in code.

Can I turn those into Auto_incrementers now?

---

Wow, very nice, thanks a ton. It worked without a hitch on one of my tables. But a second table, i&#39;m getting this error...Error on rename of &#39;.\DBNAME\#sql-6c8_62259c&#39; to &#39;.\DBNAME\dealer_master_events&#39;

make an ID in a mysql table auto_increment (after the fact)

I have a table set up that currently has no primary key. All I need to do is add a `primary key, no null, auto_increment`.

I&#39;m working with a `Microsoft SQL Server` database. I understand that it can&#39;t be done in a single command but every command I try keeps returning syntax errors.

edit ---------------

I have created the primary key and even set it as not null. However, I can&#39;t set up the `auto_increment`.

I&#39;ve tried:

    ALTER TABLE tableName MODIFY id NVARCHAR(20) auto_increment
    ALTER TABLE tableName ALTER COLUMN id NVARCHAR(20) auto_increment
    ALTER TABLE tableName MODIFY id NVARCHAR(20) auto_increment
    ALTER TABLE tableName ALTER COLUMN id NVARCHAR(20) auto_increment

I&#39;m using `NVARCHAR` because it wouldn&#39;t let me set `NOT NULL` under int

How do I add a auto_increment primary key in SQL Server database?

I generate a SQLite table (in java): 

    create table participants (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, col1,col2);

afterwards I try to add rows using the INSERT comand: 

    insert into participants values (&quot;bla&quot;,&quot;blub&quot;);

i get the error:

    java.sql.SQLException: table participants has 3 columns but 2 values were supplied

I thought the row id would be generated automatically, but it seems that I miss anything.

----
I tried another solution: 

    PreparedStatement prep = conn.prepareStatement(&quot;insert into participants values (?,?,?);&quot;);
    Integer n = null;
    prep.setInt(1,n);
    prep.setString(2, &quot;bla&quot;);
    prep.setString(3, &quot;blub&quot;);
    prep.addBatch();
    prep.executeBatch();

as result I received a null pointer exception at &quot;prep.setInt(1,n);&quot;

Do you see the fault?

All Auto Increment Solutions on Gang of Coders

Total of 35 Auto Increment Solutions