Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-10-27
14
Medium Priority
?
256 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

572 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