I'm using MySQL 5.6 (5.6.29-76.2-56-log Percona XtraDB Cluster)
When I issue a
CREATE TABLE LIKE the triggers on the tables are not copied - is it possible to get them on the new table as well?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
The long and the short answer is no! This is yet another "non-feature" of MySQL. This construct is known more commonly as CTAS (
CREATE TABLE AS.... SELECT) - the
CREATE TABLE LIKE syntax is non-standard - like so much of MySQL!
MySQL's documentation argues here - that this lacuna is to ensure "flexibility" - you'd laugh if it wasn't so sad!
CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible.
You can create them (indexes) by declaring them before the
SELECT as follows
If you want to have indexes in the created table, you should specify these before the SELECT statement:
> mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
But you're out of luck for
If this is important to you, you could try your luck with PostgreSQL - which has a table inheritance mechanism, whereby schema changes are propaged, but I can't see why a WITH
STORED PROCEDURES syntax couldn't be introduced.
If you put in a feature request (good luck with that! MySQL doesn't even have
CHECK CONSTRAINTs yet), I'd be happy to file a
"me-too" if you post the link back here for my attention.
CREATE TABLE LIKE is a similarly crippled option - from here:
CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.