Solved

clone a MySQL table without cloning key, index and auto_incremental item

Posted on 2014-10-27
14
248 Views
Last Modified: 2015-02-05
Dear all,

right now  try to clone a My SQL table using CREATE TABLE IF NOT EXISTS <table name> LIKE <source table>, but found that all index, primary key and auto increment attribute will goes to the new table too.

Any way to just clone the tabele schema with out all these?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
14 Comments
 
LVL 7

Expert Comment

by:Stampel
ID: 40406789
what happens if you use create table newtable as (select * from oldtable;);
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40407731
I don't understand this ?

select * from old table only get the data but not the schema without constraint, primary key and index.

please assume that the new table don't exist at the beginning.
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40409209
the new table dont exist and it gets created by the SQL statement.
Just wanted you to verify for the rest.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:marrowyung
ID: 40410003
so you mean you didnt' try it before and you think it works, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410026
it seems working fine.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410029
it can be use with IF NOT EXISTS  statement ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410039
but one thing, the new table has ALL DATA from the original table, I don't want the data, is it possible ?
0
 
LVL 7

Accepted Solution

by:
Stampel earned 500 total points
ID: 40410158
Then use :
create table newtable as select * from oldtable limit 0;

you will get new empty table as you wish :)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40410471
I just done a test on this schema:

CREATE TABLE `SPI_INS_CS` (
  `GROUP_SR_ID` bigint(19) NOT NULL,
  `ORDER_TYPE` varchar(3) NOT NULL,
  `SERVICE_TYPE` varchar(100) NOT NULL,
  `PRODUCT_TYPE` varchar(50) NOT NULL,
  `CUSTOMER_ID` varchar(6) NOT NULL,
  `END_CUSTOMER_ID` varchar(6) DEFAULT NULL,
  `ACCT_NO` varchar(30) DEFAULT NULL,
  `MASTER_SR` bigint(19) DEFAULT NULL,
  `STATUS` varchar(2) NOT NULL,
  `CREATED_BY` varchar(12) NOT NULL,
  `CREATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `UPDATED_BY` varchar(12) DEFAULT NULL,
  `UPDATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `IS_OBSOLETE` int(1) NOT NULL DEFAULT '0',
  `SRC_UPDATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PRODUCT_ID` varchar(3) NOT NULL DEFAULT ' ',
  PRIMARY KEY (`GROUP_SR_ID`),
  KEY `FK_CS_CUSTOMER` (`CUSTOMER_ID`),
  CONSTRAINT `FK_CS_CUSTOMER` FOREIGN KEY (`CUSTOMER_ID`) REFERENCES `SPI_INS_Customer` (`CUSTOMER_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


and the result is :

CREATE TABLE `SPI_INS_CS_clonetest` (
  `GROUP_SR_ID` bigint(19) NOT NULL,
  `ORDER_TYPE` varchar(3) CHARACTER SET utf8 NOT NULL,
  `SERVICE_TYPE` varchar(100) CHARACTER SET utf8 NOT NULL,
  `PRODUCT_TYPE` varchar(50) CHARACTER SET utf8 NOT NULL,
  `CUSTOMER_ID` varchar(6) CHARACTER SET utf8 NOT NULL,
  `END_CUSTOMER_ID` varchar(6) CHARACTER SET utf8 DEFAULT NULL,
  `ACCT_NO` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `MASTER_SR` bigint(19) DEFAULT NULL,
  `STATUS` varchar(2) CHARACTER SET utf8 NOT NULL,
  `CREATED_BY` varchar(12) CHARACTER SET utf8 NOT NULL,
  `CREATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `UPDATED_BY` varchar(12) CHARACTER SET utf8 DEFAULT NULL,
  `UPDATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `IS_OBSOLETE` int(1) NOT NULL DEFAULT '0',
  `SRC_UPDATED_DT` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PRODUCT_ID` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ' '
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open in new window


you can see that a lot of :

CHARACTER SET utf8

has been created, how is it and do it impact in any way ?
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40410579
utf8 charset is the default encoding on most softwares, there is most chance it will have no impact.
But you will have to verify it for your applications using this table.

You will find more details on MySQL charset here.
http://dev.mysql.com/doc/refman/5.5/en/charset.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40412708
ok
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40480051
Dear all,


please help to answer this :"

http://www.experts-exchange.com/Database/MySQL/Q_28570501.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40499812
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40592894
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question