Tag Info

New answers tagged

0

this is exactly what you are facing: test=# CREATE SEQUENCE seq_a; CREATE SEQUENCE test=# SELECT nextval('seq_a'); nextval --------- 1 (1 row) test=# BEGIN; BEGIN test=# SELECT nextval('seq_a'); nextval --------- 2 (1 row) test=# ROLLBACK; ROLLBACK test=# SELECT nextval('seq_a'); nextval --------- 3 (1 row) a sequence makes ...


0

The reason we specify keys for a table is primarily to improve the data integrity and usefulness of the data. Keys guarantee the table is free from duplicate data and therefore they allow the user/consumer of the data to identify information correctly. DBMS query optimizers and storage engines are designed to take advantage of keys so having a key will also ...


1

Having a primary key per se will not speed up queries. Primary key constraints are usually accompanied by a unique index. If this index matches query predicates or join conditions then those queries are likely to run faster.


3

No, having a surrogate key would not speed things up. You should consider what your queries are doing, and whether your indexes are sufficient. For instance, if you are trying to find out the total sales for Widgets in Timbuktu on Christmas Day, then do you have an index on ProductID, StoreID, TransactionDate that also includes the SalesAmount? If not, how ...


0

Auto increment values are assigned sequentially. In the event there is a rollback or deadlock you will find gaps in the auto inc value. The auto inc is always assigned previous_auto_inc_value+1. If a value is manually bumped you will get another gap in ids according to this rule. If there are gaps you could manually specify a value for a "missing" id but ...


0

Based on your new structure, the auto_id field holds the auto_increment value from where mysql would fetch the "next" if we need it. The value you want as the auto_increment value can be got using: SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='table name'; You can directly do SET NEW.seqno = SELECT ...


0

# Create Temp Table for UserMst with New Keys CREATE TABLE UserMst_New LIKE UserMst; ALTER TABLE UserMst_New ADD OldUserID MEDIUMINT NOT NULL AFTER UserID; ALTER TABLE UserMst_New ADD UNIQUE INDEX (UserID); INSERT INTO UserMst_New (OldUserID,UserName,CreatedOn) SELECT UserID,UserName,CreatedOn FROM UserMst ORDER BY CreatedOn; # Create Temp Table for UserDet ...



Top 50 recent answers are included