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

  • EFI PXE grub2 Howto guide for Linux EFI PXE Booting on Debian, Mint, Ubuntu, RHEL
  • Aruba/HP/Dell IAP Wireless Controller Common Default Passwords
  • Debian, Mint Ubuntu how to remove package and associated config files
  • Linux Grub not booting the intended kernel solution in Debian, Mint, Ubuntu how to specify which kernel to boot by default
  • QEMU KVM Keyboard Problems Not Working Right Repeating Characters, Ctrl+C Copy and Paste not working right when using PS2 mouse in guests Solution
  • Linux how to compile binary with static sharedobjects embedded instead of dynamic to use on multi-distributions and avoid glibc compatiblity issues
  • /bin/sh: msgfmt: not found error solution on Linux Compilation Ubuntu Debian Mint Centos
  • Mikrotik RouterOS CHR/ISO Basic and Quick Setup Howto Guide
  • qemu 4 compilation options
  • CentOS 7 8 PXEBoot Netinstall Not Working Solution "Pane is dead "new value non-exisetnt xfs filesystem is not valid as a default fs type"
  • CentOS 6 EOL yum repo won't work Error: Cannot find a valid baseurl for repo: base Solution
  • CentOS 7 8 How To Disable SELinux
  • Wordpress How To Add Featured Image To Post in Hueman Theme
  • kdenlive full reset how to erase all config files
  • CentOS 7 8 yum error Trying other mirror. To address this issue please refer to the below wiki article
  • Microsoft Teams Linux - Calendar Doesn't Work Missed Meetings!
  • Scanner not working in Linux Ubuntu Fedora Mint Debian over the network? Use sane-airscan!
  • How To Boot, Install and Run Windows 2000 on QEMU-KVM
  • bash cannot execute permission denied
  • Huion and Wacom Tablets How To Install in Linux Mint / Ubuntu and make the stylus work properly