1

This is the default PMA output of an export:

CREATE TABLE `foo` (
  `id` int(11) UNSIGNED NOT NULL,
  `fk_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `foo`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `foo`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `foo`
  ADD CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`),

...

Is there a way to get an export where all of the following ALTER TABLE are merged into the CREATE TABLE statement?

1

I shows all changes you have made, as you can see in the example

Schema (MySQL v8.0)

CREATE TABLE `bar` (`id` int(11) UNSIGNED,PRIMARY KEY (`id`));
CREATE TABLE `foo` (
  `id` int(11) UNSIGNED NOT NULL,
  `fk_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `foo`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `foo`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `foo`
  ADD CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`);

Query #1

SHOW CREATE  TABLE foo;

| Table | Create Table                                                                                                                                                                                                                                                                                              |
| ----- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| foo   | CREATE TABLE `foo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fk_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_ibfk_1` (`fk_id`),
  CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

View on DB Fiddle

2
  • Yes, the command shows indeed what I asked for but my question was, how to enable it in PMA. I think it's not possible. As I need only the structure I can live with it. +1 & accepted. Thx – Peter VARGA Jul 25 '20 at 16:53
  • phpmyadmin works like mysql 5.7 and this shows all changes like the show in the export to sql. – nbk Jul 25 '20 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.