Tag Info

Hot answers tagged

22

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). ...


22

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 http://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 ...


14

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 ...


12

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 ...


10

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 ...


9

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 ...


7

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 ...


7

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 ...


6

The @@ prefix will modify settings in a session scope. Try: SET GLOBAL auto_increment_increment=1;


6

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 ...


6

In fact the AUTO_INCREMENT attribute is not limited to the PRIMARY KEY (any more). It used to be so in old versions - definitely 3.23 and probably 4.0. Still the MySQL manual for all versions since 4.1 reads like this There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. So you can indeed have ...


6

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 ...


6

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 ...


6

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 ...


6

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) );


6

The main task to do is to find the root cause why the current value is that high. The most reasonable explanation for SQL Server versions prior to SQL2012 -assuming you're talking about a test database- would be that there was a load test followed by a cleanup. Starting with SQL2012 the most probable reason is due to several restarts of the SQL Engine (as ...


6

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 ...


5

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( ...


5

I don't think you can get the new value of auto_id in a BEFORE insert trigger. This will do what you want (if I have understood correctly): CREATE TRIGGER innodb_seqno_trigger BEFORE INSERT ON my_table FOR EACH ROW BEGIN SELECT MAX(seqno) INTO @newseqno FROM my_table WHERE id = NEW.id; SET NEW.seqno = COALESCE(@newseqno + 1, 1); END; ...


5

Why would you want to have an auto_increment column that is not the primary key? If you want a column to be an auto_increment, by definition, you are not storing meaningful data in that column. The only case where storing non-meaningful information makes sense is the special case that you want to have a synthetic primary key. In that case, the lack of ...


5

You don't need composite keys to enforce your referential integrity in your case. The reason is that you have a pretty straight-forward three tier hierarchy: PATIENT + | ^ TEST + | ^ SAMPLE Your SAMPLE table just needs a simple foreign key to your TEST table and your TEST table just needs a simple foreign key to your PATIENT table. ...


5

I don't use phpPgAmin. To change an existing column definition to use the CYCLE attribute you need to understand that this is an attribute of the sequence not the "column". A serial or bigserial is only a shorthand notation to assign a default value to a column which is take from a sequence. When you define a column as serial Postgres automatically ...


4

With MyISAM, there's a neat trick you can do: CREATE TABLE IF NOT EXISTS test ( id INT NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, PRIMARY KEY (name, id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; With the above PRIMARY KEY, if all users have different name, they will be given id = 1. If another user comes and chooses ...


4

two options: Use the "datatype" SERIAL or create a sequence and use this sequence as a default value for your integer: CREATE SEQUENCE your_seq; CREATE TABLE foo( id int default nextval('your_seq'::regclass), other_column TEXT ); INSERT INTO foo(other_column) VALUES ('bar') RETURNING *;


4

This is an interesting question because different databases have unique approaches for providing auto_increment. MySQL : Only one auto_increment key is generated to uniquely identify a row in a table. There is not a lot of explanation behind why, but just implementation. Depending on datatype, auto_increment values are fixed by the length of datatype in ...


4

So you just need to fetch the last auto-increment value that was inserted? There are a couple of ways to do this, but they're all pretty simple. Query: SELECT LAST_INSERT_ID() php mysql: $id = mysql_insert_id($mysql_conn); http://php.net/manual/en/function.mysql-insert-id.php php mysqli: $id = $mysqli->insert_id; ...


4

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) ...


4

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 ...


4

There are many things that can cause gaps in an IDENTITY column (rollbacks, deletes), but in this case due to the jump I suspect it is this bug - caused by the changes to IDENTITY with the introduction of SEQUENCE: Connect # 739013 : Failover or Restart Results in Reseed of Identity So I bet that if you look in SQL Server's error logs, the rows ...


4

Identity values are not affected by ROLLBACK operations, so if you insert rows in your table and then issue a ROLLBACK, the identity column will be incremented anyway. Even if your UNIQUE constraint causes the INSERT commands to fail, the identity values are consumed anyway. Example: IF OBJECT_ID('tempdb..#test_identity') IS NOT NULL DROP TABLE ...



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