Mysql – phptheadmin/ thesql how to copy a table with all its constraints

MySQLPHPphpmyadmin

I tried to copy a table which is connected to another table through a foreign key using

$query = "CREATE TABLE table1 AS SELECT * FROM table 2";

but when I run this query it copies every thing but the constraints are not copied.

Is there any way I can run a script through phpmyadmin or write a function that preserve the constraints and the references ?

Best Answer

Within phpMyAdmin, if you go to the table you wish to copy and click the "Operations" tab, there should be a section "Copy table to (database.table)"

Here you will be able to copy the table with the same constraints as the table you are trying to copy.

Related Solutions

MySQL: Delete a record from all tables in one shot

The automated way of doing so is by defining foreign keys on your tables, with a DELETE CASCADE in your case. This way, a DELETE on the master table will propagate and delete matching rows from all derived tables.

You will have to use InnoDB tables for that.

I assume your tables do not have foreign keys at the moment, otherwise you wouldn't ask. In which case you may try using triggers, see this post for an example. Triggers are bad for performance, mind you.

Those who do not define foreign keys tend to work out all DELETEs in application code.

MySQL – How to Permanently Enable Event Scheduler

This is quite an easy one really (one of those "Doh" moments :-) ).

You set it in my.cnf as described here:

[mysqld]
..
.. other_stuff
..
event_scheduler=on
..
.. more_stuff
..

(or my.ini if you're running Windows as pointed out below)

From the MySQL documentation here, you have:

When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process, and its state is represented as shown here:

A daemon process is one that runs in the background as described here. Also known as a service in Windows

So, run:

mysql> SHOW PROCESSLIST\G

and there should be an entry like this:

*************************** 2. row ***************************
     Id: 2
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon      <<<<<====== Note: Daemon!!!
   Time: 3
  State: Waiting for next activation
   Info: NULL
2 rows in set (0.00 sec)

Best Answer

Related Solutions

MySQL: Delete a record from all tables in one shot

MySQL – How to Permanently Enable Event Scheduler

Related Question