I have this database table where I insert multiple results during 1 action. In php I have a submit button when pressed, inserts 1-50 rows(depending on the user input). I've got a foreign key in this table which has the same value on the rows inserted during that action(so when the primary key connected to the foreign key gets deleted, the 1-50 rows will be deleted as well). Now I've got an Auto Increment in my table and during testing, I find out that the ID's are somewhat like this: 1,2,3,200,249,460,670. So like huge differences between the ID's. Whenever I would create a new row, it will get the ID 671. I feel like it would be better if the ID would just get 4 in this case. Is there any way of doing this automatically and not typing ALTER TABLE users AUTO_INCREMENT=4; manually? If so, are there any advantages/disadvantages?
124164
For example, I have a table for user registration:  CREATE TABLE IF NOT EXISTS test_user (      user_id int(10) unsigned NOT NULL AUTO_INCREMENT,      user_name varchar(50) NOT NULL,      PRIMARY KEY (user_id),   ) ENGINE=InnoDB DEFAULT CHARSET=utf8   I want the user_id field ID numbers to be continuous, without gaps.    Failed transactions will occupy some ID numbers, is there any way around this?
104158
I wanted to get some UI design advice. I am not sure this is the proper forum to ask this question so here goes.  My situation is I have a table that has 51 bit values to hold the values of statutes.     In production, this table would have over 10,000 records for the different LocationID's.  The UI for this in its pure database for would have a page with the dozens of text boxes.  Is there a better, cleaner way to do this?  Any recommendation or links to how others have handled this situation.  Please feel free to post any additional questions if further clarification is needed.
82615
On PHP 5.3.13/MySQL 5.5.21 the following code doesn't work:  if($check_custom_fields_form!=1){     $sql = "CREATE TABLE IF NOT EXISTS ". $table_custom_fields_form ." (                 `form_name` longtext NOT NULL,                 `field_id` bigint(20) NOT NULL,                 FOREIGN KEY (`field_id`) REFERENCES $table_custom_fields (`ID`) ON DELETE CASCADE ON UPDATE CASCADE     ) CHARACTER SET utf8 COLLATE utf8_general_ci";     dbDelta($sql); } if($check_subscribe_cat!=1){     $sql = "CREATE TABLE IF NOT EXISTS ". $table_subscribe_cat ." (                 `subscribe_id` bigint(20) NOT NULL,                 `cat_id` bigint(20) NOT NULL,                 FOREIGN KEY (`subscribe_id`) REFERENCES ".$wpdb-&gt;prefix."tgt_subscription (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,                 FOREIGN KEY (`cat_id`) REFERENCES ".$wpdb-&gt;prefix."terms (`term_id`) ON DELETE CASCADE ON UPDATE CASCADE     ) CHARACTER SET utf8 COLLATE utf8_general_ci";     dbDelta($sql);  }   The  suggested a downgrade to MySQL 5.1.37 (no, thanks) or the following update:  if($check_custom_fields_form!=1){         $sql = "CREATE TABLE IF NOT EXISTS ". $table_custom_fields_form ." (                     `form_name` longtext NOT NULL,                     `field_id` bigint(20) NOT NULL,                     KEY(field_id)         ) CHARACTER SET utf8 COLLATE utf8_general_ci";         dbDelta($sql);     } if($check_subscribe_cat!=1){       $sql = "CREATE TABLE IF NOT EXISTS ". $table_subscribe_cat ." (                   `subscribe_id` bigint(20) NOT NULL,                   `cat_id` bigint(20) NOT NULL,                   KEY(subscribe_id),                   KEY(cat_id)       ) CHARACTER SET utf8 COLLATE utf8_general_ci";       dbDelta($sql);    }   Which seems like a rather dirty way to work around the problem (no cascading deletes/updates). Therefore:   Do I really have to live with that ? Is it true that dbDelta only works with foreign key in a 3 year old MySQL version?
4465
Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables.   My argument, as detailed , is that the performance difference between INT and VARCHAR is negligible, since every INT foreign key reference will require a JOIN to make sense of the reference, a VARCHAR key will directly present the information.  So, does anyone have experience with this particular use-case and the performance concerns associated with it?
11491
I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this:  ALTER TABLE tablename AUTO_INCREMENT = 1   I am curious if there is a way to do this from within phpMyAdmin.  Something like a check box to reset the auto-increment or something else along those lines?  Not that there is anything wrong with the command line approach.  More one of those curiosity things I keep thinking on...  Thanks in advance!
85836
I need to create a table like the following one:  +----+---------+---------+ | Id | Product | Version | +----+---------+---------+ |  1 |       1 |       1 | |  2 |       1 |       2 | |  3 |       2 |       1 | |  4 |       1 |       3 | |  5 |       2 |       2 | +----+---------+---------+   Where ID is an Identity(1,1) and I need the Version to be auto-populated depending on being inserted Product.  A similar effect could be achieved upon SELECT by using ROW_NUMBER() with PARTITION like this:  SELECT ID, Product, ROW_NUMBER() OVER(PARTITION BY Product ORDER BY ID) AS Version FROM Products   What are my options?
14064
My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record.  I want to copy the data from DATA_TABLE where it is linked to one given object identity and insert corresponding records into Data_Table and Link_Table for a different given object identity.  I can do this by selecting into a table variable and the looping through doing two inserts for each iteration.  Is this the best way to do it?  Edit : I want to avoid a loop for two reason, the first is that I'm lazy and a loop/temp table requires more code, more code means more places to make a mistake and the second reason is a concern about performance.  I can copy all the data in one insert but how do get the link table to link to the new data records where each record has a new id?
6535
Using PostgreSQL v9.1.  I have the following tables:  CREATE TABLE foo (     id BIGSERIAL     NOT NULL UNIQUE PRIMARY KEY,     type VARCHAR(60) NOT NULL UNIQUE );  CREATE TABLE bar (     id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,     description VARCHAR(40) NOT NULL UNIQUE,     foo_id BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT );   Say the first table foo is populated like this:  INSERT INTO foo (type) VALUES     ( 'red' ),     ( 'green' ),     ( 'blue' );   Is there any way to insert rows into bar easily by referencing the foo table?  Or must I do it in two steps, first by looking up the foo type I want, and then inserting a new row into bar?  Here is an example of pseudo-code showing what I was hoping could be done:  INSERT INTO bar (description, foo_id) VALUES     ( 'testing',     SELECT id from foo WHERE type='blue' ),     ( 'another row', SELECT id from foo WHERE type='red'  );
122704
I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. I know this is technically not possible, but I guess this is a problem others have tackled before, so I'd like to know if there is a well-known 'solution' for this issue?   What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.   This is my current setup:  CREATE TABLE `invoices` (   `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,   `invoicenumber` mediumint unsigned NOT NULL,   `branch` enum('A','B') NOT NULL,   `date` date NOT NULL,   `client` varchar(100) NOT NULL ) COMMENT='' ENGINE='InnoDB';   To check the lateset invoice, I run:  SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'
101615
I have a table like the following:  create table my_table (     id   int8 not null,     id_A int8 not null,     id_B int8 not null,     id_C int8 null,     constraint pk_my_table primary key (id),     constraint u_constrainte unique (id_A, id_B, id_C) );   And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:  INSERT INTO my_table VALUES (1, 1, 2, NULL); INSERT INTO my_table VALUES (2, 1, 2, NULL);   But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.  How can I guarantee my unique constraint even if id_C can be NULL in this case? Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?
91339
I am running the below query in ideone   CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) );  INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1235, 'J K ROLLING');     INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1236, 'WILLAM'); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1237, 'SHAKESPEAR');  --------------------------------- CREATE TABLE BOOK  ( BOOK_ID NUMBER(5) PRIMARY KEY, BOOK_NAME VARCHAR2(15), AUTHOR_ID NUMBER(5), CONSTRAINT author_fk FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID) ON DELETE CASCADE );  INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10001, 'SOFTWARE DEVELOPEMENT', 1234); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10002, 'HARRY POTTER', 1235); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10003, 'TO BE OR NOT TO BE', 1237); INSERT INTO BOOK (BOOK_ID, BOOK_NAME, AUTHOR_ID)      VALUES (10004, 'PROJECT MANAGEMENT', 1234);   After creating these entries, when I try to delete the entries in AUTHOR Table by using following command:  DELETE FROM AUTHOR WHERE AUTHOR_ID = 1234;   Select * from book ;  10001|SOFTWARE DEVELOPEMENT|1234 10002|HARRY POTTER         |1235 10003|TO BE OR NOT TO BE   |1237 10004|PROJECT MANAGEMENT   |1234   and  Select * from author ;  1235|J K ROLLING 1236|WILLAM 1237|SHAKESPEAR   the entry from parent table is deleted but their is no delete cascade effect in child table (BOOK table). Can someone help me what I am doing wrong.  Thanks,
71286
In MySQL table definitions is there a different between INT(5) and SMALLINT(5)?  Or do they both represent the same size?
46162
I need to have one column as the primary key and another to auto increment an order number field. Is this possible?  EDIT: I think I'll just use a composite number as the order number. Thanks anyways.
94051
I want to do something like this:    create table app_users (     app_user_id smallint(6) not null auto_increment primary key,     api_key     char(36) not null default uuid() );    However this results in a error, is there a way to call a function for a default value in mysql?  thanks.
31743
I am converting some module from d5 to d7, and in old code there is an update query like this:  &lt;?php db_query("UPDATE {a_table} SET count = count + 1 WHERE field1 = %d", $some_value);   As we see, it is incrementing a field value (AFAIK it is a mysql feature). But, if I use new style of update it won't work.  &lt;?php db_update('a_table')     -&gt;fields(array('count' =&gt; 'count + 1',))     -&gt;condition('field1', $some_value)     -&gt;execute();   Are there any workaround within new style of db update without running an extra query to get existing field value to increment it?
44606
I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:  CREATE TABLE [dbo].[PONumbers] (   [PONumberPK] [int] IDENTITY(1,1) NOT NULL,   [NewPONo] [bit] NOT NULL,   [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(),   CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC)     );   And a stored procedure created using this script:  CREATE PROCEDURE [dbo].[GetPONumber]  AS BEGIN     SET NOCOUNT ON;      INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1);     SELECT SCOPE_IDENTITY() AS PONumber; END   At the time of creation, this works fine. When the stored procedure runs, it starts at the desired number and increments by 1.  The strange thing is that, if I shut down or hibernate my computer, then the next time the procedure runs, the sequence has advanced by almost 1000.   See results below:    You can see that the number jumped from 8 to 1002!   Why is this happening?  How do I ensure that numbers aren't skipped like that?  All I need is for SQL to generate numbers that are:   a) Guaranteed unique.  b) increment by the desired amount.    I admit I'm not a SQL expert. Do I misunderstand what SCOPE_IDENTITY() does?  Should I be using a different approach? I looked into sequences in SQL 2012+, but Microsoft says that they are not guaranteed to be unique by default.
76368
How do you stop race conditions in MySQL? the problem at hand is caused by a simple algorithm:   select a row from table if it doesn't exist, insert it   and then either you get a duplicate row, or if you prevent it via unique/primary keys, an error.  Now normally I'd think transactions help here, but because the row doesn't exist, the transaction don't actually help (or am I missing something?).  LOCK TABLE sounds like an overkill, especially if the table is updated multiple times per second.  The only other solution I can think of is GET_LOCK() for every different id, but isn't there a better way? Are there no scalability issues here as well? And also, doing it for every table sounds a bit unnatural, as it sounds like a very common problem in high-concurrency databases to me.