FAQ Database Discussion Community


How efficient is Last_insert_id?

php,mysql,one-to-many,auto-increment,identifier
I recently came across a problem where I needed to be able to grab the last ID of a query I've inserted on a page. The process was quite simple, but I'd like some reassurance on how the process is done. Here's how my code is: $query30 = $db->query("INSERT `mybb_quickexample`...

MySQL: How to create table with auto incrementing column via script

mysql,auto-increment,create-table
I am new to MySQL and hope someone can help me with this. I want to create a simple table with two special settings: a two-column primary key including the columns "de" and "location" an auto incrementing column "tID" that generates a unique ID, starting with 1 I tried the...

Keep sequence created from BIGSERIAL when deleting table

postgresql,sequence,auto-increment,postgresql-9.2
I have a postgres table creating with the following SQL: CREATE TABLE mytable ( mytable_id BIGSERIAL NOT NULL, mytable_char VARCHAR(8) NOT NULL ) This creates the table as well as an implicit mytable_mytable_id_seq sequence. Now, after creating 1.000.000 records, I want to split this table into partitioned tables (using inheritance)....

C# Auto Increment Primary Key with String?

c#,sql,combobox,primary-key,auto-increment
I am newbie to C# and programming... When form loading primary key has to load automatically to comboBox and it has to automatically generate +1 from last value.. for example if COUNT value is 0 then it has to add like this EMP01 Here's my code: class PrimaryKeyIncrement { public...

Difference between RETURN_GENERATED_KEYS and specifying the generated column names

java,jdbc,auto-increment
What is the difference between the prepareStatement(String sql, int autoGeneratedKeys) and prepareStatement(String sql, String[] columnNames) methods of the JDBC Connection class? The Javadoc for both indicates that the returned PreparedStatement object is capable of returning auto-generated keys if the SQL statement is an INSERT statement. In the case of the...

Make AUTO_INCREMENT reset itself on change of Parent ID

