Tag Info

Hot answers tagged

21

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


8

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


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

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


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


7

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


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

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


6

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


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


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

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


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

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


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

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


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

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

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

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

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


3

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


3

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


3

I'm having a very difficult time thinking of any possible misbehaving mechanism inside MySQL or InnoDB that could cause this behavior as the result of a bug, without throwing errors or causing a crash. It seems much more likely that the table is being updated by the application. Instead of writing new rows, something is finding and updating old ones. If ...


3

The sequence will return values in order, whether or not they exist in the table. Your code will need to defend against that. Any method to defend against possibly already-in-use serial values will really hurt concurrency - and if your DB is big enough that you're wrapping int4 serial columns, you care about concurrency. Most people just use bigint keys and ...


3

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


3

Because the value is incremented outside the scope of the transaction. This is the way you want it to work, so that separate transactions can happily insert rows or roll back without waiting for each other. If you care about gaps or want different behavior, stop using the built in and roll your own...



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