Create Table with structure of another existing table

Create Table with structure of another existing table

Ok, this has many valid uses such as if you need to create backups or the ability to undo changes, you would want to create new tables with the same structure as another. Here is how you do it.
[i:7ed9581493]
Also remember Indexes such as PRIMARY KEYS [b:7ed9581493]will not be preserved[/b:7ed9581493] so you will have to readd them manually.[/i:7ed9581493]

[code:1:7ed9581493]CREATE TABLE NewName AS SELECT * FROM TableStructureYouWantToCopy;[/code:1:7ed9581493]


Another way to do the same thing (although in MySQL 3.23 it doesn't work - probably version 4+ will support it) is:

[code:1:7ed9581493]CREATE TABLE NewName LIKE TableStructureYouWantToCopy[/code:1:7ed9581493]


[b:7ed9581493]Restore PRIMARY KEY[/b:7ed9581493]

As I mentioned above you will need to recreate any Indexes/PRIMARY KEYS

Here is an example of readding a PRIMARY KEY with an AUTO_INCREMENT field:

[b:7ed9581493]First make the field NOT NULL[/b:7ed9581493]

[code:1:7ed9581493]
ALTER TABLE tableName MODIFY FieldNAME INT(18) NOT NULL;
[/code:1:7ed9581493]

[b:7ed9581493]Create The Primary Key[/b:7ed9581493]
[code:1:7ed9581493]ALTER TABLE tableName ADD CONSTRAINT PRIMARY KEY(FieldName);[/code:1:7ed9581493]

[b:7ed9581493]ADD AUTO_INCREMENT[/b:7ed9581493]
[quote:7ed9581493]ALTER TABLE tableName MODIFY FieldNAME INT(18) NOT NULL AUTO_INCREMENT;[/quote:7ed9581493]

If there is an easier way then this then please let me know :)



Tags:

existing, tablecreate, tableok, valid, backups, undo, indexes, primary, preserved, readd, manually, newname, select, tablestructureyouwanttocopy, mysql, doesn, restore, recreate, readding, auto_increment, null, alter, tablename, modify, fieldname, int, constraint,

Latest Articles

  • qemu: could not load PC BIOS 'bios-256k.bin' solution
  • Proxmox How To Custom Partition During Install
  • Hyper-V Linux VM Boots to Black Screen, Storage, NIC Not Found Issues
  • Ubuntu Mint How to Fix Missing/Broken /dev and /dev/pts which causes terminal to immediately close exit and not work
  • How high can a Xeon CPU get?
  • bash fix PATH environment variable "command not found" solution
  • Ubuntu Linux Mint Debian Redhat Youtube Cannot Play HD or 4K videos, dropped frames or high CPU usage with Nvidia or AMD Driver
  • hostapd example configuration for high speed AC on 5GHz using WPA2
  • hostapd how to enable and use WPS to connect wireless devices like printers
  • Dell Server Workstation iDRAC Dead after Firmware Update Solution R720, R320, R730
  • Cloned VM/Server/Computer in Linux won't boot and goes to initramfs busybox Solution
  • How To Add Windows 7 8 10 11 to GRUB Boot List Dual Booting
  • How to configure OpenDKIM on Linux with Postfix and setup bind zonefile
  • Debian Ubuntu 10/11/12 Linux how to get tftpd-hpa server setup tutorial
  • efibootmgr: option requires an argument -- 'd' efibootmgr version 15 grub-install.real: error: efibootmgr failed to register the boot entry: Operation not permitted.
  • Apache Error Won't start SSL Cert Issue Solution Unable to configure verify locations for client authentication SSL Library Error: 151441510 error:0906D066:PEM routines:PEM_read_bio:bad end line SSL Library Error: 185090057 error:0B084009:x509 certif
  • Linux Debian Mint Ubuntu Bridge br0 gets random IP
  • redis requirements
  • How to kill a docker swarm
  • docker swarm silly issues