CODEWITHSUNDEEP
mysql-workbench
Vincent Poirier
Vincent Poirier

Reputation: 4316

How do you change a table's schema?

We have a MySQL Workbench project with two tabs (two schemas/two databases).

If we copy-paste/duplicate a table from the first tab to the second tab, the resulting table remains in the first schema. How can you change a table's schema?

Each schema is linked with a specific database, so when we use the "Synchronize Model..." feature, it links all the tables properly.

Visual support

Upvotes: 13

Views: 15716

Answers (4)

Sébastien
Sébastien

Reputation: 1948

I found a less painful way to do this.

  1. Save and backup your diagram and your schema.

  2. Display schema's name before table's names in diagram. This will make the next step easier. enter image description here

  3. Right-click on the tables which are on the wrong schema, and select "Copy SQL to clipboard". Paste the script in a new SQL window. Repeat for each table you want to migrate. enter image description here

  4. Edit the script to change the schema name. Watch for any miss in entries, the wrong schema might be a reference at any line. Mine was mydb, which I don't remember creating. Execute the script. Now you have the tables on the right schema. enter image description here

  5. Synchronize your model. Be sure to check "Update the model" for each missing table, otherwise, the tables will be deleted from the schema :)

enter image description here

  1. Drag'n'drop the newly created tables into the diagram. Then remove the ones which are using the wrong schema. Tip: tables that are not in diagram won't display a dot next to their name. enter image description here

  2. Optionally, you can delete the faulty schema from the model so this never happens again. Be sure to know what you're doing first!

enter image description here

Upvotes: 0

Marwan Salim
Marwan Salim

Reputation: 722

Follow this simple steps (never miss step 4 and 5) :

  1. Open Model Tab

  2. Choose source schema. In my case, I want to copy table users from schema abc_develop_v1 to schema abc_develop_v2 then paste to diagram . So I choose schema abc_develop_v1, right-click table users then Copy 'users'

  3. Go to the targeted schema. In my case is schema abc_develop_v2, right-click then Paste 'users'

  4. Next, copy table users from schema abc_develop_v2. Right-click table users then Copy 'users'

  5. Go to your diagram and Paste 'users'.

That's all. Your table is ready in your diagram with the right schema :-)

Notes: You can double check by double-click on the table in your diagram, and look at the right corner. It will show the Schema name.

Upvotes: 2

Bampfer
Bampfer

Reputation: 2220

The cut-and-paste method described in another answer works well for tables with no foreign keys, and for a reasonable number of tables.

An alternative that preserves foreign keys is to export the model as a SQL script, edit it, and then import the new script into a new model.

Using MySQL Workbench v6.3:

  1. File -> Export -> Forward Engineer SQL Script
  2. Carefully edit SQL script. Replace references to one schema with the other, for the tables you want to move. Do this both for CREATE TABLE commands and foreign key references.
  3. File -> New Model
  4. File -> Import -> Reverse Engineer SQL Script

Unfortunately you will then need to recreate any diagrams. But that can be straightforward if you have the original diagram as reference (take a screenshot or export it to PNG or PDF.)

Upvotes: 7

Mike Lischke
Mike Lischke

Reputation: 53337

Use the model tab. You can cut out a table from one schema tab and insert it into another.

Upvotes: 8

Related Questions