35

A couple caveats I'd like to point out when using LAST_INSERT_ID: I know you mentioned single-row inserts. But when doing multiple-row inserts, LAST_INSERT_ID() will return the value of the first row inserted (not the last). If the insert failed, LAST_INSERT_ID() would be undefined. The same is true for automatic rollbacks of transactions (due to errors). ...


29

You can reset the identity value by DBCC CHECKIDENT('tableName', RESEED, 0) So next time you insert into TableName, the identity value inserted will be 1. When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case. Now when you truncate the table, it will reset the ...


24

Without seeing code, it is pretty hard to say conclusively what is happening. Although, most likely the IDENTITY value is being cached, causing gaps in the value after SQL Server is restarted. See https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server for some good answers and info about that. A simple INT ...


22

Kin has shown you how you can reset the IDENTITY value, but outside of a development environment when you're really removing all of the data, why do you need to do this? I hope you are not intending to maintain a contiguous sequence of IDENTITY values when you are in production. And I hope you aren't really writing your code to hard-code the IDENTITY ...


19

phpmyadmin Perhaps you could just select the phpMyAdmin Operations tab: In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value Click on the Operations Tab In the Table Options box find the auto_increment field. Enter the new auto_increment starting value Click on the Go button for the Table Options box. Since this one of ...


18

Nothing is wrong with your table definition. (Except hat I would use jos_content_id or something instead of the non-descriptive column name id. And I probably would use text instead of varchar(50). Your INSERT statement is the problem. With your id column defined as serial, you shouldn't insert manual values for id. Those may collide with the next value ...


18

Apparently you inserted rows into that table without using the sequence and that's why they are out of sync. You need to set the correct value for the sequence using setval() select setval('context_context_id_seq', (select max(context_id) from context)); Then the next call to nextval() should return the correct value. If the column is indeed defined as ...


17

That is, when table row is deleted, it's PK must be reused in subsequent inserts. What universe is your lecturer from?? That is grossly inefficient. If you try to do that, you will cut your performance prospects down by a factor of 10. If you need gapless numbers for auditing reasons, build them explicitly, not directly from database tools. And never ...


15

If you want to do this in PGAdmin, it is much easier than using the command line. It seems in PostgreSQL, to add a auto increment to a column, we first need to create a auto increment sequence and add it to the required column. I did like this. 1) Firstly you need to make sure there is a primary key for your table. Also keep the data type of the primary ...


13

You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator. If you try to insert a value of NULL or 0 or DEFAULT, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator. So, it's fine to INSERT INTO table1 SELECT * ...


13

The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows. First off, some test data to play with: CREATE TABLE dbo....


12

Here's what the MySQL 5.5 documentation says: The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other ...


12

This is by design - all DBMS act this was with auto-increment columns. If they did not external referential integrity could be damaged. For a simple example of this, imagine you are storing URLs for a shortening service using an auto-increment column as the key. You don't know if the shortened URL has been given out to anyone yet, and the database certainly ...


11

As a supplement to the other answer(s), I'd prefer a more visual approach: Click the table you want to change. Click Operations: Look under "Table options": Also note that:  –§–  You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less ...


11

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as `firstname`, `user`.`lastname` as `lastname`, `user`.`lang` as `lang`, `user`.`name` as `user_name`, `group`.`name` as `group_name` from `user` inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`) left join `group` on (`group`.`...


11

If it always needs to start with 51 then you can use a sequence (or identity) that starts at 510,000,001. CREATE TABLE #Test ( ID BIGINT IDENTITY(510000001, 1), SomeColumn VARCHAR(10) ); INSERT INTO #Test (SomeColumn) VALUES ('Test'), ('Test2'); SELECT * FROM #Test AS t; Just keep in mind that once the sequence reaches 519,999,999, it will not ...


10

I assume you are asking about columns with the AUTO_INCREMENT property. No, deleted rows is not the only reason, there are several others: inserts that did not succeed (due to duplicate unique or primary key violations or other reasons). inserts inside transactions that did not commit and were rolled back. inserts where the id was explicitely defined (and ...


10

You can use DBCC CHECKIDENT to reseed the IDENTITY column. Here is a sample you can run: SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.foo(ID INT IDENTITY(1,1)); GO INSERT dbo.foo DEFAULT VALUES; GO 100 -- note: set it to ([the next value you want] - 1) DBCC CHECKIDENT(N'dbo.foo', RESEED, 499); GO INSERT dbo.foo DEFAULT VALUES; GO 3 SELECT ID FROM ...


9

Going backwards just feels wrong to me. With only two data centers you could also implement identity ranges. Unless you cycle through identity values at an alarming rate, there is no reason you can't have: -- Data center 1 CREATE TABLE dbo.Table ( ID INT IDENTITY(1,1) PRIMARY KEY -- , ... ); -- Data center 2 CREATE TABLE dbo.Table ( ID INT IDENTITY(...


9

This is not unusual and there are a couple of causes. Sometimes it is due to optimisations the query runner makes to reduce contention issues with the counter resource, improving efficiency when there are concurrent updates to the affected table. Sometimes it is due to transactions that got explicitly rolled back (or implicitly rolled back due to ...


9

Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are: Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much prefer this method as it gives you complete control over the Table that is being created, such as including the auto-increment column and specifying that it be the ...


9

You can tell the tool, in this case SQL Developer, to do it for you. I talk about how to set this up here https://www.thatjeffsmith.com/archive/2014/02/auto-replace-in-oracle-sql-developer/ But basically, you define a code template in the preferences with the code you want to be recognized as a template, and then the code you want inserted. If there is ...


8

Well it does now - Oracle 12c introduced IDENTITY columns, see: Identity Columns in Oracle Database 12c Release 1 (12.1) e.g. CREATE TABLE identity_test_tab ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(30) );


8

Try temporary disabling foreign keys (make sure no ones allowed to update the db meanwhile): create table t1 (id int not null primary key) engine = innodb; create table t2 (id int not null primary key ,t1_id int not null , constraint abc foreign key (t1_id) references t1 (id) ) engine =...


8

Reusing an identity value, should in general be discouraged. Either the value is used entirely internally, in which case it’s actual value is immaterial, or it is also used externally in which case reusing the value is very likely going to lead to a misidentification. Take the obvious case of an invoice or purchase order number, these might easily come ...


7

Some problems that may rise with with this setting: Following the link in @Martin Smith's comment, negative values in an identity column may cause issues with some applications: Why database designers do not make IDENTITY columns start from the min value rather than 1? Another issue is not related to the values being negative but being decreasing, and if ...


7

It won't cause problems in that SQL Server lets you do it: create table decrement( id integer identity(0,-1), test int ) insert into decrement (test) select number from numbers select top 10 id, test from decrement order by id asc go id test ------------ -5103 5110 -5102 5109 -5101 5108 -5100 5107 -5099 5106 -5098 5105 -5097 5104 -5096 ...


7

Keys are for identification and data integrity. A key defines how tuples (rows) in a table can be uniquely identified. The integrity of keys is assured because the DBMS prevents users from entering duplicate information into the table. Database users can therefore rely on the keys to identify in the real world the things recorded in the database. A "primary"...


7

To expand further on point number 2 in the answer given by DTest: On the versions of MySQL that I have used, it is a good idea to explicity reset the value of LAST_INSERT_ID prior to each block of code where you plan to perform an insert. This can be done like so: -- initialize the LAST_INSERT_ID to some flag value: SELECT LAST_INSERT_ID( ...


6

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value: create sequence my_sequence_name; create table foo ( id numeric(20,0) ...


Only top voted, non community-wiki answers of a minimum length are eligible