mysql,database,combinations,auto-increment
In a project assignment I have I want the one part of the PRIMARY KEY to reset after the other part changes value. For example: CREATE TABLE shopping_center( centerID INTEGER AUTO_INCREMENT, centerName CHAR(50), CONSTRAINT center_PK PRIMARY KEY(centerID)); CREATE TABLE staff( staffID INTEGER AUTO_INCREMENT, centerID INTEGER, name VARCHAR(50), CONSTRAINT staff_PK PRIMARY...

Insert row into table with only a primary key field

sql,postgresql,primary-key,auto-increment,sql-insert
Googling around told me to try this: insert into Plan default values; but I got: null value in column "id" violates not-null constraint or this: insert into Plan (id) values (null); but I got the same thing. The examples were for SQL: is there something different for PostgreSQL? Table structure:...

getDao exception with generatedId = true

java,android,auto-increment,ormlite
I want to insert record to my sqlite database without providing ids for new record. All ids should be autogenerated. So, id column is defined as: //primary key @DatabaseField(id = true, columnName = C_ID,generatedId = true) private int cID; But there is exception thrown while getting dao: Java.lang.IllegalArgumentException: Must specify...

1 auto_increment with 2 primary keys

mysql,primary-key,innodb,auto-increment,myisam
My intention is to create a table with 2 primary keys with one of those autoincrementing and the other specified when inserting and when I create a new field for this table it must start the recount if the not incremented primary key changes. This is what I had: I...

Auto Incrementing HTML Form ID's in input field

javascript,auto-increment,html-form
How does one auto-increment Id's to an HTML form so it's easier to classify? Kind of like an invoice/reference number? In other words, is it possible to create an hidden input field that would attribute a serie of numbers and also an ID automatically when the form page is loaded...

i want to reset auto increment value when i delete some rows from database table

c#,.net,sqlite,auto-increment
I want to reset increment value when i delete value from database table . Pno is primary key and here is the code what i have tried so far private void txtincr() { int a; if (txtPno.Text == "") { if (con.State == 0) { con.Open(); } string str =...

Auto-increment a value in Firebase

java,android,firebase,auto-increment
How do I auto increment a value stored in Firebase from an Android client? Currently: I declare int id = 1. When I increment, I see the values 2, 3 etc. being stored. That's fine, but when I re-run the project, id is set equal to 1 again. I want...

How to avoid a possible conflict associating data sent by a form into a MySQL database with an autoincrement unique id?

php,mysql,post,auto-increment,last-insert-id
I do not actually know much about PHP and jQuery. I am a self-taught programmer, lately I've focused on web programming with PHP for the server-side and JavaScript for client-side. AJAX is a relatively new concept for me, I'm just doing the first tests. I'll explain my problem, surely what...

Java auto increment issue

java,oop,auto-increment
I'm having trouble with this requirement. I have this snippet: private String id; private int age; private static int index; public Customer(int a) { this.id = a + "C" + index; index++; this.age = a; } It works fine. But the thing is, I want for every age the index...

Make Postgres choose the next minimal available id

database,postgresql,database-design,auto-increment,gaps-and-islands
I would like to make Postgres choose the first next available id so that no error occurs in the following case: CREATE TABLE test( id serial PRIMARY KEY, name varchar ); Then: INSERT INTO test VALUES (2,'dd'); INSERT INTO test (name) VALUES ('aa'); INSERT INTO test (name) VALUES ('bb'); This...

Auto generate and AutoIncrement ID in C# when trying to add new record to database

c#,sql-server,auto-increment
I'm using this code to select the maxID from a database table and each time I want to add a new record, the autogenerated ID is not the last one +1. public formularAddCompanie() { InitializeComponent(); try { string cs = "Data Source=CODRINMA\\CODRINMA;Initial Catalog=TrafficManager;Integrated Security=True"; string select = "SELECT max(IDCompanie) FROM...

No auto Increment when MySql Database converted to Postgres database

mysql,database,postgresql,auto-increment
In Mysql, ID is Auto Increment but when converted to Postgres there is no Auto Increment . Mysql database CREATE TABLE IF NOT EXISTS `cities` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `state_id` bigint(20) NOT NULL, `district_id` bigint(20) NOT NULL, `name` varchar(255) NOT NULL, `description` varchar(1000) DEFAULT NULL, `created_by` int(11) NOT...

Mysql replace all id's to make a correct sequence

mysql,phpmyadmin,auto-increment,increment
I was wondering if it's possible in mysql to reassign auto incremented ID's so that they will follow eachother up. Currently I have deleted rows and because of that my ID's are as follow 1 2 3 8 9 14 Is it possible to run a query that will replace...

auto_increment in Phalcon migrations system

php,mysql,auto-increment,database-migration,phalcon
For some reason, the migration system is not behaving well with auto_increment primary keys. Am I overseeing something? (I'm using phalcon 2.0.1) I have the following auto generated migration: class LanguagesMigration_100 extends Migration { public function up() { $this->morphTable( 'languages', array( 'columns' => array( new Column( 'id', array( 'type' =>...

How to Insert auto increment ID where column not auot_increment

sql,insert,auto-increment
I am trying to insert some Data in a Table where the ID is int. The Id is unique and I have no idea why the developer of this software have not set this field to auto_increment So normaly this would work, because with auto_increment I would exlude the ID...

Increment filename-number by the amount of files in directory .Net

vb.net,auto-increment,winscp-net
My program finds and lists all files located on a remote folder. The same program is able to save new files created into the same folder. I can decide the filename, but I need the program to give some sort of "ID" to each of them. I would like the...

MySQL Auto-Increment with several different user-input words

php,mysql,database,innodb,auto-increment
I'm currently working on a species cataloging code written on HTML + PHP using a MySQL InnoDB database. The code allows the user to enter data of animals and store that on the database for further search (ID(used for indexing),Specie, order, family, name - these are the fields on the...

MySQL is there a chance of accidental offset of automatic increment

php,mysql,auto-increment
If inserting into two mysql tables at the same time is there a chance that another user could insert at the same time making the tables offset? For example hypothetically: (In php) $conn = new mysqli($servername, $username, $password, $dbname); $sql = "INSERT INTO Name (user_id, name, email) VALUES (DEFAULT, 'User...

MySQL, AUTO_INCREMENT, and NOT NULL

mysql,sql,auto-increment,notnull
In a MySQL table, you can obviously set an INT as both NOT NULL and AUTO_INCREMENT, but while they both work together, is the former really necessary when you have the latter? That aside, is there any performance, memory, storage, or speed bonus for dropping NOT NULL?...

SQLite PRIMARY key AutoIncrement doesn't work

android,sqlite,android-sqlite,auto-increment
I'm trying to a have a table with an auto incremented primary key. The SQL query for table creation is included. Problem is the auto-increment does not work. Meaning when I insert a row with NULL as the value of conversation_id it just inserts null. I have this problem on...

ios/xcode/core-data: Store and retrieve unique identifier

ios,core-data,auto-increment,uniqueidentifier
IOS newb building an app to work with a website. Ultimately, I want to keep the app and backend on the website in synch. Photos are saved on the website using the ID of the item. For the app, I would also like to save the photo with a unique...

Why does perl string auto-increment magic behavior exist?

perl,auto-increment
In perl, the string "aa" can be auto-incremented to become "ab" when various specific conditions are met. PHP has similar behavior, and references Perl, which I take to mean that the earlier use is with Perl. Why was the magic behavior added to Perl in the first place? An example...

Mongoose auto increment

javascript,mongodb,mongoose,auto-increment
According to this mongodb article it is possible to auto increment a field and I would like the use the counters collection way. The problem with that example is that I don't have thousands of people typing the data in the database using the mongo console. Instead I am trying...

Can other users break into my sql sequence of operations?

sql,postgresql,concurrency,auto-increment,sql-insert
I do insert operations into the database (Postgres) and then immediately take the last serial id. If 10 people do the same thing, can they break the sequence of my database operations? INSERT INTO table VALUES (DEFAULT,...); SELECT currval(pg_get_serial_sequence('table','column')) as inserted_id; I'm afraid that may happen situation like: INSERT INTO...

Auto-increment - automatic reset for each year

mysql,innodb,auto-increment
MySQL/InnoDB In my case my receipts should be counted on yearly basis; 1/2015, 2/2015 ... 556/2015 and so on. When next year comes, the counter should start from 1 again and receipts should be counted as 1/2016, 2/2016 ... How to define auto_increment field which will reset itself on yearly...

Shifting id values of existing rows in mysql

mysql,phpmyadmin,auto-increment,alter-table,value
I have a table in a mysql database, in which every row has an id column with auto increment. The id values start at 1, and end at 639. What I need to do is change the id column of each row, so that the count doesn't begin at 1...

mysql like button table id auto_increment optimization

mysql,auto-increment,partitioning
I have this table: CREATE TABLE IF NOT EXISTS `likes` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user` varchar(40) NOT NULL, `post_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; it is for a big store, it allow customers to like products. it record who like (user)...

MYSQL Reset Auto Increment improve performance

php,mysql,innodb,auto-increment
I have a question about MYSQL databases will resetting the primary key improve performance? I use auto increment as key (NumID) see below but the count is in the 42 million mark now and lately I notice up and down performance. The database size is 8GB and there is 450,000...

why is auto-increment trigger creation in oracle failing?

oracle11g,triggers,auto-increment
Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production I am trying to create a trigger for auto-incrementation. This solution is discussed all over the web, and is pretty much conceded to be the standard auto-increment solution for Oracle. For example this Stack Overflow article seems to be...

Auto incrementing id value with string django

python,django,auto-increment
I am trying to create a model in which I have a case_id field. This field should be auto incrementing. But my case_id value should be in the format shown, case_id=FAS150001 (This should be the first value) class InformationRequest(models.Model): """Model to track information""" case_id = models.CharField(max_length=50) user = models.ForeignKey(User) information...