Recently we discovered some tricks in schema synchronization process of MySQL databases. It’s about how MySQL creates views and tables with foreign keys. I’m going to explain them and give workarounds for dbForge Studio for MySQL users.

Let’s take a small database named crm_demo for an example.

CREATE DATABASE crm_demo;
USE crm_demo;

CREATE TABLE productcategories(
  CategoryID INT (11) NOT NULL DEFAULT 0,
  CategoryName VARCHAR (20) DEFAULT NULL,
  ParentCategory INT (11) DEFAULT NULL,
  PRIMARY KEY (CategoryID)
)
ENGINE = INNODB;

CREATE TABLE products(
  ProductID INT (11) NOT NULL DEFAULT 0,
  ProductName VARCHAR (50) NOT NULL,
  CategoryID INT (11) DEFAULT NULL,
  UnitName VARCHAR (20) DEFAULT NULL,
  UnitScale INT (5) DEFAULT NULL,
  InStock INT (11) DEFAULT NULL,
  Price DECIMAL (10, 0) DEFAULT NULL,
  DiscontinuedPrice DECIMAL (10, 0) DEFAULT NULL,
  PRIMARY KEY (ProductID),
  INDEX Product_Category USING BTREE …