• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

drop index during the time we clone a MySQL table.

Dear all,

Any way to drop the index during the time we clone a table by :


CREATE TABLE IF NOT EXISTS Table B LIKE Table A

Open in new window


which B will copy everything from A when this command execute and not need extra AFTER script manual method?
0
marrowyung
Asked:
marrowyung
  • 6
  • 2
2 Solutions
 
PortletPaulCommented:
This isn't possible. This is what the LIKE option does according to documentation:
Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:
You will have to follow-up with subsequent commends (as already provided) to remove any auto-increments and primary key.

forgot the url:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
0
 
marrowyungAuthor Commented:
so I have to drop manually anyway ?
0
 
PortletPaulCommented:
manually? that is up to you.

what you cannot do is expect there to be some magic parameter to

             create table audit.x like source. x with magic drop unwanted bits

that magic you have to provide, which "might" be scripted - but again that is up to you.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Chris StanyonCommented:
String the queries together:

CREATE TABLE IF NOT EXISTS TableB LIKE TableA; ALTER TABLE TableB DROP yourColumn;

Open in new window

If it's just the INDEX you want to drop and not the Column:

DROP INDEX `PRIMARY` ON TableB;

Open in new window

0
 
marrowyungAuthor Commented:
ChrisStanyon,

This means this logic is not automated, I can't use dynamic SQL for it, build a lot recently and see if anything we can check from schema level and, if index found, constraint is found, drop it.
0
 
marrowyungAuthor Commented:
0
 
marrowyungAuthor Commented:
If this is the table structure:

CREATE TABLE `YYYY` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `CRON_EXPRESSION` varchar(120) NOT NULL,
  `TIME_ZONE_ID` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `QRTZ_CRON_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


Then after I clone the table using "like", the just issue:

DROP INDEX `PRIMARY` ON YYYY; 

Open in new window


I read this :
stackoverflow.com/questions/3798524/mysql-dropping-all-indexes-from-table?s=ca15d66a-5c5f-459f-80f5-58430b24626e#new-answer

can the SHOW INDEX FROM <table thing> plugable to the cursor and let we loop that one by one ?
0
 
marrowyungAuthor Commented:
0
 
marrowyungAuthor Commented:
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now