phpmyadmin schema dump, how to avoid alter table for key definitions?
When I dump the mysql db schema with phpmyadmin (5.2.0), I would like to get something like this:
CREATE TABLE `MEASURE_TABLE` ( `measureID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, `sensorID` int(10) UNSIGNED NOT NULL, `timestamp` int(10) UNSIGNED NOT NULL, PRIMARY KEY (`measureID`), FOREIGN KEY (`sensorID`) REFERENCES `LOGICAL_SENSOR_TABLE` (`sensorID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Instead I get something like this (key defined via "ALTER TABLE"):
DROP TABLE IF EXISTS `MEASURE_TABLE`; CREATE TABLE `MEASURE_TABLE` ( `measureID` bigint UNSIGNED NOT NULL, `sensorID` int UNSIGNED NOT NULL, `timestamp` int UNSIGNED NOT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
ADD PRIMARY KEY (
MEASURE_TABLE_ibfk_1FOREIGN KEY (
I'm pretty sure they are equivalent, but for me the first version is much more readable. I had a previous schema dump from phpmyadmin (not done by me), that was in the first format. It was a different version though (4.6.6deb5).
I couldn't find the option in the export menu, is it possible to change it?
I, too, wish the dump had the entire table definition in one spot.
To get the entire definition afterwards, do
SHOW CREATE TABLE tablename
Such is often very helpful in programming questions about MySQL/MariaDB.
Some lame explanations for what you are seeing:
PRIMARY KEYis an index and a uniqueness constraint; in most situations, it would be faster (for
INSERTs) to have it already declared in the table.
SPATIALare probably required to be created after loading the data. For other
UNIQUE), the speed is more debatable; it works either way.
PRIMARY KEYalways requires copying the table! Very inefficient.
This forum (and others) are littered with the same question about
FOREIGN KEYs-- an error message because the referenced table has not been declared before the FK is declared. The simple fix is to push them to after declaring all tables.
I guess that the tool you are asking about did something simple that made it work, but without thinking about performance. File a bug report against the tool, if you like. (and add a link here)