Tag Info

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

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

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

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

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

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

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

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

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

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

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

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

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

You allude to tutorials but provide no example. Consider the following two tables that have what I understand to be an "id key" (noting the question has the auto-increment tag) and similar to many found in the wild: CREATE TABLE Books1 ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, isbn CHAR(13) NOT NULL ); INSERT INTO Books1 (isbn) VALUES ...

You could use a trigger. The key problem there though is that it will have to do a lookup and so multiple concurrent inserts into the table may get messy (concurrent being within the life of the transaction). A real alternative here is probably going to go with a natural candidate key (multiple columns of actual rather than artificial data) and add a ...

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

What is interesting about this situation can be solved using the MyISAM storage. I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site CREATE TABLE site_workorder_seq ( SiteID int not null, ...

This would be expected behavior in a few scenarios that come to mind: If you you ROLLBACK transactions where you have inserted records into the table, the auto_increment values transiently assigned to those rows you ultimately did not COMMIT the insertion of will not be reused. If you are using INSERT IGNORE and you have another unique key (in addition to ...

I try to insert a record with null sample_id `sample_id` int unsigned NOT NULL, The sample_id can't be null. You will probably find NEW.sample_id = 0 inside the trigger if you don't specify a value on insert, since you have declared the column as NOT NULL... so you need to test for that instead: IF NEW.sample_id = 0 THEN ... ...or change your ...

No, you can't. Not out of the box. Possible workarounds: Triggers (an AFTER INSERT trigger). Disadvantages: Plain horror. Comes with all the other disadvantages of triggers, like maintenance and debugging nightmares. Advantages: You can have FOREIGN KEY constraints that reference this column. You can update the first and the second id ...

I reached the max possible value of an int coloumn. Problem resolved by altering table and setting big int than int. The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215 Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

In Informix, a SERIAL column is a 4-byte signed integer with some auto-increment properties. If you insert the value 0 into a SERIAL column, or fail to provide a value for the serial column, the next number in ascending order will automatically be assigned for this row. In a programming language such as ESQL/C, you can retrieve the value inserted from the ...

Both variables have global and session values. So it's very likely you only changed the session's value which was gone when you closed the MySQL Workbench. Another caveat to pay attention to, is that these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server. If the global value of either variable is set, its ...

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant: create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly): SELECT MIN(ID) AS ...