auto-increment

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

I acquired a database from another developer. He didn't use auto_incrementers on any tables. They all have primary key ID's, but he did all the incrementing manually, in code. Can I turn those into Auto_incrementers now? ...

Alternative to "PDO::lastInsertId" / "mysql_insert_id"

I always hear that using "lastInsertId" (or mysql_insert_id() if you're not using PDO) is evil. In case of triggers it obviously is, because it could return something that's totally not the last ID that your INSERT created. $DB->exec("INSERT INTO example (column1) VALUES ('test')"); // Usually returns your newly created ID. // However w...

Autoincrement in Oracle

What are the other ways of achieving autoincrement in oracle other than use of triggers? ...

JavaDB: Is it possible to change auto-increment offset on existing table?

Is it possible to change the auto-increment offset on a pre-existing table with JavaDB? I'm having a problem where inserting new records usually (but not always) fails with an error complaining about using an existing key (my auto-increment column). To populate this database, I took a dump from another database (MySQL) and used a Jav...

Linq to sql error with identitiy increment field

I've just started to use linq to sql and have run into a problem with inserting a record with an auto incrementing field. I have created a new instance of a company object defined by linq. it has initialised an auto incrementing field 'companyID' to 0. InsertOnSubmit() fails with the following invalidOperationException. Incorrect au...

Can you access the auto increment value in MySQL within one statement?

Hi! I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field. What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'....

AutoIncrement fields on databases without autoincrement field

In MS Sql Server is easy create autoincrement fields. In my systems I stopped to use autoincrement fields for primary keys, and now I use Guid's. It was awesome, I've got a lot of advantages with that change. But in another non-primary key fields, I really was needing implement a "soft autoincrement". It's because my system is DB indepen...

Reset auto-incrementing column

I recently added Items to an ID and the the table got messed up in the transfer process so I deleted the Items from the table. Upon reentering the data instead of the ID starting at one it now starts at 332. I would like to have the table start at one instead of 332. I've removed the data from the data so it's clear. How do I reset the I...

Auto Increment In rails

Hi ,By mistake I have removed the autoincrement option from id field of my table.Can any one tell me ,how can i reinsert the option of autoincrement in table through migration. ...

Can you use auto-increment in MySql with out it being the primary Key

I am using GUIDs as my primary key for all my other tables, but I have a requirement that needs to have an incrementing number. I tried to create a field in the table with the auto increment but MySql complained that it needed to be the primary key. My application uses MySql 5, nhibernate as the ORM. Possible solutions I have though...

auto_increment by group

Is there a way with MySQL (5.0 specifically) to have an auto_increment field who's value is based on a grouping column? Example: id name group_field 1 test 1 2 test2 1 1 test3 2 2 test4 2 1 test5 3 2 test6 3 I'd like to not have to go through any 'crazy' methods to achive this, but will if necessary. ...

Scoped/composite surrogate keys in MySQL

Here's an excerpt of my current database (changed the table-names for an easier understanding): Pet(ownerFK, id, name, age) Owner(id, name) Where id is always a surrogate key, created with auto_increment. I want to have the surrogate key Pet.id to be "scoped" by Pet.ownerFK or in otherwords, have a composite key [ownerFk, id] as my m...

Use current auto_increment value as basis for another field in the same INSERT query - is this possible in MySQL?

In my table (MySQL) I have these fields: id - primary, auto_increment hash - base 36 representation of id I'd like to populate both fields at once, using a stored procedure to compute the base 36 equivalent of the value id receives on INSERT. Something like this: INSERT into `urls` (`id`,`hash`) VALUES (NULL,base36(`id`)); Obviou...

SQL Server: Retrieve auto-incremented ID inside a stored procedure?

My database has a parent table with an auto-incrementing primary key identity 'ID', and a normal 'TIMESTAMP column'. I have child tables with a foreign key that refer to the parent 'ID' column. I want to write a stored procedure that inserts a new column into both the parent and child databases. How would I set the child 'ID' column to...

Can one rely on the auto-incrementing primary key in your database?

In my present Rails application, I am resolving scheduling conflicts by sorting the models by the "created_at" field. However, I realized that when inserting multiple models from a form that allows this, all of the created_at times are exactly the same! This is more a question of best programming practices: Can your application rely on ...

MySQL current_insert_id()? (Not last_insert_id())

Hi All, I am inserting a row with a char column for a hash based on (among other things) the row's auto id. I know I can insert it, fetch the insert_id, calculate the hash, and update it. Does anyone know of a way to do this in a single query? You would need the rows insert_id at the time of insert. Is that completely impossible, ...

Is it possible to have an AUTO_INCREMENT column that permits duplicates?

I have a table that has an AUTO_INCREMENT field. Currently, it is also a PRIMARY KEY. However, there are situations where I need this AUTO_INCREMENT column to permit duplicates. In other words - two different rows can have the same value inside the AUTO_INCREMENT column. This would mean having an AUTO_INCREMENT field that is not a PR...

Get Auto Increment value with MySQL query

I currently have a database with over 6 million rows and growing. I currently do SELECT COUNT(id) FROM table; in order to display the number to my users, but the database is getting large and I have no need to store all of those rows except to be able to show the number. Is there a way to select the auto_increment value to display so t...

Make one ID with auto_increment depending on another ID - possible?

I want to make a small ticket-system for a project server which has several projects. So far the TicketID will be counted globally, i.e. there is a project A and a ticket with TicketID 1 and another project B and the ticket for this project will get TicketID 2 - like this: (TicketID, ProjectID) (1, 1) (2, 1) (3, 1) (4, 2) (5, 2) (6, 3) ...

MySQL table incrementing by 10 for some reason

The id field in a mysql table is incrementing by 10 (11, 21, 31) for some reason. Here is the table definition: CREATE TABLE `clients` ( `id` int(11) NOT NULL auto_increment, `first_name` varchar(255) default NULL, `last_name` varchar(255) default NULL, ) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8; If I do a simple in...