0

I have several table(s) in mysql as follows :

CREATE TABLE UserMst (
  UserID   mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  UserName varchar(20) NOT NULL,
  CreatedOn timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (UserID)
) ENGINE=InnoDB;

CREATE TABLE UserDet (
  ID mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  UserID mediumint(8) unsigned DEFAULT NULL,
  PRIMARY KEY (ID),
  KEY FK_UserDet_UserMst_UserID (UserID),
  CONSTRAINT FK_UserDet_UserMst_UserID FOREIGN KEY (UserID) REFERENCES UserMst (UserID) ON DELETE NO ACTION ON UPDATE CASCADE,
) ENGINE=InnoDB;

"UserMst" table has "UserID" as a primary key with auto increment and forginekey relation with "UserDet" with update casecade.

UserMst table has about 200000+ records and UserDet has 20000000 records in it. So now I want to reorder "UserMst" table based on "CreatedOn" field. How Do I do this without dropping relation between both tables, any idea?

Thanks

Manish Sapkal
  • 5,591
  • 8
  • 45
  • 74
  • If by reorder you mean "change the order the data is stored in the table", you can't AFAIK. And it's useless, BTW. Use `ORDER BY` when querying. – xlecoustillier Nov 04 '14 at 13:07
  • Use `order by Primary_key_of_ur_table desc` at the end , you can do it for primary key ALSO – Pratik Joshi Nov 04 '14 at 13:11
  • @X.L.Ant I want to update that records with new value starting with 1, because many records are deleted, and I want to re-order that. This is looks like silly, but still I want to do that. :) – Manish Sapkal Nov 04 '14 at 13:28
  • Oh ok, now I understand. See http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key – xlecoustillier Nov 04 '14 at 13:37

0 Answers0