DEV Community: Son Tran

SQL Server Multiple Cascade Paths (Error 1785): Which Foreign Key Loses ON DELETE CASCADE, and What Replaces It

Son Tran — Thu, 12 Nov 2026 07:48:30 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: SQL Server refuses a foreign key when its CASCADE, SET NULL or SET DEFAULT would let one delete or update reach the same table by two paths, and it decides that from the declared actions when the key is created, not from the rows. One key has to become NO ACTION, which makes some deletes fail with error 547 until a stored procedure or an INSTEAD OF DELETE trigger on the parent removes the child rows first. An AFTER DELETE trigger cannot do it, and SET NULL on the second key is refused like CASCADE.

Error 1785 means two of your foreign keys would both act on the same table when one parent row is deleted or updated, and SQL Server will not create the second one, even on empty tables. The fix has two parts. One of the keys has to become NO ACTION, and then something else has to remove the rows that key used to remove, because a NO ACTION key turns some deletes into error 547. Keep the cascade on the key that says who owns the row, and replace the other one with a stored procedure or an INSTEAD OF DELETE trigger on the parent table.

Everything below ran on SQL Server 2022 (16.0.4265.3, CU26) in a throwaway Docker container, and again on SQL Server 2025 (17.0.5035.5) with the same results. The schema is three tables: users, posts with a user_id, and comments with a post_id and a user_id.

Why does SQL Server say a foreign key may cause cycles or multiple cascade paths?

Because deleting one row would reach the same table by two routes, and SQL Server allows only one route to each table per delete or update.

A comment belongs to a post and was written by a user, and a post was written by a user. Give all three keys ON DELETE CASCADE and a deleted user reaches comments twice: directly, and through posts.

CREATE TABLE users (
  id INT IDENTITY PRIMARY KEY,
  name NVARCHAR(50) NOT NULL
);

CREATE TABLE posts (
  id INT IDENTITY PRIMARY KEY,
  user_id INT NOT NULL,
  title NVARCHAR(200) NOT NULL,
  CONSTRAINT fk_posts_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE comments (
  id INT IDENTITY PRIMARY KEY,
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  body NVARCHAR(1000) NOT NULL,
  CONSTRAINT fk_comments_post FOREIGN KEY (post_id)
    REFERENCES posts (id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE CASCADE
);
-- Msg 1785, Level 16, State 1
-- Introducing FOREIGN KEY constraint 'fk_comments_user' on table 'comments'
-- may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
-- ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
-- Msg 1750: Could not create constraint or index. See previous errors.

The comments table is not created at all. Microsoft's page for error 1785 gives the rule: "a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement." The check reads the declared actions only. There were no rows in any table when it failed.

The key the message names is the one that arrived second, not the one that is wrong. With both keys in one CREATE TABLE it named fk_comments_user. When I created comments with only the user key and added the post key afterwards with ALTER TABLE, the same error named fk_comments_post.

PostgreSQL 18.3 and MySQL 8.4.11 created the same three keys without complaint, and deleting one user there removed that user's post and every comment on either path. So this is a rule of SQL Server, not of SQL, and it tends to arrive from an ORM: Entity Framework Core configures required relationships to cascade by default, and its documentation on database cascade limitations shows this exact exception on a blog, post and person model.

The diamond is one of three shapes SQL Server refused, along with two keys from one parent and a self-reference:

Shape	Keys	Result
Direct and indirect path into one table	`users` to `posts` to `comments`, plus `users` to `comments`, all `CASCADE`	Msg 1785
The same, with `SET NULL` on `comments.user_id`	`CASCADE`, `CASCADE`, `SET NULL`	Msg 1785
The same, with `SET NULL` on `comments.post_id`	`CASCADE`, `SET NULL`, `CASCADE`	Msg 1785
Two keys from one parent	`messages.sender_id` and `messages.recipient_id` to `users`, both `CASCADE`	Msg 1785
Two keys from one parent	one `CASCADE`, one `SET NULL`	Msg 1785
Two keys from one parent	one `CASCADE`, one `NO ACTION`	Created
Self-reference	`categories.parent_id` to `categories`, `CASCADE` or `SET NULL`	Msg 1785
Two `ON UPDATE CASCADE` keys from one parent	`transfers.from_id` and `transfers.to_id` to `users`	Msg 1785

SET NULL does not get around it, and ON UPDATE is checked the same way as ON DELETE.

Which foreign key should be NO ACTION?

The key that records a reference rather than ownership: keep ON DELETE CASCADE from posts to comments, because a comment cannot exist without its post, and make comments.user_id the NO ACTION key.

Either choice makes SQL Server accept the schema, and either choice makes some deletes fail. (A third, NO ACTION on posts.user_id, is accepted too, but then deleting any user who has a post fails with Msg 547 on fk_posts_user, so it is not a candidate.) I loaded three users, one post each, and four comments: Ana on her own post, Ana on Ben's post, Ben on Ana's post, and Cy on his own post. Then I tried both versions.

Delete	`NO ACTION` on `comments.user_id`	`NO ACTION` on `comments.post_id`
A post that has comments	Works, the comments go with it	Fails, Msg 547 on `fk_comments_post`
Cy, who only commented on his own post	Works	Works
Ana, who commented on Ben's post and has Ben's comment on hers	Fails, Msg 547 on `fk_comments_user`	Fails, Msg 547 on `fk_comments_post`

The failure reads:

Msg 547, Level 16, State 1
The DELETE statement conflicted with the REFERENCE constraint "fk_comments_user".
The conflict occurred in database "blog", table "dbo.comments", column 'user_id'.
The statement has been terminated.

Cy's delete works in both versions because SQL Server applies every cascade before it checks a NO ACTION key, so by the time it looks, his one comment is already gone with his post. Ana's fails because a row survives the cascades and still points at something being deleted.

That makes the first column the better schema. With NO ACTION on comments.post_id, deleting any post that has a comment fails, which breaks the most ordinary delete in the application. With NO ACTION on comments.user_id, posts delete normally and only one operation needs help: deleting a user who has commented on someone else's post. The schema is accepted, and the delete that used to be one statement is now your job.

What replaces the cascade you removed?

A stored procedure, or an INSTEAD OF DELETE trigger on the parent table, that removes the child rows before the parent row; an AFTER DELETE trigger cannot do it.

Replacement	Result for deleting Ana
`AFTER DELETE` trigger on `users` that deletes her comments	Still Msg 547. The key is checked before the trigger fires, so it never runs
`INSTEAD OF DELETE` trigger on `users`	Works: Ana, her post and every comment on either path are gone
Stored procedure that deletes her comments, then her, in one transaction	Works, same rows removed
`ON DELETE SET NULL` on `comments.user_id`	Not available, Msg 1785

The trigger that worked:

CREATE TRIGGER trg_users_delete ON users INSTEAD OF DELETE AS
BEGIN
  SET NOCOUNT ON;
  DELETE FROM comments WHERE user_id IN (SELECT id FROM deleted);
  DELETE FROM users WHERE id IN (SELECT id FROM deleted);
END;

It reads the deleted table as a set, so DELETE FROM users WHERE id IN (2, 3) handled both users in one statement. The second DELETE inside it does not fire the trigger again; it runs the real delete, and fk_posts_user and fk_comments_post cascade as declared.

If a comment should outlive its author, the trigger is also where the null goes, since SQL Server refuses SET NULL on that key. Make comments.user_id nullable and swap the first statement for UPDATE comments SET user_id = NULL WHERE user_id IN (SELECT id FROM deleted). Deleting Ana then left her comment on Ben's post in place with no author, and removed her post and the comments on it.

The trigger has three limits, all measured:

It only fits a table that has no cascading foreign key of its own. CREATE TRIGGER ... ON posts INSTEAD OF DELETE failed with Msg 2113, "Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trg_posts_delete' on table 'posts'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.", because posts.user_id cascades from users.
It closes a door on the table that has it. With the trigger on users, adding users.org_id with ON DELETE CASCADE to an orgs table failed with Msg 1787. The same key with ON DELETE SET NULL was accepted.
It breaks a DELETE that has an OUTPUT clause. DELETE FROM users OUTPUT 1 WHERE id = 3 failed with Msg 334, "The target table 'users' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause." EF Core 7 and later save changes with OUTPUT by default, so the entity has to be configured with HasTrigger before EF Core can write to that table.

A stored procedure has none of these limits, and it is the same two statements in a transaction:

CREATE PROCEDURE delete_user @user_id INT AS
BEGIN
  SET NOCOUNT ON; SET XACT_ABORT ON;
  BEGIN TRANSACTION;
  DELETE FROM comments WHERE user_id = @user_id;
  DELETE FROM users WHERE id = @user_id;
  COMMIT;
END;

Its cost is that it only protects callers who use it. A plain DELETE FROM users from another service, a script or an ORM still fails with Msg 547, which at least fails loudly rather than leaving orphans. Doing the same two statements from application code is equivalent, and it is what EF Core's ClientCascade option does for rows it has loaded.

How do you delete a tree when a self-reference cannot cascade?

Leave the self-referencing key at NO ACTION and delete the whole subtree in one statement with a recursive common table expression.

SQL Server refused categories.parent_id with ON DELETE CASCADE and with ON DELETE SET NULL. With the key at NO ACTION, deleting a category that has children fails with Msg 547, "The DELETE statement conflicted with the SAME TABLE REFERENCE constraint". Collecting the subtree first works, because the key is checked against the rows the whole statement leaves behind, and parent and children go together:

WITH tree AS (
  SELECT id FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id FROM categories c JOIN tree t ON c.parent_id = t.id
)
DELETE FROM categories WHERE id IN (SELECT id FROM tree)
OPTION (MAXRECURSION 0);

On four rows, a chain of 1, 2 and 3 plus an unrelated row 4, that statement removed the chain and left row 4. The OPTION (MAXRECURSION 0) matters on a deep tree: SQL Server stops a recursive query after 100 levels by default, and without the option a chain of 150 categories failed with Msg 530, "The maximum recursion 100 has been exhausted before statement completion", and deleted nothing.

How Schemity flags a second cascade path before SQL Server does

Schemity's lint reports the foreign key that adds the second path while it is still a line on the diagram, and names the key it collides with.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. On a SQL Server diagram, whether it is connected to a database or a design with no connection yet, the ON DELETE and ON UPDATE actions are set in the relationship dialog, and nothing stops you from choosing CASCADE on both of a table's keys. On an unconnected design there is no server to object, so the first place the problem can show is schema lint.

Changing that last key to CASCADE is one dropdown in the dialog, and Schemity accepts it:

The rule is sqlserver-multiple-cascade-paths, in the "Fails at runtime" group, and it runs only on SQL Server diagrams. For the three tables above with all keys at CASCADE, the finding sits on comments.user_id:

SQL Server refuses this foreign key: its ON DELETE action makes a second path between the same tables (Msg 1785).

ON DELETE in users reaches comments through fk_comments_post and through this key. One of the two has to be NO ACTION.

On a connected diagram the finding also follows the change into Preview changes, next to the planned migration it would run. The preview draws only users and comments, the two tables the change touches, but lint reads the whole schema, so the path through posts is still counted:

Statement 4 in that migration is the one SQL Server would answer with Msg 1785. Schemity runs a SQL Server migration in one transaction and rolls it back on the first error, so the DROP CONSTRAINT before it would be undone and the old NO ACTION key would stay in place.

It covers the same shapes as the table earlier: a direct and an indirect path, two acting keys from one parent, and a self-reference, where the detail says that the rows below have to be handled by the application or a trigger. It checks deletes and updates separately, as SQL Server does, and it counts SET NULL and SET DEFAULT as acting keys, so switching the second key to SET NULL keeps the finding. It also follows one subtlety I confirmed on the server: an ON DELETE SET NULL in the middle of a chain ends that path, because the row it changes is not deleted (an ON UPDATE chain has no such exit). users to members with CASCADE, members to tasks with SET NULL, then tasks to notes with CASCADE and users to notes with SET NULL was created without error, and lint stays silent on it.

What lint cannot tell you is which key to change, because that depends on which table owns the row. That decision is the one in the second section, and it is easier to make on an ERD, where both paths from users to comments are visible as lines, than in a migration file where the two keys are forty lines apart. When you design the next one, set the reference key to NO ACTION in the dialog, and the finding clears before any SQL is written.

Two related posts pick up from here: ON DELETE SET NULL vs CASCADE in PostgreSQL covers choosing between the two actions where both are allowed, and circular foreign keys covers the insert side of a cycle, which SQL Server permits as long as the actions do not go round it.

MySQL ENUM vs CHECK Constraint: Which Value List Changes Copy the Table

Son Tran — Wed, 11 Nov 2026 04:44:04 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: On MySQL 8.4, appending a value to the end of an ENUM of up to 255 values is a metadata change that takes no time, while inserting one in the middle, reordering, or removing one copies the whole table and holds back writes. Removing a value that rows still hold fails with error 1265. An enforced CHECK on a VARCHAR copies the table for every change to its list, appends included, and a lookup table needs no ALTER TABLE at all.

On MySQL 8.4, an ENUM is the cheapest value list to extend and among the most expensive to edit any other way. Appending a value to the end of the list changes only the table definition and finished in 0.00 seconds on a million rows. Inserting a value in the middle, reordering the list or removing a value copies the whole table and holds back writes while it does, and removing a value that rows still hold fails outright. A CHECK (status IN (...)) on a VARCHAR copies the table for every change to its list, appends included. So whether ENUM or CHECK is the better choice depends on how the list will change, not on how it looks today.

Everything below ran on MySQL 8.4.11 in a throwaway Docker container on a laptop. The table is tasks, with 1,000,000 rows and a status ENUM('open','done','blocked') NOT NULL DEFAULT 'open' column. 400,000 rows hold open, 599,964 hold done and 36 hold blocked. Each change ran on a fresh copy of the table, and the timings are there to compare kinds of change, not to predict your server.

MySQL ENUM vs CHECK constraint: which is cheaper to change?

The ENUM is cheaper only for an append within its storage width (and a change of letter case): every other ENUM edit, and every edit to an enforced CHECK list, copies the table.

MySQL stores an ENUM value as a number, the position of the value in the list, and keeps the strings in the table definition. Appending a string leaves every stored number pointing at the same value, so there is nothing to rewrite. Anything that moves a value to a different position, or takes one away, changes what the stored numbers mean, so MySQL rebuilds the table to write them again. The online DDL reference says it in one line: "Adding members in the middle of the list causes renumbering of existing members, which requires a table copy."

Change to `status` on 1,000,000 rows	What MySQL does	Time
Append `'archived'` at the end	`INSTANT`	0.00 s
Insert `'review'` between `'open'` and `'done'`	Copies the table (`INSTANT` and `INPLACE` refused, 1846)	1.58 s
Reorder to `('done','open','blocked')`	Copies the table	1.50 s
Remove `'archived'`, which no row holds	Copies the table	1.53 s
Remove `'blocked'`, which 36 rows hold	Fails, `ERROR 1265 (01000): Data truncated for column 'status' at row 2`	-
Change the case of `'done'` to `'Done'`	`INSTANT`, and every row now reads `Done`	0.00 s
Append a 256th value to a 255-value `ENUM`	Copies the table	1.46 s
Append a 9th member to an 8-member `SET`	Copies the table	1.39 s
Append a 10th member to a 9-member `SET`	`INSTANT`	0.00 s
Add `'archived'` to `CHECK (status IN (...))` on a `VARCHAR(20)`	Copies the table (`INSTANT` and `INPLACE` refused, 1845)	1.65 s

Neither MySQL nor PostgreSQL can alter a check constraint's expression, so changing the list means DROP CHECK and ADD CONSTRAINT ... CHECK, and adding a check makes MySQL read every row to validate it. MySQL does that by copying the table, even when the new list only adds a value and no row could fail it. The DROP CHECK alone is instant; it is the ADD that costs. PostgreSQL can split the cost with NOT VALID and a later VALIDATE CONSTRAINT; MySQL has no equivalent. Its NOT ENFORCED check is added instantly, but it enforces nothing, and switching it to ENFORCED copies the table.

A copy is not only slow, it holds back writes. On the same table grown to 3,000,000 rows, inserting 'review' in the middle of the list copied for 4.94 seconds. A single-row INSERT sent 0.7 seconds in waited 4.33 seconds, while a SELECT by primary key sent at the same moment came back at once. Appending 'archived' to the same 3,000,000 rows took 0.00 seconds.

Does adding a value to a MySQL ENUM copy the table?

Not if the value goes at the end and the list stays within the same storage width.

An ENUM takes 1 byte per row up to 255 values and 2 bytes from 256 up to its limit of 65,535; a SET takes 1, 2, 3, 4 or 8 bytes for its up to 64 members, per the storage requirements table. When an append crosses one of those widths, every row has to be rewritten at the new width, so the append copies the table like any other change. That is why the 9th SET member copied and the 10th did not, and why the 256th ENUM value copied. Few lists ever reach 255 values, but 8 SET members is an easy boundary to cross.

To make the cost visible before production, name the algorithm in the migration:

ALTER TABLE tasks
  MODIFY status ENUM('open', 'done', 'blocked', 'archived') NOT NULL DEFAULT 'open',
  ALGORITHM=INSTANT;
-- Query OK, 0 rows affected (0.00 sec)

ALTER TABLE tasks
  MODIFY status ENUM('open', 'review', 'done', 'blocked') NOT NULL DEFAULT 'open',
  ALGORITHM=INSTANT;
-- ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported.
-- Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

The second statement changes nothing and says why. Without the clause it would have copied the table, because MySQL picks the cheapest algorithm it can and does not name it; the only sign is 1000000 rows affected instead of 0 rows affected, after the copy has run. The same clause catches a migration generated from a model where someone inserted 'review' where it reads well, rather than at the end.

Order matters for a second reason. MySQL sorts an ENUM by position, not alphabetically, so ORDER BY status on this table returns open, done, blocked. Appending keeps that order stable; putting a new value anywhere but last in a sort means inserting it or reordering the list, and both copy the table.

How do I remove a value from a MySQL ENUM?

Move every row off the value first, then remove it, and expect the removal to copy the table.

With strict mode on, the MySQL 8.4 default, removing a value that any row holds fails before anything changes: ERROR 1265 (01000): Data truncated for column 'status' at row 2. Without strict mode, the same statement succeeds with one warning per row, 36 here, and those rows now hold the empty string, which the ENUM documentation calls the error value, with index 0. SELECT status + 0 shows it. Nothing tells the application that 36 tasks lost their status. So before the change:

SELECT COUNT(*) FROM tasks WHERE status = 'blocked';   -- 36
UPDATE tasks SET status = 'open' WHERE status = 'blocked';
ALTER TABLE tasks MODIFY status ENUM('open', 'done') NOT NULL DEFAULT 'open';

Renaming a value is the same three steps: append the new name (instant), move the rows with an UPDATE, remove the old name (a copy). On a large table the UPDATE is the expensive step, one transaction over every row that holds the value, so run it in batches and have the application accept both names while the rows move. Changing 'done' to 'completed' in place is refused INSTANT with 1846 and fails with 1265 on rows holding 'done'. There is no shorter route, with one exception: a change of letter case only. Under a case-insensitive collation such as the default utf8mb4_0900_ai_ci, relabelling 'done' as 'Done' was instant and every row read back as Done. Under utf8mb4_bin or utf8mb4_0900_as_cs the same statement failed with 1265, because there 'done' and 'Done' are different values. The default follows the same rule: DEFAULT 'Open' on a list holding 'open' is accepted under _ci and stored as 'open', and refused on a status ENUM('open','done') COLLATE utf8mb4_bin column with ERROR 1067 (42000): Invalid default value for 'status'.

If the copy is too expensive, the alternative is not to remove the value at all: leave it in the list, stop the application writing it, and remove it the next time the table is rebuilt for another reason.

Which should you use: ENUM, CHECK or a lookup table?

Use an ENUM for a list that only grows at the end, a lookup table for a list that changes often or carries data of its own, and a CHECK when portability matters more than the cost of changing it.

	`ENUM`	`VARCHAR` + `CHECK`	Lookup table + foreign key
Add a value	Instant at the end; a copy anywhere else	Copies the table	An `INSERT`, no `ALTER TABLE`
Remove a value rows still hold	Fails, 1265 (strict)	Fails, 3819 (`Check constraint 'c_status_check' is violated.`)	Fails, 1451 (`Cannot delete or update a parent row`)
Storage per row	1 byte (up to 255 values)	The string itself	The string or a numeric key
Sort order	Position in the list	Collation order	By label after a join, or a sort column you add
Moves to PostgreSQL or SQL Server	Needs converting	As written	As written

The removal row matters as much as the add row: every option refuses to orphan a row, but only the lookup table does it without an ALTER TABLE on the table that holds the rows. Its cost is a join to read the label and one more table in the diagram.

How Schemity shows what an ENUM change costs

Schemity's impact analysis reports which MySQL ENUM edits copy the table and how many rows hold a value you removed, before the migration runs.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. After you connect to MySQL, an ENUM column's values appear in the field dialog's Values box, and editing them there becomes a planned ALTER TABLE ... MODIFY statement.

Impact analysis applies the rules measured above:

Append a value and there is no finding, because MySQL changes only the definition. Append the 256th ENUM value or the 9th SET member and the table rewrite appears.
Insert, reorder or remove a value and the findings include "Rewrites tasks to change status" with the row estimate, and a note that changing the type of status holds back writes to tasks while it reads the same estimated rows.
Remove a value and a "Can fail on existing data" finding appears ("Loses data" on a server without strict mode), with "Count exactly" to count the rows that still hold it. The count runs only when you ask, as a read-only SELECT COUNT(*) that matches the removed values, so 36 blocked rows show up before MySQL reports them as error 1265.
A change of letter case is matched the way the column's collation compares: no finding under _ci, a failing removal under _bin or _cs.

Appending archived after open, done and blocked in the Values box is the first case, and the Impact drawer stays empty:

Remove blocked instead and the same drawer shows three findings: the change can fail on existing data, it rewrites tasks, and it holds back writes while it reads the estimated ~997.6K rows, 42 MB on disk.

Schema lint adds one rule for the same column type: mysql-enum-default-not-listed reports an ENUM or SET default that is not one of its values, which MySQL refuses with error 1067, compared under the column's collation. That is how the diagram lets you see the impact of every change to a value list: the append and the insert look alike in a migration file, and only one of them copies the table.

MySQL ENUM changes, in one list

Only an append within the same storage width, or a change of letter case under a _ci collation, is free; everything else rewrites the table or fails.

Appending to an ENUM or SET is INSTANT, unless it crosses 255 ENUM values or a SET storage width (8, 16, 24 or 32 members).
Inserting in the middle, reordering and removing a value copy the table and hold back writes until the copy ends.
Removing a value that rows hold fails with 1265 in strict mode and silently empties those rows without it; move them first.
Changing an enforced CHECK list copies the table every time, appends included; a lookup table changes with an INSERT.
Add ALGORITHM=INSTANT to an append so MySQL refuses, rather than copies, if the change is not one, and set lock_wait_timeout so even an instant change cannot queue reads behind an open transaction.

The other MySQL ALTER TABLE changes that copy, from a VARCHAR crossing 255 bytes to a foreign key added with checks on, are timed in MySQL ALTER TABLE: which changes lock the table, and what happens to the statements before a failed one is covered in which failed migrations roll back. The same choice on PostgreSQL, where enums are a type of their own, is compared in Postgres enum vs check constraint vs lookup table, and if you are converting a MySQL schema to PostgreSQL, every ENUM needs that decision anyway.

MariaDB to MySQL Migration: Which Schema Features Break, and How to Find Them First

Son Tran — Tue, 10 Nov 2026 01:19:17 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: A MariaDB schema is not a MySQL schema once it uses anything MariaDB added on its own: UUID, INET6 and INET4 columns, sequences, system-versioned tables and the uca1400 collations all stop a MySQL 8.4 import, and a JSON column comes out of the dump as LONGTEXT with a default MySQL refuses. Six information_schema queries list the affected columns and tables before you try, and the MySQL replacement for each is a choice, not a rename.

Moving a schema from MariaDB to MySQL works only for the parts both databases share, and since MariaDB 10.x the parts they do not share are the ones teams pick MariaDB for. A plain mariadb-dump of a three-table MariaDB 11.4 schema with one sequence, loaded into MySQL 8.4, failed on every CREATE in it: the sequence, a table with UUID and INET6 columns, a table whose default draws on the sequence, and a table whose only visible problem was its collation. Each failure has a MySQL replacement, but the replacement changes the column, so it is a design decision you make per column, not a find-and-replace.

The schema below is what was tested, on mariadb:11.4 (11.4.13) and mysql:8.4 (8.4.11) in throwaway containers. It is the kind of thing a MariaDB shop accumulates without trying: a native UUID for public ids, an INET6 for the last login address, a JSON preferences column, a generated total, a sequence for invoice numbers, and system versioning on orders so support can see what an order looked like last week.

CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id UUID NOT NULL DEFAULT UUID(),
  email VARCHAR(255) NOT NULL UNIQUE,
  last_ip INET6 NULL,
  signup_ip INET4 NULL,
  prefs JSON NOT NULL DEFAULT '{}'
);

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  qty INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  total DECIMAL(12,2) AS (qty * unit_price) PERSISTENT,
  status VARCHAR(20) NOT NULL DEFAULT 'new',
  FOREIGN KEY (customer_id) REFERENCES customers (id)
) WITH SYSTEM VERSIONING;

CREATE SEQUENCE invoice_no START WITH 1000;

CREATE TABLE invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  number BIGINT NOT NULL DEFAULT (NEXT VALUE FOR invoice_no),
  FOREIGN KEY (order_id) REFERENCES orders (id)
);

Can you migrate MariaDB to MySQL with a dump?

You can when the schema uses only what both databases share, and the dump of this one failed on every MariaDB-only feature it touched.

mariadb-dump --no-data shop, piped into mysql --force on 8.4, produced these errors, in order:

Statement in the dump	MySQL 8.4 said
`DROP SEQUENCE IF EXISTS invoice_no`	`ERROR 1064 (42000)`: syntax error near `SEQUENCE IF EXISTS`
`CREATE SEQUENCE invoice_no ...`	`ERROR 1064 (42000)`: syntax error near `SEQUENCE`
`DO SETVAL(invoice_no, 1000, 0)`	`ERROR 1305 (42000): FUNCTION shop.SETVAL does not exist`
`CREATE TABLE customers` with `uuid` and `inet6`	`ERROR 1064 (42000)`: syntax error near `uuid NOT NULL DEFAULT uuid()`
`CREATE TABLE invoices` with `DEFAULT nextval(shop.invoice_no)`	`ERROR 1064 (42000)`: syntax error near `nextval(`
`CREATE TABLE orders ... COLLATE=utf8mb4_uca1400_ai_ci`	`ERROR 1273 (HY000): Unknown collation: 'utf8mb4_uca1400_ai_ci'`

Fixing those exposed three more. A CHAR(36) column with the dump's DEFAULT uuid() is still a syntax error on MySQL, which wants an expression default in parentheses, DEFAULT (UUID()). With that rewritten, customers failed on ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'prefs' can't have a default value, and orders failed on a syntax error near SYSTEM VERSIONING. MySQL's parser stops at the first problem in each statement, so the first error list is never the whole list. That is why it pays to find everything before the first attempt, with the queries further down.

The collation row is the one that surprises people, because nothing in the schema asked for it. utf8mb4_uca1400_ai_ci is the server default on MariaDB 11.4 (SELECT @@collation_server returned it on 11.4.13), so every table created without an explicit collation carries it, and MySQL has never had it.

Which MariaDB schema features does MySQL 8.4 not accept?

Six MariaDB features have no MySQL equivalent under the same name (UUID, INET6, INET4, sequences, system versioning and the uca1400 collations), JSON changes meaning on the way, PERSISTENT needs renaming, and expression defaults need parentheses.

MariaDB feature	What MySQL 8.4 does with it	What to use in MySQL
`UUID` type (MariaDB 10.7+)	Syntax error	`BINARY(16)` with `DEFAULT (UUID_TO_BIN(UUID(), 1))`, or `CHAR(36)` with `DEFAULT (UUID())`
`INET6`, `INET4` types (`INET4` from 10.10)	Syntax error	`VARBINARY(16)` with `INET6_ATON()`, or `VARCHAR(45)`
`JSON` (an alias of `LONGTEXT` plus a check)	Arrives as `longtext` with `CHECK (json_valid(...))`; a literal default fails	`JSON`, with `DEFAULT ('{}')` in parentheses
`CREATE SEQUENCE`, `NEXT VALUE FOR`	Syntax error	`AUTO_INCREMENT`, or a one-row counter table
Expression defaults such as `uuid()`	Syntax error without parentheses	The same expression in parentheses
`WITH SYSTEM VERSIONING`	Syntax error	A plain table plus a history table, or no history
`utf8mb4_uca1400_ai_ci` (11.4 default)	`ERROR 1273`: unknown collation	`utf8mb4_0900_ai_ci`
`PERSISTENT` generated column	Syntax error in hand-written DDL	`STORED` (the dump already writes `STORED`)
`VECTOR(n)` (MariaDB 11.7+)	Syntax error: MySQL 8.4 has no `VECTOR` (it arrived in MySQL 9.0)	`VARBINARY`/`BLOB` holding the packed floats, or MySQL 9

The JSON row needs the most thought, because nothing fails if the default is left out. MariaDB's JSON page says "JSON is an alias for LONGTEXT COLLATE utf8mb4_bin introduced for compatibility reasons with MySQL's JSON data type", and SHOW CREATE TABLE on MariaDB prints prefs longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '{}' CHECK (json_valid(prefs)). MySQL 8.4 accepts that column once the default is written as DEFAULT ('{}'), keeps the check, and rejects '{bad' with ERROR 3819. But it is a text column, not MySQL's binary JSON. Change it to JSON on purpose; a dump will not.

The UUID and INET rows are real choices. BINARY(16) with UUID_TO_BIN(UUID(), 1) keeps the 16-byte storage and, for values generated by UUID() (version 1), the time-ordered index layout MariaDB's UUID type gave you, but every query that prints the value needs BIN_TO_UUID(public_id, 1), and every query that searches by it, and the data load itself, needs UUID_TO_BIN. CHAR(36) keeps the queries unchanged and costs 36 bytes per value plus a random-order index. The same trade applies to VARBINARY(16) against VARCHAR(45) for addresses, where INET6_ATON('2001:db8::1') and INET6_NTOA(last_ip) are the conversions. The trade between key types is the subject of UUID vs bigint primary keys; the short version is that the binary form is the one that keeps the index small.

System versioning is the row with data at stake. The history rows live in the same table, hidden from a plain SELECT, and MySQL has nowhere to put them. On MariaDB the table also refuses to change: an ALTER TABLE orders ADD COLUMN ... failed with ERROR 4119 (HY000), which refuses the change on a system-versioned table until @@system_versioning_alter_history is changed. A default mariadb-dump writes only the current rows; --dump-history adds the old versions with their row_start and row_end values, which a MySQL table has no columns for. Decide whether the history matters before the move. If it does, copy it out with SELECT ... FROM orders FOR SYSTEM_TIME ALL into an ordinary orders_history table and fill that table from the application or a trigger on MySQL.

How do you find the MariaDB-only features in your schema?

Six queries against information_schema on the MariaDB server list the columns, tables and defaults that will not move as written, before anything is dumped.

Run them in the database you plan to move, on MariaDB itself, where these values are reported. On the test schema they returned exactly the problem list from the dump, plus signup_ip, which the dump never reached:

-- 1. Column types MySQL does not have
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND data_type IN ('uuid', 'inet6', 'inet4');

-- 2. JSON columns (stored as LONGTEXT with a json_valid check)
SELECT table_name, constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = DATABASE()
  AND check_clause LIKE 'json_valid(%';

-- 3. System-versioned tables and sequences
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_type IN ('SYSTEM VERSIONED', 'SEQUENCE');

-- 4. Expression defaults, sequence defaults included
SELECT table_name, column_name, column_default
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND column_default LIKE '%(%'
  AND column_default NOT LIKE 'current_timestamp(%';

-- 5. Columns with a collation MySQL does not have
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND collation_name LIKE '%uca1400%';

-- 6. Tables with that collation as their default
SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_collation LIKE '%uca1400%';

Query 4 matters twice. MariaDB reports an expression default without the parentheses MySQL requires (uuid(), concat('a','b')), and it reports NEXT VALUE FOR invoice_no as a nextval() call on the sequence, so a search of the migration files for NEXT VALUE finds the column a developer wrote, not the one an ORM generated. On the test schema it returned public_id and invoices.number. Query 2 is the way to tell a JSON column from a plain LONGTEXT, because data_type is longtext for both.

The six results are a list. To see what the list means for the rest of the schema, you also need the foreign keys, the tables that reference orders, and the columns that copy customers.public_id into other tables, and that is where a picture of the schema is faster than more queries.

Does the reverse direction, MySQL to MariaDB, work better?

Mostly yes: a MySQL 8.4 table with a JSON column and the utf8mb4_0900_ai_ci collation loaded into MariaDB 11.4.13 unchanged, and a functional index was the part that failed.

MariaDB reads MySQL's JSON as its alias, so the column arrived as longtext ... CHECK (json_valid(attrs)). MySQL's default collation is accepted because the MariaDB 11.4.5 release notes list "Added support for utf8mb4_0900_* collations", so check the server's version before relying on it. What failed was the index KEY sku_color ((CAST(attrs->>'$.color' AS CHAR(20)))), a MySQL functional key part (the dump writes it out as a json_extract expression), which MariaDB 11.4 rejected as a syntax error. On MariaDB that index is written as a virtual generated column holding the expression, with an ordinary index on the column.

One column-level difference runs the same way. MySQL 8.4 accepts n INT NOT NULL INVISIBLE with no default, and MariaDB 11.4 refuses the same column with ERROR 4108 (HY000): Invisible column 'n' must have a default value, because an INSERT that lists no columns would have nothing to put in it. Give every invisible NOT NULL column a default before moving it to MariaDB.

So the two directions are not symmetric. MariaDB has spent several releases accepting MySQL's syntax, and MySQL has not done the same for MariaDB's. A move from MariaDB to MySQL is the one that needs the audit. The other direction mostly needs the JSON caveat: MariaDB's JSON page also says row-based replication "will not work for JSON types from MySQL to MariaDB", because one side stores text and the other binary.

Direction	Tested result	Main blocker
MariaDB 11.4 to MySQL 8.4	Failed on every MariaDB-only feature	Types, sequences, versioning, `uca1400` collation
MySQL 8.4 to MariaDB 11.4	Loaded, apart from one index	Functional key parts, invisible `NOT NULL` columns with no default

How to see a MariaDB schema before you move it

Connect Schemity to the MariaDB server and the diagram shows every table with the types MariaDB reports, so the columns from the audit can be read in place, next to the tables that depend on them.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. From the next release MariaDB is its own database type, supported from 11.4 onwards: pick MariaDB in the connection dialog or paste a mariadb:// URL, and the diagram footer shows the server it read. The reader was tested on MariaDB 11.4.13, 11.8.9, 12.3.3 and 13.0.2 and read this schema the same on all four; the screenshot below is from 13.0.2. Reverse engineering then reads the whole schema, and the test schema came out as MariaDB stores it:

customers.public_id as UUID, last_ip as INET6 and signup_ip as INET4, so the first audit query's results are visible as column types on the canvas.
customers.prefs as LONGTEXT with its json_valid(prefs) check, which is what MariaDB means by JSON.
orders, a system-versioned table, read like any other table, with its row count and dependents.
invoices.number with its nextval() default on the sequence. The sequence itself is not a table and is not drawn.

That is what it takes to understand your schema before a move like this: the column types, which tables hold the columns that change, and every foreign key into them, on one canvas. public_id and last_ip are in customers, which orders references, which invoices references, so the UUID decision touches every query that joins those three.

Can Schemity write the MySQL version of a MariaDB schema?

Yes, from the next release: the SQL dialog writes a MariaDB diagram as MySQL DDL, and the script it wrote for the test schema ran on MySQL 8.4.11 with no errors, all three tables and both foreign keys created.

Open the SQL dialog with Export > SQL file and pick MySQL in its Database dropdown (converting SQL to another database). The script makes the conservative choice from the table above for each MariaDB-only feature and lists every one in a comment block at the top, so the review is the list, not a diff:

In the MariaDB diagram	In the MySQL script	Said in the notes
`public_id UUID DEFAULT uuid()`	`CHAR(36) DEFAULT (UUID())`	MySQL has no `UUID` type: stored as its 36-character text
`last_ip INET6`, `signup_ip INET4`	`VARCHAR(45)`	MySQL has no network address type: stored as text
`prefs LONGTEXT ... DEFAULT '{}'` with `json_valid`	`LONGTEXT DEFAULT ('{}')`, check kept	-
`number DEFAULT nextval(invoice_no)`	No default	The default draws from a MariaDB sequence, which MySQL has none of: left out
`utf8mb4_uca1400_ai_ci` on every table	No collation, so MySQL 8.4 used `utf8mb4_0900_ai_ci`	4 collation settings left out
`total ... PERSISTENT`	`GENERATED ALWAYS AS (...) STORED`	-
`BIGINT UNSIGNED` keys	`BIGINT UNSIGNED`	-

What it does not decide is the part of the move that is a design choice. CHAR(36) is the choice that leaves queries unchanged; if you want BINARY(16), edit those columns in the script. The prefs column stays text with its check, the same as the dump, so changing it to MySQL JSON is still a deliberate edit. invoices.number now needs its value from the application or a counter table. And orders is created as a plain table: its history is not part of the schema, so it has to be copied out before the move as described above. The diagram itself is left as MariaDB, so the MariaDB design and the MySQL script never drift into two models.

If the MariaDB model lives in Navicat Data Modeler rather than a running server, Import Navicat reads a MariaDB .nmodel into a MariaDB diagram with its UUID, INET6 and INET4 columns kept native, and the same SQL dialog writes it for MySQL. Converting to another database is a licensed feature.

What to decide before moving a schema from MariaDB to MySQL

Every MariaDB-only feature in the audit needs a MySQL replacement chosen before the dump, and the UUID, INET, sequence and versioning choices change application queries, not only DDL.

Run the six audit queries on the MariaDB server and keep the output as the migration checklist.
Set every table to utf8mb4_0900_ai_ci, or another collation MySQL has, in the dump or before it.
For each UUID column, choose BINARY(16) (smaller index, conversion functions in every query) or CHAR(36) (unchanged queries, larger index). Do the same for INET6 and INET4.
Change JSON columns to MySQL JSON on purpose, and write their defaults in parentheses.
Replace each sequence with AUTO_INCREMENT or a counter table, and put every expression default from query 4 in parentheses.
Decide whether system-versioned history is kept. If it is, copy it out with FOR SYSTEM_TIME ALL before the move.

Moving between engines with more distance between them, such as MySQL to PostgreSQL, follows the same pattern of type decisions at a larger scale. For a MySQL schema that is staying put, which MySQL ALTER TABLE changes lock the table is the next question, and the Navicat Data Modeler alternative post shows a MariaDB shop model read from a live MariaDB 11.4 server.

PostgreSQL Composite Foreign Keys vs Row-Level Security: Which Keeps Tenants Apart?

Son Tran — Tue, 10 Nov 2026 01:18:44 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Row-level security decides which rows a session can read and write, but PostgreSQL foreign key checks bypass it, so an Acme task can still be assigned to a Globex member the session cannot even see. A composite foreign key such as (company_id, assignee_id) referencing members (company_id, id) makes the database itself refuse that row. They close different holes: keep the composite keys for references between tenant tables, and add row-level security when queries need a second line of defence.

Row-level security stops a query from seeing another tenant's rows; it does not stop a row from pointing at one. PostgreSQL foreign key checks bypass every policy, so with row-level security enabled and filtering correctly, an Acme task can still be assigned to a Globex member that the Acme session cannot even read. The constraint that refuses that row is a composite foreign key, (company_id, assignee_id) referencing members (company_id, id), and the two features answer different questions.

I found this reviewing a multi-company task tracker that an AI agent designed, I clicked a foreign key on tasks.assignee_member_id and company_id lit up with it, on both tables. The members table had a primary key on id and, beside it, UNIQUE (company_id, id), a constraint that adds no uniqueness at all. In my whole career I had never seen one. It turns out to be the cheapest way to make a multi-tenant database refuse cross-tenant data, and it has three traps that are easy to miss.

Does row-level security stop a row from referencing another tenant?

No: the PostgreSQL manual says foreign key references always bypass row security, and on PostgreSQL 18.3 a cross-tenant reference goes straight through.

Here is the usual setup: two companies, one member each, tasks and members both scoped by a policy on company_id, and an app role that is not the table owner.

CREATE TABLE companies (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE members (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  company_id bigint NOT NULL REFERENCES companies (id),
  name       text NOT NULL
);
CREATE TABLE tasks (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  company_id  bigint NOT NULL REFERENCES companies (id),
  assignee_id bigint REFERENCES members (id),
  title       text NOT NULL
);
INSERT INTO companies (name) VALUES ('Acme'), ('Globex');
INSERT INTO members (company_id, name) VALUES (1, 'Ann'), (2, 'Bob');

CREATE ROLE app LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON companies, members, tasks TO app;
ALTER TABLE members ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks   ENABLE ROW LEVEL SECURITY;
CREATE POLICY members_tenant ON members
  USING (company_id = current_setting('app.company_id')::bigint);
CREATE POLICY tasks_tenant ON tasks
  USING (company_id = current_setting('app.company_id')::bigint)
  WITH CHECK (company_id = current_setting('app.company_id')::bigint);

As app with SET app.company_id = '1', the policies work: SELECT count(*) FROM members returns 1, and SELECT ... FROM members WHERE id = 2 returns nothing, because Bob belongs to Globex. Writing a Globex task is refused with new row violates row-level security policy for table "tasks". And then:

INSERT INTO tasks (company_id, assignee_id, title) VALUES (1, 2, 'cross-tenant');
-- INSERT 0 1

Acme now has a task assigned to Bob. The row passes WITH CHECK, because its own company_id is Acme's, and the foreign key finds member 2, because the check does not go through the policy. The PostgreSQL manual says so directly: referential integrity checks, foreign key references included, always bypass row security to keep data integrity. That is the right call for integrity and the wrong assumption for isolation: a bug that passes the wrong id, or a request that sends one, writes a reference no policy will ever stop.

Two more facts from the same session decide how much weight row-level security can carry. The table owner bypasses the policies unless the table is altered with FORCE ROW LEVEL SECURITY (an owner role saw both rows until then), and superusers and roles with BYPASSRLS always bypass them. And the setting is easy to get wrong on a pooled connection. A connection that never set it fails with unrecognized configuration parameter "app.company_id", but a plain SET lasts for the whole connection: after one request ran SET app.company_id = '2', the next transaction on that connection set nothing and still counted Globex's member. Set it with SET LOCAL, or set_config('app.company_id', ..., true), inside every transaction, so it ends with the transaction; a later transaction that forgets then fails with invalid input syntax for type bigint: "" instead of reading another tenant.

How does a composite foreign key keep tenants apart?

It makes the reference carry the tenant: the child's company_id must match the parent's, so the pair has to exist in the parent as one row.

ALTER TABLE members ADD CONSTRAINT members_company_id_id_key UNIQUE (company_id, id);

ALTER TABLE tasks DROP CONSTRAINT tasks_assignee_id_fkey;
ALTER TABLE tasks ADD CONSTRAINT tasks_company_id_assignee_id_fkey
  FOREIGN KEY (company_id, assignee_id) REFERENCES members (company_id, id)
  ON DELETE SET NULL (assignee_id);

Now the same insert is refused: insert or update on table "tasks" violates foreign key constraint "tasks_company_id_assignee_id_fkey", while assigning Ann, who is in Acme, still works. No policy, setting or role is involved; the database proves the reference with the same machinery that proves any foreign key, and the check runs for the table owner and superusers too. The one way past it is a superuser switching triggers off: with SET session_replication_role = replica, the same cross-tenant insert went through, which is what restore and replication tools do on purpose.

The unique constraint looks redundant and is required. A foreign key may only point at columns declared as the primary key or a unique constraint, as exactly that list, and PostgreSQL does not reason that (company_id, id) is unique because id is. Without it, the key is refused with there is no unique constraint matching given keys for referenced table "members". Its only job is to be the target, plus the index the check uses. The same pattern on a junction table, so a member can only hold roles of their own tenant, is worked through in the multi-tenant RBAC post.

What breaks when you add a composite tenant key?

Three things: ON DELETE SET NULL clears the tenant column, swapped columns are accepted silently, and MySQL wants the columns in index order.

SET NULL without a column list. A plain ON DELETE SET NULL clears every column of the key. On the composite key that includes company_id, which is NOT NULL, so deleting the member fails: null value in column "company_id" ... violates not-null constraint, from an internal UPDATE ... SET "company_id" = NULL, "assignee_id" = NULL. PostgreSQL 15 added the column list, ON DELETE SET NULL (assignee_id), which clears only the member column; with it, deleting Ann leaves the task in Acme with no assignee. MySQL and SQL Server have no list, MySQL 8.4 refuses to create the plain SET NULL key at all (ERROR 1830: Column 'company_id' cannot be NOT NULL: needed in a foreign key constraint), and CockroachDB 26.3 refuses the clause, so on those use CASCADE, RESTRICT or NO ACTION for these keys. The general choice between those actions is in ON DELETE SET NULL vs CASCADE.

Columns pair by position, not by name. FOREIGN KEY (assignee_id, company_id) REFERENCES members (company_id, id) is created without complaint, because both columns are integers, and then compares assignee_id with company_id. With a third company, Initech, and a third member, Cy (id 3, in Acme), that key refused the correct row (company 1, assignee 3) with Key (assignee_id, company_id)=(3, 1) is not present and accepted an Initech task assigned to Ann of Acme (company 3, assignee 1). Nothing fails at creation; it fails on the first real rows, and some of the rows it lets through cross tenants.

MySQL wants index order. PostgreSQL accepts the referenced columns in any order as long as some unique constraint covers that set. MySQL 8.4.11 accepted REFERENCES members (company_id, id) against UNIQUE (company_id, id) and refused REFERENCES members (id, company_id) with error 6125, Missing unique key for constraint ... in the referenced table. Writing the tenant first everywhere avoids it.

The index is less of a trap than it looks. The check that runs when a member is deleted searches for company_id = ? AND assignee_id = ?, and an index leading with assignee_id alone serves it: on 100,000 rows, PostgreSQL 18.3 used ix_tasks_assignee for that lookup, because the member id already names one parent row.

Composite foreign key vs row-level security: which should you use?

Use composite foreign keys wherever tenant tables reference each other, and add row-level security when queries need a second guard; they close different holes.

	Composite foreign key	Row-level security
Stops a query reading another tenant's rows	No	Yes, for roles it applies to
Stops writing a row for another tenant	No	Yes: `WITH CHECK`, or `USING` when no `WITH CHECK` is given
Stops a reference to another tenant's row	Yes	No, foreign key checks bypass it
Applies to the table owner and superusers	Yes, unless a superuser turns triggers off	Owner only with `FORCE`; superusers and `BYPASSRLS` never
Needs per-session setup	No	With the setting pattern used here, `SET LOCAL` in every transaction
Schema cost	`company_id` on child tables, `UNIQUE (company_id, id)` on parents, wider foreign keys	One policy per table

The composite key is a structural guarantee: once it exists, no ordinary write can store a cross-tenant reference, including migrations, admin scripts and the next service someone writes. Row-level security is a query guarantee: it catches the missing WHERE company_id = ? that every multi-tenant codebase eventually ships. A schema that has only the policies looks isolated and can still hold Acme tasks assigned to Globex people; a schema with only composite keys can still leak a list of every tenant's members to a query that forgot its filter.

The cost of the keys is real but bounded. Every table that references a tenant table carries company_id, which it usually has anyway, and every referenced table gets one extra unique constraint. Tables outside the tenancy, such as a global country list, keep ordinary single-column keys.

How do you design a composite tenant key in Schemity?

Draw the relationship as usual: when the parent has a unique constraint holding its primary key plus a tenant column, Schemity makes the composite key for you.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Since v2.14.5, dragging a relationship from members to tasks when members has UNIQUE (company_id, id) creates (company_id, member_id) referencing members (company_id, id). If tasks already has company_id, it is shared; if not, it is added as NOT NULL, and the relation dialog shows that row locked so you can see which. A checkbox keeps the plain key for a table outside the tenancy. The details are in Relationships.

The traps above are handled where you design the next one, not after the migration runs. SET NULL on such a key is written as ON DELETE SET NULL (member_id) on PostgreSQL and refused with the reason on the databases that cannot limit it. A key read from a database keeps its column list instead of losing it on the next migration. And schema lint catches both mistakes before they ship: redundant-fk-path flags tasks when it and members both reference companies but the key from tasks to members leaves company_id out, the key that let an Acme task go to a Globex member, and names the composite key that fixes it; fk-columns-crossed flags a key whose columns are paired crosswise, the swapped key that is created and then refuses correct rows.

If an AI agent designs your schema, the open-source schema design skill makes it decide ON DELETE per key, name every constraint, and check its design with Schemity's lint before handing it back. It was one of those agent-designed schemas that put this key in front of me.

Transactional DDL in PostgreSQL, MySQL and CockroachDB: Which Failed Migrations Roll Back?

Son Tran — Sat, 07 Nov 2026 17:53:23 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: If the third statement of a migration fails, PostgreSQL, SQL Server and SQLite undo the whole migration, and MySQL and CockroachDB do not. MySQL commits before (and after) every DDL statement, so earlier statements and any data changes made before them stay. CockroachDB does the same by default through autocommit_before_ddl, and with that setting off it can still commit the data changes while undoing the schema change.

Whether a failed migration rolls back depends on the database as much as on the BEGIN around it. With the migration in one transaction and a rollback on error, PostgreSQL, SQL Server and SQLite undo every statement when the third one fails. MySQL and CockroachDB keep the first two, and both also keep any data change the same transaction made before them. On CockroachDB, turning off the setting that causes this can still leave the data change committed while the schema change is undone.

The migration below was run on each database as it comes out of the box, against a customers table with two rows, one of them with a NULL email:

BEGIN;
UPDATE customers SET credit = 100 WHERE id = 1;           -- a data fix
ALTER TABLE customers ADD COLUMN phone VARCHAR(30);       -- statement 1
CREATE INDEX customers_phone_idx ON customers (phone);    -- statement 2
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;    -- statement 3 fails
ROLLBACK;

Statement 3 was always going to fail: one row has no email. The versions were PostgreSQL 18.3, MySQL 8.4.11 and 9.7.4, CockroachDB v26.3.4, SQL Server 2022 (CU26) and SQLite 3.54.0, each in a throwaway container or in memory. MySQL spells the NOT NULL change MODIFY email VARCHAR(255) NOT NULL. SQL Server needs BEGIN TRANSACTION, ADD phone NVARCHAR(30) NULL without the word COLUMN, and ALTER COLUMN email NVARCHAR(255) NOT NULL. SQLite cannot add NOT NULL to an existing column, so its third statement was a UNIQUE index that the data breaks.

What happens when a migration fails halfway?

On PostgreSQL, SQL Server and SQLite nothing is left behind; on MySQL and CockroachDB the statements before the failure stay applied.

Database	After statement 3 fails	Why
PostgreSQL 18.3	Nothing changed: no `phone`, no index, `credit` unchanged	DDL runs inside the transaction
SQL Server 2022	Nothing changed	DDL runs inside the transaction
SQLite 3.54	Nothing changed	DDL runs inside the transaction
MySQL 8.4 and 9.7	`phone` and its index exist, `credit = 100`	Every DDL statement commits the open transaction first
CockroachDB 26.3, default	`phone` and its index exist, `credit = 100`	`autocommit_before_ddl` commits before each schema change
CockroachDB 26.3, setting off, table unlocked	The new column was not added, but the `UPDATE` stayed	The schema change failed after the transaction committed

The MySQL and CockroachDB rows are the ones to look at closely. The ROLLBACK at the end ran without an error on both and changed nothing. The data fix at the top, which the developer wrote inside BEGIN because they wanted it undone on failure, was committed the moment the first ALTER TABLE arrived.

One condition sits under the first three rows: something has to roll back. After a failed statement, PostgreSQL refuses everything except ROLLBACK, and turns a COMMIT into a rollback. SQLite undoes only the failed statement and leaves the transaction open, so a script that sends COMMIT next keeps statements 1 and 2. Migration tools, Schemity included, roll back on the first error, which is the case the table shows.

A schema left halfway is worse than a migration that did nothing. The migration tool records the migration as failed. Running it again then fails on statement 1 instead of statement 3, because phone already exists, so someone has to work out by hand which statements ran.

Why does PostgreSQL roll back DDL, and what does that cost?

PostgreSQL rolls back DDL because ALTER TABLE and CREATE INDEX run inside the transaction like any UPDATE, and the price is that their locks last until COMMIT.

Inside the migration above, just before statement 3, pg_locks held two locks on customers: AccessExclusiveLock from the ADD COLUMN and ShareLock from the CREATE INDEX. ACCESS EXCLUSIVE blocks every read and write on the table, and it is not released when the ADD COLUMN finishes. It is released when the transaction ends. A migration that adds a column and then backfills a million rows in the same transaction blocks the table for the whole backfill. That is why adding a NOT NULL column to a large table splits the work into separate steps with a lock_timeout on each.

Two statements do not fit inside the rule, and PostgreSQL 18.3 rejected or limited both in a transaction:

CREATE INDEX CONCURRENTLY fails with ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. The CREATE INDEX page says it directly: "a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot." It needs a migration of its own, with the transaction turned off for that migration.
ALTER TYPE tier ADD VALUE 'team' runs, but SELECT 'team'::tier in the same transaction fails with unsafe use of new value "team" of enum type tier. The ALTER TYPE notes say the new value "cannot be used until after the transaction has been committed", so a migration that adds an enum value and backfills rows with it in one transaction fails.

Can you roll back a schema change in MySQL?

No: MySQL commits the open transaction before every DDL statement, and again after it, so a ROLLBACK after an ALTER TABLE has nothing left to undo.

The implicit commit page lists ALTER TABLE, CREATE INDEX and CREATE TABLE among the statements that "implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement". In the test, that is exactly what happened on 8.4.11 and on 9.7.4. Statement 3 failed with ERROR 1138 (22004): Invalid use of NULL value. The ROLLBACK succeeded. And customers still had phone, customers_phone_idx and credit = 100 on row 1.

MySQL 8.0 introduced atomic DDL, which is easy to read as the fix, and the atomic DDL page says it is not: "Atomic DDL is not transactional DDL." It makes one statement all-or-nothing. A crash in the middle of an ALTER TABLE no longer leaves the data dictionary and the table files disagreeing. A migration of five statements is still five commits.

So on MySQL, the unit of rollback is the statement. Statement-at-a-time behaviour also explains why a MySQL change can be slow and still safe: how long each statement holds the table is a separate question, measured in which MySQL ALTER TABLE changes lock the table.

Does CockroachDB roll back a failed migration?

Not by default: CockroachDB commits before each schema change, and turning that off does not make DDL and data changes fail together.

CockroachDB's session variables page describes autocommit_before_ddl as causing "any DDL statement received during an explicit transaction to auto-commit before executing the schema change", default on, "because CockroachDB does not fully support multiple schema changes in a single transaction." On v26.3.4 it announced itself on the first ALTER TABLE with NOTICE: auto-committing transaction before processing DDL due to autocommit_before_ddl setting. After that, the results matched MySQL: the NOT NULL check failed, and phone, its index and the UPDATE all stayed.

Turning the setting off is the obvious next step, and on 26.3 it stops at the first ALTER. SHOW CREATE TABLE showed the new table as WITH (schema_locked = true), and with the setting off, ALTER TABLE customers ADD COLUMN fax STRING inside BEGIN was refused: this schema change is disallowed because table "customers" is locked and this operation cannot automatically unlock the table. CockroachDB's own hint says that locking "improves changefeed performance". Unlocking every table you migrate trades that away.

With the table unlocked and the setting off, the migration ran inside one transaction, and the result was still not all-or-nothing. CockroachDB validates the NOT NULL after the transaction commits, and it reported:

ERROR: transaction committed but schema change aborted with error:
(23502): validation of column "email" NOT NULL failed on row: id=2, email=NULL, ...
SQLSTATE: XXA00
HINT: Some of the non-DDL statements may have committed successfully,
but some of the DDL statement(s) failed.

Afterwards, fax did not exist and the UPDATE in the same transaction had been kept. On CockroachDB, keep data changes and schema changes in separate migrations, whatever the setting says.

How should you write migrations for a database that cannot roll them back?

Assume each statement commits on its own, and write the migration so the statement most likely to fail can fail before anything else has changed.

Check the data first. SELECT count(*) FROM customers WHERE email IS NULL before a NOT NULL, and a GROUP BY ... HAVING count(*) > 1 before a unique index. The statements that fail most often are the ones that check existing rows: NOT NULL, CHECK, UNIQUE and foreign keys. Lock timeouts, name clashes and type conversions are the other usual causes.
Put the checking statement first where the order allows. A migration whose only failing statement runs first fails clean, even on a database that commits each statement.
Keep data changes out of schema migrations. A backfill in its own migration can be rerun; one that gets committed as a side effect of the next ALTER TABLE cannot be told apart from a successful run.
Keep migrations small. One schema change per migration on MySQL and CockroachDB means a failure leaves a state the migration tool can describe: done or not done.
Write the reverse statements before you apply. On these two databases, DROP INDEX customers_phone_idx and ALTER TABLE customers DROP COLUMN phone are your rollback.

On PostgreSQL the transaction does the cleanup, so the list there is about locks rather than leftovers. Keep each transaction short and give it a lock_timeout.

How Schemity shows which statement will fail before you apply it

Schemity's impact analysis names the statements that can fail on existing rows, and its migration dialog says whether your database will roll back the rest.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Make customers.email required on a connected diagram, or let an AI agent propose it, and impact analysis puts the SET NOT NULL under Can fail on existing data with the table's estimated row count. Its Count exactly button runs SELECT COUNT(*) FROM customers WHERE email IS NULL when you ask, so you know before applying whether statement 3 will fail. A new unique index gets the same treatment for duplicates. That is what it means to see the impact of every change: the failure moves from the middle of a migration to the review before it.

The second finding is the PostgreSQL cost from earlier in the post: the NULL check holds back reads and writes to customers while it scans the table, so the drawer shows both whether statement 3 can fail and what it blocks while it runs. On CockroachDB the same change shows only the first finding: it runs the NULL check as a background job, and while it checked 2,000,000 rows for 17 seconds, a SELECT, an INSERT and an UPDATE on the table each came back in about a quarter of a second.

The migration dialog then states what a failure will leave behind, worded for the database:

Connected to	The dialog says
PostgreSQL, SQL Server, SQLite	"All SQL migrations are executed within a transaction, ensuring data integrity and safety. If any errors occur, changes will be automatically rolled back."
MySQL	"MySQL commits each DDL statement immediately. If a later statement fails, earlier statements remain applied and cannot be rolled back. Review the SQL carefully before applying."
CockroachDB	"CockroachDB commits each schema change immediately. If a later statement fails, earlier statements remain applied and cannot be rolled back. Review the SQL carefully before applying."

CockroachDB has its own database type in the connection dialog. A diagram created as PostgreSQL before that still gets the CockroachDB warning, because Schemity also checks the server's version string; without that check, a CockroachDB connection would get PostgreSQL's promise of a rollback. The setup is in connecting to CockroachDB. If a statement does fail, the error names the statement's number and the database's message, and a notification repeats it, so a long migration does not hide which statement failed. The migration SQL is there to read in full before you press Apply.

Schemity does not reorder your statements or split a migration into smaller ones, and it does not change autocommit_before_ddl. It tells you which statement will fail and what the database will keep. The order is still yours.

Transactional DDL by database, in one list

Only some databases undo a failed migration, and on MySQL and CockroachDB a BEGIN around it does not change that.

PostgreSQL, SQL Server and SQLite roll back the whole migration. On PostgreSQL every lock is held until COMMIT, and CREATE INDEX CONCURRENTLY must run outside a transaction.
MySQL commits before and after every DDL statement. Atomic DDL makes each statement all-or-nothing, not the migration, and data changes made before the DDL commit with it.
CockroachDB commits before each schema change by default. With autocommit_before_ddl off, locked tables refuse the ALTER, and unlocked ones can keep the data changes when a schema change fails after commit.
On MySQL and CockroachDB, check the data first, run the statement that can fail first, keep data changes in their own migration and write the reverse statements before applying.

Where the failure can be caught before it runs, it usually is a constraint meeting old rows, as with the keys in PostgreSQL deferrable foreign keys. Knowing which schema actually reached production after a half-applied migration is what tracking schema changes in PostgreSQL and comparing staging and production schemas cover.

Navicat Data Modeler Alternative: Database Design Software Without the Enterprise Edition

Son Tran — Fri, 06 Nov 2026 02:00:24 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Navicat Data Modeler 5 draws models for free in its Lite edition, but reverse engineering, Import SQL, SQL export and both synchronize directions are Enterprise only: US$749 perpetual or US$379.99 a year. Schemity reads the live database on every license, prices each schema change before it runs, keeps the diagram as JSON in Git, and opens a Navicat .nmodel file with its table positions.

Navicat Data Modeler draws database models in every edition, but only its Enterprise edition can read a database, import or export SQL, or synchronize a model with a database, so the Navicat Data Modeler alternative worth weighing depends on whether you need that round trip. If you need Oracle, MongoDB, or conceptual and Data Vault models, stay with Navicat. If you design the physical schema of a database that already runs, you are paying US$749, or US$379.99 a year, for the part that touches it. Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

This page compares the two as of 5 November 2026, from Navicat's own product pages, feature matrix, store and online manual for Navicat Data Modeler 5, which Navicat released in September 2026 (sometimes called Navicat Modeler): what each edition can do with a real database, what it costs over three years, how the model stays in step with the database, where the model file lives, and where Navicat is the better choice.

Which Navicat Data Modeler Edition Can Read a Database?

Only Enterprise: the free Lite edition draws models but cannot reverse engineer a database, import or export SQL, or synchronize in either direction.

Navicat Data Modeler comes in two editions, the free Lite and the paid Enterprise (sold at a lower price as a Non-Commercial license, which Navicat restricts to "educational and non-profit use"). The feature matrix splits them like this:

Feature	Lite (free)	Enterprise
Conceptual, logical and physical models	Yes	Yes
Crow's Foot, IDEF1X and UML notation	Yes	Yes
Layers, notes, images, print to PDF and images	Yes	Yes
Reverse database to model	No	Yes
Import SQL to model	No	Yes
Export model to SQL	No	Yes
Copying SQL from diagram objects	No	Yes
Synchronize model to database, and database to model	No	Yes
Compare model workspaces, model conversion	No	Yes
Auto layout, data dictionary	No	Yes
Design table schemas with AI	No	Yes

The online manual labels each of the database features "Available only in Enterprise Edition". So Lite is for drawing: you can model a schema in it, but nothing you draw reaches a database, not even as SQL you could copy, and nothing in a database reaches the model.

The same model features also come inside Navicat Premium 18 Enterprise, which its manual marks "Available only in Enterprise Edition", and inside the Enterprise edition of single-database products such as Navicat for MySQL. If your team already owns one of those, you may already have the modeler.

In Schemity, every license connects to the database. Reverse engineering reads PostgreSQL (including Supabase and CockroachDB), MySQL, MariaDB, SQL Server, SQLite (including Cloudflare D1) and Snowflake into a diagram, and Import SQL reads a dump or CREATE TABLE statements. There is no edition to upgrade to.

What Does Navicat Data Modeler Cost in 2026?

Working against a real database costs US$749 perpetual for Enterprise, with one year of maintenance included, or US$37.99 a month, or US$379.99 a year.

Verified on Navicat's plan page and store on 5 November 2026, in US dollars, for one user:

Edition	Perpetual	Monthly	Yearly	Reads a database
Lite	Free	-	-	No
Non-Commercial	US$379	US$18.99	US$189.99	Yes, for educational and non-profit use
Enterprise	US$749	US$37.99	US$379.99	Yes

A perpetual license includes a year of maintenance, which Navicat says covers "software upgrades, live chat, technical support and key reset requests." At purchase you can add a second year for US$119.84 or a second and third year for US$179.76. The license "is per user base", a key works on Windows, macOS or Linux, and five or more perpetual licenses earn a volume discount. Navicat's site offers local versions by region, and its store runs promotions, so the price you see may differ; the figures here are the US list prices with no coupon applied.

Over three years with updates throughout, for one engineer:

	Up front	Years 2 and 3	Three years
Navicat Data Modeler Enterprise, perpetual	US$749	US$179.76	US$928.76
Navicat Data Modeler Enterprise, yearly subscription	US$379.99	2 x US$379.99	US$1,139.97
Schemity	$129	2 x $29	$187

Two licensing details matter on a locked-down machine. A subscription "contacts our licensing server once per hour", and a perpetual key can be activated offline, but only through a second computer with internet access that visits Navicat's activation page. Navicat also says a perpetual license and a subscription "cannot be used at the same Navicat Data Modeler."

Schemity is $129 once, with a year of updates, an optional $29 a year after that, use on up to three of your machines, and a two-week free trial with every feature. Navicat's own trial is 14 days, and Lite is free for drawing. Schemity's free use after the trial is also design-only, with no time limit, and it still imports SQL, which Lite cannot.

Does Navicat Data Modeler Keep the Model in Sync With the Database?

Yes, in Enterprise, in both directions: it compares the model with a database and writes a deployment script, and it can pull a database's changes back into the model.

Credit to Navicat here, because this is a real feature. Synchronize Model to Database compares a model with a connection, lists the differing objects, and shows two tabs before anything runs: a DDL Comparison of each object's statements on both sides, and a Deployment Script with "the specific scripts that will be executed on the target database". You can reorder the script, and an option to "Continue on error" lets the run carry on past a failing statement. Synchronize Database to Model runs the same compare the other way.

What the wizard shows you is SQL. The manual's page for it says nothing about the rows already in the target tables, and that is where the expensive questions live: how many rows a dropped column takes with it, whether an ALTER TABLE ... ADD CONSTRAINT will fail on data that already breaks it, which views depend on the column, and whether the change rewrites a large table. None of that is in the statement, and the wizard's answer to a statement that fails is an option to carry on past it.

In Schemity, a connected diagram is read again from the database every time you open it, and Refresh re-reads it on demand after a migration ran elsewhere: tables you placed keep their position, dropped ones disappear, and new ones arrive placed clear of the rest. A change you draw becomes a migration SQL diff against the real database, and impact analysis prices it before it runs: the rows a drop would take, the statements that would fail on existing data, the dependent views that would block or break, and the statements that would rewrite or lock a large table. So you see the impact of every change before it runs: nothing is applied until you approve it, and a migration file written by an ORM or an AI agent is analysed the same way without being run.

Where Does a Navicat Model Live?

A current Navicat workspace is one .nmodel archive that Navicat shares through Navicat Cloud or its On-Prem Server, while a Schemity diagram is a file in the repository next to the code it describes.

Navicat's release notes name the .ndm2 file of Data Modeler 3 and 4, a single JSON file. Navicat 18's manual names the newer format: "Workspace files (.nmodel) are stored under the Profiles Location." We opened the one in Schemity's own test suite, saved by Navicat 18: six models in four dialects, 37 tables in all, make a 268 KB tar archive of 188 gzip-compressed JSON files, one per object and named by UUID, plus a small JSON index.

Navicat's answer is its own sharing. A workspace can be saved to Navicat Cloud or to a Navicat On-Prem Server, which can push a notification to each member's copy when a shared file changes. That works, and it keeps the model inside Navicat's services rather than next to the code it describes.

A Schemity diagram is a plain JSON file in a workspace folder you choose. Put the folder in the application repository and the schema design is versioned with the code it describes: the same branch, the same history, blame and revert. Nobody reviews a schema change by reading that JSON, though: colours, positions and sizes change with every edit and would bury the one column that matters. A reviewer opens the pull request's migration file in Schemity's change preview and sees it drawn on their own diagram, with its impact, or opens the updated diagram against a database the migration has not reached yet and sees the same preview there.

How Do You Move a Navicat Model to Schemity?

Open the .nmodel file with Import Navicat on a new diagram's empty canvas, and the chosen diagram's tables arrive with their keys, indexes, check constraints, comments and positions.

If the file holds several diagrams, you pick one; when they span several databases, each is labelled with its own (a single .nmodel can hold a MySQL model next to a PostgreSQL one). Import Navicat reads MySQL, MariaDB, PostgreSQL, SQLite and SQL Server models, and column types become your diagram's database's own, so a MySQL model can start a PostgreSQL design; checks, defaults and indexes that do not carry over to the new database are counted in the drawer. What Schemity does not draw, the model's views, triggers, functions and sequences, is left out and counted in the drawer, so you know what did not come across.

Two cases it does not cover, stated plainly. Navicat Oracle models are refused by name, because Schemity has no Oracle dialect. And the older .ndm2 files of Data Modeler 3 and 4 use a different layout that the import does not read. For either, export the model to SQL in Navicat (an Enterprise feature) and use Import SQL, or connect Schemity to the database the model describes and read it directly. The same canvas also opens the other model formats Schemity imports: DBML, Visual Paradigm, MySQL Workbench and DbSchema.

How Does AI Work in Navicat Data Modeler and in Schemity?

Navicat runs an AI assistant inside the modeler on a provider key you supply, in Enterprise only; Schemity runs no model and gives the agent you already use a local MCP server instead.

Navicat Data Modeler 5 can design tables, optimize them, fix views and fill comments with AI. Its manual says the assistant "relies on external AI providers", so you bring an account and an API key: OpenAI, Gemini, Claude, Grok, DeepSeek and others, or a local model through Ollama or LM Studio. With Ollama or LM Studio the model runs locally, which is a fair answer to the privacy question.

Schemity takes the other route. It is a local database MCP server with 18 tools for Claude Code, Claude Desktop, Cursor, Codex, OpenCode or any MCP host: the agent can read the schema, analyse a migration file it has written, and propose changes that land on your diagram as unsaved edits for you to review. The agent never gets the database credentials, and nothing it does writes to a database.

Navicat Data Modeler vs Schemity

Navicat Data Modeler is broad data modeling software whose database round trip starts at the US$749 Enterprise edition, and Schemity is database design software that reads the database and previews each schema change against it, for $129 once.

	Navicat Data Modeler 5	Schemity
What it is	Data modeling software: conceptual, logical, physical and hybrid models	Database design software, desktop app (macOS, Windows, Linux)
Reads a live database	Enterprise edition (US$749, or US$379.99/yr)	Every license
Databases	MySQL, PostgreSQL, MongoDB, MariaDB, SQL Server, Oracle, SQLite, Snowflake, plus cloud services such as Redshift	PostgreSQL (Supabase, CockroachDB), MySQL, MariaDB, SQL Server, SQLite (and Cloudflare D1), Snowflake
Keeping it current	Synchronize database to model, run on demand	Read again on every open and on Refresh, layout kept
Changing the database	Synchronize model to database: DDL comparison and a deployment script	Migration SQL plus impact analysis against the live database, applied only on approval (Snowflake and Cloudflare D1 diagrams are read-only)
Model levels	Conceptual, logical, physical and hybrid; relational, dimensional and Data Vault 2.0	Physical, the schema the database runs
Notation	Crow's Foot, IDEF1X and UML	Crow's foot
Where models live	One binary model file; sharing through Navicat Cloud or On-Prem Server	JSON files in a folder you choose, ready for Git
AI	Built-in assistant on your own provider key, Enterprise only	Local MCP server with 18 tools for the agent you already use
Free use	Lite edition, drawing only; 14-day trial of Enterprise	Two-week trial with everything; after it, design-only diagrams with PNG, JPEG and JSON export
Price for database work	US$749 plus US$119.84 for a second year, or US$379.99/yr	$129 once, optional $29/yr for updates

Where Navicat Data Modeler Wins

Navicat Data Modeler is the better choice when you model Oracle, MongoDB or another database Schemity does not connect to, when you need conceptual, logical, dimensional or Data Vault models, or when your team already shares models through Navicat.

More databases. Navicat models MongoDB, Oracle, OceanBase, TiDB and several more alongside the relational databases Schemity covers. Schemity connects to PostgreSQL (and Supabase and CockroachDB), MySQL, MariaDB, SQL Server, SQLite (and Cloudflare D1, read over its HTTP API with an account ID and API token) and Snowflake and goes deep on them; it will not follow you to MongoDB or Oracle.

More model levels. Navicat keeps conceptual, logical and physical models, converts between them, adds a hybrid model, and supports dimensional and Data Vault 2.0 methods. Schemity models the physical schema only, the one the database actually runs.

Governance features. Data Modeler 5 added naming standards and a glossary you can load from CSV, plus bulk schema editing. Schemity has schema lint for the schema itself, but no naming-standards dictionary.

More notations. IDEF1X and UML are a setting per diagram in Navicat. Schemity draws crow's foot.

A free edition for a small team. Lite is free for up to five users per organization, with Navicat's collaboration projects. Schemity's free use after the trial is design-only.

Already in the family. If your team owns Navicat Premium Enterprise, the modeler is already in your license.

If the physical schema is the job and the database already exists, compare the other alternatives too: the DbSchema alternative, for a desktop database IDE that covers even more engines, the MySQL Workbench alternative for MySQL shops, and the Visual Paradigm alternative for a modeling suite with a similar edition split.

Visual Paradigm Alternative: Database Design Software Without the Modeling Suite

Son Tran — Tue, 03 Nov 2026 10:54:21 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Visual Paradigm draws ERDs in every edition, but its edition matrix keeps reverse engineering from a database, the database generation wizard and database patching for Standard (US$349 perpetual, or US$19 a month) and up, with updates after the first year at US$100 a year. Schemity is database design software and nothing else: it reads PostgreSQL, MySQL, SQL Server, SQLite or Snowflake, shows what a schema change will do to the real database before it runs, keeps each diagram as a JSON file in Git, and gives an AI agent a local MCP server, for $129 once.

Visual Paradigm is a modeling suite in which the ERD is one diagram type among dozens, and the Visual Paradigm alternative worth considering depends on whether you use the rest. If your team models UML, SysML and BPMN in the same project, keep it. If the ERD is the only diagram you open, you are paying for a suite and still need the US$349 Standard edition before it will read your database. Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

This page compares the two as of 3 November 2026, from Visual Paradigm's own shop, edition comparison and user guide: what each edition can do with a real database, what it costs over three years, where the diagrams live, how AI fits in, and where Visual Paradigm is the better choice.

This is a Visual Paradigm ERD of a real schema, a staff skills and evaluation system of nearly 50 tables that we designed in Visual Paradigm for a project that has since been retired:

And this is the same project in Schemity, read from the database it ended up with: about 60 tables, Django's own auth tables and a notification module included, grouped by module into eight colored legends:

The two do not match table for table, and that is the point. The Visual Paradigm diagram is the design as it was last updated by hand; the database kept changing after that, and the diagram fell behind it. It often felt built for a different kind of modeling than ours, which, as a UML suite, it is. That gap, and the smaller frustrations further down, are what years of Visual Paradigm taught us, and why we built Schemity.

Both tools draw a schema well. The questions below are about everything around the drawing: getting a real database into it, keeping it current, and what it costs to do either.

Which Visual Paradigm Edition Can Reverse Engineer a Database?

Only Standard, Professional and Enterprise: Modeler and the free Community edition draw ERDs but cannot read a database or patch one; Modeler can only write create and drop scripts for the entities you select.

Visual Paradigm's edition comparison splits database work into two groups. The Database Design Toolset, which every edition has, covers drawing: entity relationship diagrams in crow's foot and Chen notation, conceptual, logical and physical models, stored procedure and trigger shapes, sample records, and ERwin import. The Database Engineering group is where a diagram meets a real database, and every row in it is marked for Enterprise, Professional and Standard only:

Database Engineering row	Enterprise	Professional	Standard	Modeler	Community
Reverse engineer ERD from database	Yes	Yes	Yes	No	No
Reverse engineer ERD from DDL	Yes	Yes	Yes	No	No
Generate DDL from ERD	Yes	Yes	Yes	No	No
Generate database from ERD	Yes	Yes	Yes	No	No
Patch database from ERD	Yes	Yes	Yes	No	No

So the US$99 Modeler edition is a drawing tool for databases: you can model a schema in it, but you cannot point it at the database you already have, and the matrix marks the generation wizard Standard and up.

We checked in Visual Paradigm Modeler 17.2 (build 20260424) on 3 November 2026. Its Tools > DB menu does list Generate Database, Reverse Database and Reverse DDL, and each one opens the same dialog instead of running:

Only Database Configuration opens, so you can set up a connection to PostgreSQL or Snowflake in Modeler, but not use it to read or write a schema.

Modeler does write SQL for the entities you select. Select one or several and Generate SQL... from the right-click menu opens Visual Paradigm's Ad-Hoc SQL generator, with a CREATE TABLE for each, the foreign keys between them as ALTER TABLE ... ADD CONSTRAINT, and the matching DROP statements:

That is a script for a new, empty database. With nothing to compare the model against, it cannot say what to ALTER in a database that already exists, which is what a migration needs; comparing the model with a database is the Standard edition's Database Engineering. Two more design features also start at Standard: the Database View editor, and synchronization between the conceptual, logical and physical models.

Schemity sells one license with every feature. It connects to a database, directly or, for server databases, over an SSH tunnel, and reverse engineers the schema: tables, columns, types, defaults, CHECK constraints, unique constraints, indexes, views, and every foreign key as a relationship with its ON DELETE and ON UPDATE actions. It reads PostgreSQL (Supabase and CockroachDB included), MySQL and MariaDB, SQL Server, SQLite and Snowflake, and with no database at all, a design-only diagram still writes CREATE TABLE SQL for any of the five dialects.

Nothing in that diagram was typed: every table, type, default, key and the view came from the database.

What Does Visual Paradigm Cost for ERD Work in 2026?

Working against a real database costs US$349 perpetual for Standard, plus US$100 a year if you want updates after the first year, or US$19 a month as a subscription.

Verified on Visual Paradigm's shop and subscription page on 3 November 2026, for one user:

Edition	Perpetual, single seat	Maintenance per extra year	Subscription	Reads a database
Community	Free, "for non-commercial use only"	-	-	No
Modeler	US$99	US$30	US$6/mo	No
Standard	US$349	US$100	US$19/mo	Yes
Professional	US$799	US$200	US$39/mo, US$35 billed quarterly	Yes
Enterprise	US$1,999	US$600	US$99/mo, US$89 billed half-yearly	Yes

A perpetual license includes one year of maintenance. After that, the shop says, "you can continue using the last version of Visual Paradigm you downloaded, indefinitely", and maintenance buys the updates: Visual Paradigm ships "two to three versions per year", and reinstating maintenance that has lapsed costs more than extending it (US$150 instead of US$100 for Standard). A floating license, shared by concurrent users, costs more again (US$449 for Standard) and needs a Floating License Server.

Over three years with updates throughout, for one engineer:

	Up front	Years 2 and 3	Three years
Visual Paradigm Standard, perpetual	US$349	2 x US$100	US$549
Visual Paradigm Standard, subscription	-	36 x US$19	US$684
Schemity	$129	2 x $29	$187

Schemity is $129 once, with a year of updates, an optional $29 a year after that, use on up to three of your machines (the same limit as a Visual Paradigm single seat), and a two-week free trial with every feature. Visual Paradigm's own 30-day trial is longer, and its Community edition is free for non-commercial use, which Schemity has no equivalent of.

Does Visual Paradigm Keep the ERD in Sync With the Database?

It can reverse engineer the database again and patch the database from the diagram, but we found no step in its user guide that merges a changed database into a diagram you have already arranged.

Visual Paradigm's reverse engineering offers to "Form a new diagram with the reversed entities" or add them to the first ERD in the project, and warns that "having too many entities on one diagram may result in a poor database design that is hard to read and maintain." Going the other way, its database patching is a real and useful feature: "We compare the differences between your design and a database, and patch the differences to your database accordingly." You can have it write the DDL to a file to review before anything runs, and an option will "drop tables and/or columns that do not exist in your ER diagram."

What the patch flow shows you is SQL. Reading that SQL is where the expensive questions live: how many rows a dropped column takes with it, whether a statement will fail on rows already in the table, which views read the column, and whether the change locks or rewrites a large table. None of that is in the statement.

In Schemity, a connected diagram is read again from the database every time you open it, and Refresh re-reads it on demand after a migration ran elsewhere: tables you placed keep their position, dropped ones disappear, and new ones arrive placed clear of the rest. A change you draw becomes a plan against the real database, and impact analysis prices it before it runs: the rows a drop would take, the statements that would fail on existing data, the dependent views that would block or break, and the statements that would rewrite or lock a large table. Lint adds what is wrong in the schema itself, such as a foreign key with no index or a type that does not match the key it references. Nothing is applied until you approve the migration SQL (a Snowflake diagram is never migrated at all), and a migration file written by an ORM or an AI agent can be analysed the same way without being run.

Delete orders.placed_at from the shop diagram above and open Preview changes:

The statement at the top is all a patch script would show. The findings under it are the two facts that decide whether to run it: the column holds about 200,000 values, and PostgreSQL would refuse the statement until the view order_totals stops reading it.

What Did We Run Into Drawing a Real Schema in Visual Paradigm?

Seven smaller things, all in drawing the schema itself: creating a relationship, self-referencing relationships, PostgreSQL arrays, unique constraint names, composite unique markers, allowed values, and copy and paste.

Creating a relationship. Drag a relationship out from one table and Visual Paradigm opens a picker with two rows of the same four relationship symbols, in a different order and with no labels; the top row may be the recently used ones, but nothing on screen says so:

Drop it on the foreign key column of the other table instead, and a second picker appears, this time with four symbols on top and five below, each paired with an entity icon:

In Schemity you drag from the parent table onto the child, and Schemity adds the foreign key column to the child, pointing at the parent's primary key. The relation dialog then opens with both tables and the new column filled in, and asks its questions in words: a relation or a virtual one, 1:N, 1:1 or N:N (which builds the junction table), and the ON DELETE and ON UPDATE actions. Dropping on empty canvas lets you pick the target table by name instead.

Self-referencing relationships. Visual Paradigm would not let us draw a self-referencing relationship by dragging a relationship from a table back onto itself, which is how positions.parent_id points at its own table. There may be another way to make one; we did not find it, and its guide to drawing entities does not cover the case. In Schemity you drag the new-relation handle off a table and drop it back on the same table, and the self-reference is drawn, as it is on positions above.

PostgreSQL arrays. We found no array type in Visual Paradigm's PostgreSQL type list, so to make a column such as evaluate_campaigns.attended_titles show as an array on the canvas, we gave it the base type int4 and typed int[] into its User type field:

The canvas then reads int[], but the type itself is still int4 with a label beside it. In Schemity, a PostgreSQL column has an array selector next to its type, so attended_titles arrives from the database above as INTEGER[], is written back as one, and is what lint checks when an array holds foreign keys.

Unique constraint names. Visual Paradigm will not save a unique constraint without a name you type yourself: leave Name empty, as here for (hard_level, group) on skill_source_questions, and it stops with "name cannot be empty":

Which columns are unique together. Once the constraint is saved, the canvas does not say which columns are unique together. On skill_source_questions, title is unique on its own and hard_level and group are unique as a pair, yet all three carry the same mark:

members is harder still. Three unique constraints share tenant_id: (tenant_id, user_id), (tenant_id, sso_sub) and (tenant_id, sso_id). Only the Constraints tab of the Entity Specification says so; the canvas gives tenant_id one U and marks user_id, sso_sub and sso_id as if each were unique on its own:

Schemity names a unique constraint for you when you leave the name empty, in PostgreSQL's own pattern: skill_source_questions_hard_level_group_key. On the canvas, a single-column unique is a plain U and a composite unique is an underlined U; a column in several unique constraints gets one U for each, and each constraint can take its own color. So (hard_level, group) reads as one rule and title as another, and on members, tenant_id carries three underlined U marks, one per constraint; give each constraint its own color and the U on its partner column matches.

Allowed values. members.status takes one of a fixed set of values, ACTIVE among them, but the entity has no place for that set, so the column reads text = ACTIVE and nothing more. Its Column Specification does have a Check constraint field that takes the rule as text, but the values never show on the diagram, so we kept them in a sticky note or the column's description, apart from the DDL they belong to:

In Schemity the values are part of the column. A CHECK (status IN (...)) list or a PostgreSQL enum type is read from the database with the column; a column that has one is underlined on the canvas, and so is a default that is one of its values, as status and ACTIVE are on the same members table in Schemity above. Open the field and its Default control offers the values the CHECK allows, ACTIVE and INACTIVE, because they are part of the column, not a note beside it:

View options (Cmd+O, or Ctrl+O on Windows and Linux) lists the values without opening the field editor at all:

And lint flags a status-like column with nothing restricting its values, so the set that Visual Paradigm left to a sticky note is something Schemity asks you for.

Copy and paste. Copy an entity and paste it with Ctrl-V, and Visual Paradigm does not make a copy: it pastes a second view of the same entity, marked a for auxiliary view, while the original is marked M for master view. The two shapes are one table, so editing either one edits both:

This is deliberate. Visual Paradigm's copy and paste guide says pasting as view presents "the same model element in another view", while Paste Model from the right-click menu, or Duplicate, makes a real copy, and its Know-how site explains how to bind Ctrl-V to Paste Model instead. It makes sense for a model shown on many diagrams. In a database diagram, where each shape is one CREATE TABLE, it is the opposite of what Ctrl-V does everywhere else. In Schemity, pasting skill_source_answers creates skill_source_answers_copy, a separate table that the migration creates on its own.

Where Do Visual Paradigm and Schemity Keep the Diagram?

Visual Paradigm keeps a project in one .vpp file and versions it through its own repository; Schemity keeps each diagram as a JSON file you commit to Git like code.

Visual Paradigm "saves all project content to a single file, with file extension .vpp", and its user guide names "Visual Paradigm VPository and VP Teamwork Server" as "the supported standards of versioning systems", with commit, update and merge run from Visual Paradigm's own Teamwork Client. That is a complete system for a team that adopts it, and a separate one from the repository the application code lives in.

A Schemity diagram is a plain JSON file in a workspace folder you choose. Put the folder in the application repository and the schema design is versioned with the code it describes: the same branch, the same pull request, the same history. A reviewer does not need to read JSON to review a schema change either; they open the pull request's migration against their own connected diagram and see it drawn and priced in the change preview.

How Does AI Work in Visual Paradigm and in Schemity?

Visual Paradigm's AI ERD generation runs through its cloud service on the Professional and Enterprise editions with active maintenance; Schemity gives the AI agent you already use a local MCP server.

Visual Paradigm's AI Diagram Generator can turn a description of business objects into an ERD. Its shop marks AI diagram generation for Enterprise and Professional only, groups the AI apps as "via VP Online", and states that "active maintenance is required to continue using any AI-powered features." A custom AI endpoint lets a workspace manager bring an API key for providers such as OpenAI, Anthropic or Google; for "an internally hosted model", the page points to its enterprise team. We did not find an MCP server in Visual Paradigm's documentation as of November 2026.

Schemity does not run a model of its own. It is a local database MCP server with 18 tools for Claude Code, Claude Desktop, Cursor, Codex, OpenCode or any MCP host: the agent can read the schema and its context views, analyse a migration file it wrote, run lint, and propose changes, which land on your open diagram as unsaved edits for you to review and save. Schemity itself sends your schema nowhere; what reaches a model is whatever your agent's host sends, under the agreement you already have with it.

Visual Paradigm vs Schemity

Visual Paradigm is a modeling suite whose database engineering starts at the US$349 Standard edition, and Schemity is database design software that reads the database and previews each schema change against it, for $129 once.

	Visual Paradigm	Schemity
What it is	Modeling suite: UML, SysML, BPMN, ERD and more in one project	Database design software, desktop app (macOS, Windows, Linux)
Reads a live database	Standard edition and up (US$349)	Every license
Databases	MySQL, SQL Server, Oracle, PostgreSQL, SQLite, DB2, Sybase, MariaDB, Informix, Firebird and more	PostgreSQL (Supabase, CockroachDB), MySQL, MariaDB, SQL Server, SQLite, Snowflake
Keeping it current	Reverse again, into a new ERD, the first ERD, or an entity list you drag onto diagrams by hand	Read again on every open and on Refresh, layout kept
Changing the database	Patch: compares the ERD with the database and writes the `ALTER` and `CREATE` statements, to the database or to a file to review first	Migration SQL plus lint and impact analysis against the live database, applied only on approval
Model levels	Conceptual, logical and physical, synced from Standard up	Physical, the schema the database runs
Notation	Crow's foot and Chen	Crow's foot
Where diagrams live	One `.vpp` file per project; versioning through VPository or Teamwork Server	JSON files in a folder you choose, ready for Git
Large schemas	Several ERDs per project, plus sub-diagrams per model element	Context views per domain and a Context Map of the dependencies between them
AI	AI ERD generation via VP Online, Professional and up, with active maintenance	Local MCP server with 18 tools for the agent you already use
Footprint	About 790 MB download; 4 GB RAM minimum, 8 GB recommended	183 MB of memory open on its home screen, measured on an M1 Pro
Free use	Community edition and VP Online Free, non-commercial only; 30-day trial	Two-week trial with everything; after it, design-only diagrams with PNG, JPEG and JSON export
Price for database work	US$349 plus US$100/yr for updates, or US$19/mo	$129 once, optional $29/yr for updates

Where Visual Paradigm Wins

Visual Paradigm is the better choice when the ERD is one model among many, when you need conceptual and logical models, Chen notation, stored procedures or Oracle and DB2, or when your team already works in its repository.

For years Visual Paradigm was still the best tool we could find for this work, frustrations included; the others we tried were worse.

One suite for every model. UML, SysML, BPMN and the ERD live in one project, and from Standard up Visual Paradigm can synchronize an ERD with a UML class diagram and generate Hibernate code from it. Schemity draws databases and nothing else.

Conceptual and logical models. Visual Paradigm models a schema at three levels and, from Standard up, keeps them in sync. Schemity models the physical schema, the one the database actually runs, and does not do a conceptual model with generalization.

More of the database. Stored procedures and triggers are shapes on a Visual Paradigm ERD, and its database list reaches Oracle, DB2, Informix, Sybase and Firebird. Schemity draws tables, views, keys, constraints and indexes, not procedures or triggers, though its impact analysis does name the triggers, functions and procedures a change would affect, and it connects to the databases listed above.

Migrating from other modeling tools. Visual Paradigm imports ERwin projects in every edition and PowerDesigner models from Modeler up. Schemity imports SQL, so a model from those tools arrives as the DDL they generate.

Documentation and team process. Doc. Composer builds formatted design documents from Standard up, and VPository gives a team commit, merge and revision history inside Visual Paradigm. Schemity exports a data dictionary and leaves versioning to Git.

A free edition. Community is free for non-commercial ERD work, with no time limit. Schemity's free use after the trial is design-only.

If the ERD is the only diagram you need and the database already exists, compare the other alternatives too: the DbSchema alternative, for a desktop database IDE that covers far more database engines, the MySQL Workbench alternative, and the ChartDB alternative for a browser-first view.

MySQL ALTER TABLE: Which Changes Lock the Table, and How to Make One Fail Instead of Copying

Son Tran — Tue, 03 Nov 2026 02:44:57 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: On MySQL 8.4, adding, dropping or renaming a column and changing a default are metadata-only, and an index or NOT NULL builds while writes continue. Other type changes, including a VARCHAR that crosses 255 bytes, a CHECK and a foreign key with checks on copy the table and hold back writes until the copy ends. Every ALTER TABLE, instant ones included, first waits for an exclusive metadata lock, and new queries on the table queue behind it.

On MySQL 8.4, most ALTER TABLE statements do not hold the table for their whole run. Adding, dropping or renaming a column and changing a default only edit metadata, and adding an index or a NOT NULL builds while reads and writes continue. Among the common changes, the ones that copy the table, and hold back writes until the copy ends, are a data type change (including a VARCHAR that grows past 255 bytes), a new CHECK and a foreign key added with checks on. And every one of them, instant ones too, first needs an exclusive metadata lock, which is how a 0.1-second ADD COLUMN can stop reads on the table for 17 seconds.

Everything below ran on MySQL 8.4.11 in a throwaway Docker container on a laptop, against a users table of 1,000,000 rows (id INT AUTO_INCREMENT, email VARCHAR(50) NOT NULL, name VARCHAR(100) NULL, status VARCHAR(20), created_at DATETIME, all utf8mb4) and an orders table of 1,000,000 rows pointing at it. Each change in the table below ran on a fresh copy of users with no foreign key on it, so every row starts from the same table. Read the timings as ratios between kinds of change, not as what your server will do.

Does MySQL ALTER TABLE lock the table?

Briefly, always; for the whole operation, only when MySQL decides to copy the table.

When a statement names no algorithm, the ALTER TABLE documentation says MySQL "uses ALGORITHM=INSTANT" where it is supported, "otherwise, ALGORITHM=INPLACE is used. If ALGORITHM=INPLACE is not supported, ALGORITHM=COPY is used." INSTANT edits only the data dictionary. INPLACE builds without copying rows into a new table and usually lets writes continue. COPY builds a new table row by row, and while it does, reads go through, except for a moment at the final swap, and writes wait. Which one a change gets is decided by the change, not by you. The algorithm column below is the one MySQL accepted when the statement named it, since MySQL does not report which one it picked:

Change on 1,000,000 rows	Algorithm it runs as	Writes during it	Time
`ADD COLUMN phone VARCHAR(30) NULL AFTER email`	`INSTANT`	Allowed	0.12 s
`DROP COLUMN name`	`INSTANT`	Allowed	0.06 s
`RENAME COLUMN name TO full_name`	`INSTANT`	Allowed	0.03 s
`ALTER COLUMN status SET DEFAULT 'pending'`	`INSTANT`	Allowed	0.02 s
`MODIFY COLUMN name VARCHAR(200) NULL` (was 100)	`INPLACE`, no rebuild	Allowed	0.02 s
`MODIFY COLUMN name VARCHAR(100) NOT NULL`	`INPLACE`, rebuilds the table	Allowed	1.20 s
`ADD INDEX ix_name (name)`	`INPLACE`	Allowed	1.18 s
`MODIFY COLUMN email VARCHAR(100) NOT NULL` (was 50)	`COPY`	Held back	1.92 s
`MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT`	`COPY`	Held back	1.64 s
`MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT`	`COPY`	Held back	1.68 s
`ADD CONSTRAINT ... CHECK (status IN (...))`	`COPY`	Held back	1.74 s
`ADD CONSTRAINT ... FOREIGN KEY` on `orders`	`COPY`	Held back	4.07 s

The "writes during it" column was measured, not read off the documentation. On the same table grown to 4,000,000 rows, a single-row INSERT sent 1.5 seconds into the run waited 6.3 seconds for the VARCHAR(50) to VARCHAR(100) copy and 5.9 seconds for the CHECK, while a SELECT by primary key sent at the same moment came back in about 0.1 second. During the NOT NULL rebuild and the index build, the same INSERT came back in 0.12 and 0.22 seconds. The NOT NULL row needs one more caveat: it is in-place, but with the default strict SQL mode it still fails with ERROR 1138 (22004): Invalid use of NULL value if any existing row holds a NULL.

The foreign key row is the one most often missed. The online DDL reference says adding one supports INPLACE only "when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported." With checks off, the same statement took 0.01 seconds, and it validated nothing: on a small test table, a key added that way was accepted over a child row pointing at a parent that does not exist.

Why does widening a VARCHAR sometimes copy the table?

Because the copy is triggered by the column's length prefix changing from one byte to two, and MySQL counts that at the character set's widest character, 4 bytes for utf8mb4.

A VARCHAR value carries a prefix saying how long it is. Up to 255 bytes it is one byte; from 256 bytes it is two. The documentation states the consequence: in-place ALTER TABLE "does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes." At 4 bytes per character, utf8mb4 VARCHAR(63) is 252 bytes and VARCHAR(64) is 256. So two changes that look the same in a migration file behave differently:

-- name was VARCHAR(100): 400 bytes, already a two-byte prefix
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NULL, ALGORITHM=INPLACE, LOCK=NONE;
-- Query OK

-- email was VARCHAR(50): 200 bytes, one-byte prefix; 100 characters is 400 bytes
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
-- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
-- Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

The boundary held exactly in the container: utf8mb4 VARCHAR(60) to VARCHAR(63) ran in place and VARCHAR(63) to VARCHAR(64) was refused; latin1 VARCHAR(100) to VARCHAR(255) ran in place and VARCHAR(255) to VARCHAR(256) was refused; VARBINARY(255) to VARBINARY(256) was refused too. Shrinking a VARCHAR, growing a CHAR, and VARCHAR to TEXT always copied. So the cost depends on where a column started. A utf8mb4 VARCHAR(50) copies the table the first time it grows past 63 characters, and grows in place after that; the VARCHAR(191) many utf8mb4 schemas use is already 764 bytes with a two-byte prefix, so it grows in place from the start.

How do you make MySQL refuse an ALTER instead of copying?

Name the algorithm and lock you expect: with ALGORITHM=INSTANT, or ALGORITHM=INPLACE, LOCK=NONE, MySQL refuses a change it cannot do that way instead of falling back to a copy.

The documentation is explicit that "specifying an ALGORITHM clause requires the operation to use the specified algorithm ... or fail with an error otherwise", and LOCK=NONE adds "if supported, permit concurrent reads and writes. Otherwise, an error occurs." That turns a silent decision into a reviewed one. A migration that says

ALTER TABLE users ADD CONSTRAINT users_status_check
  CHECK (status IN ('active', 'pending', 'closed')),
  ALGORITHM=INPLACE, LOCK=NONE;

fails in staging with ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. instead of holding back writes in production. Two limits on the clause: INSTANT takes no LOCK other than the default, and the clause has to be in the SQL that runs, so check whether the migration your ORM generates carries it or needs a raw-SQL step.

The clause also catches the case where an INSTANT change stops being instant without telling you. Each instant add or drop of a column creates a new row version, and the online DDL reference says "the maximum number of row versions permitted is 64 (255 as of MySQL 9.1.0)". On a fresh table, the 65th ADD COLUMN ..., ALGORITHM=INSTANT failed with ERROR 4092 (HY000): Maximum row versions reached for table t/rv. No more columns can be added or dropped instantly. Please use COPY/INPLACE. The same ADD COLUMN without the clause succeeded with no warning, and information_schema.INNODB_TABLES showed why: TOTAL_ROW_VERSIONS went from 64 to 0, because MySQL had quietly rebuilt the whole table.

Why can an instant ALTER TABLE still stop reads?

Because every ALTER TABLE first takes an exclusive metadata lock, it cannot get one while any open transaction has touched the table, and new queries queue behind it while it waits.

The metadata locking page explains both halves. A transaction keeps its metadata locks "until the transaction ends", so "a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends". It also says "write lock requests have higher priority than read lock requests", and in the container a waiting ALTER held back every query that arrived after it:

Session A: BEGIN; SELECT id FROM users WHERE id = 1; and then nothing for 20 seconds, the shape of a slow report or a forgotten console.
Session B, one second later: ALTER TABLE users ADD COLUMN nickname VARCHAR(30) NULL, ALGORITHM=INSTANT;. The process list showed it as Waiting for table metadata lock.
Session C, two seconds after that: SELECT email FROM users WHERE id = 2;. It waited 17.0 seconds, until A committed and B ran.

Nothing about the change was slow; the queue was. And SELECT @@lock_wait_timeout in the same server returned 31536000, one year, so by default B waits as long as A stays open. The fix is to give the migration session a short timeout and retry:

SET SESSION lock_wait_timeout = 3;
ALTER TABLE users ADD COLUMN nickname VARCHAR(30) NULL, ALGORITHM=INSTANT;
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

With the same open transaction in A, B gave up after 3 seconds, a query that arrived while B was waiting took 1.6 seconds instead of 17, and the next one took 0.1 seconds. The wait is not only at the start: per the ALTER TABLE documentation, an INPLACE change may take the exclusive lock again during execution and a COPY needs it to swap the tables at the end, so a long build can stall the same way at its finish. Before any migration on a busy table, performance_schema.metadata_locks shows which sessions hold a lock on the table, and information_schema.INNODB_TRX how long their transactions have been open.

How Schemity shows which MySQL changes copy the table

Schemity's impact analysis marks the MySQL changes that rewrite the table and hold back writes before the migration runs.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. After you connect to MySQL, a change you draw on the diagram, or one an AI agent proposes, becomes a planned migration, and impact analysis reports what each statement does to the rows before anything runs.

Widen users.email from VARCHAR(50) to VARCHAR(100) and the findings include "Rewrites users to change email: ~995.6K rows, 70 MB on disk" and, under what holds back other statements, "Changing the type of email holds back writes to users while it reads ~995.6K rows, 70 MB on disk".

Widen users.name from VARCHAR(100) to VARCHAR(200) instead and neither finding appears, because MySQL grows that column in place.

Add a CHECK on users and the finding is "Checking the new constraint holds back writes to users while it reads" the same row estimate; add a foreign key on orders and it names orders. That is the point of being able to see the impact of every change: the two VARCHAR widenings read the same in a migration file and cost different things.

The planner also writes the statements MySQL insists on. Changing the type or sign of a column at either end of a foreign key drops the key, changes the columns and adds the key back, because MySQL refuses a type change under a foreign key with error 3780, even with foreign_key_checks off. Foreign key checks are turned back on before the keys return, so the re-added key checks the rows that are already there, which is also why it copies the referencing table. And the migration dialog warns that MySQL commits each DDL statement immediately, so if a later statement fails, the earlier ones stay applied.

What Schemity does not do is take the metadata lock for you. The queue in the previous section depends on what other sessions have open when the migration starts, which no diagram can see, so set lock_wait_timeout in the session that runs it.

MySQL ALTER TABLE locks, in one list

Only a copy holds back writes for the whole run, but every ALTER TABLE can queue reads behind a metadata lock.

Adding, dropping or renaming a column and changing a default are usually INSTANT, until the table reaches 64 row versions on MySQL 8.4, and renaming a column a foreign key uses is INPLACE.
Indexes and NOT NULL run INPLACE; writes continue.
Other data type changes, including a VARCHAR crossing 255 bytes, a CHECK and a foreign key with checks on copy the table and hold back writes until the copy ends.
Add ALGORITHM=INPLACE, LOCK=NONE or ALGORITHM=INSTANT to make MySQL refuse instead of copying.
Set lock_wait_timeout to a few seconds before any ALTER TABLE on a busy table, because even an instant one waits for open transactions and blocks every query behind it.

PostgreSQL draws the same lines in different places: its SET NOT NULL blocks reads and writes while it scans every row, as measured in adding a NOT NULL column to a large table, and it treats VARCHAR growth as free at any length, compared with MySQL in VARCHAR vs TEXT. SQL Server refuses a type change under a key outright, as in SQL Server INT to BIGINT, and what a MySQL key type change costs across a whole foreign key graph is measured in MySQL UNSIGNED primary keys.

Laravel ERD: Draw It From the Migration Files or From the Database?

Son Tran — Mon, 02 Nov 2026 01:09:45 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Draw a Laravel ERD from the database that php artisan migrate built, not from the migration files. In Invoice Ninja v5.13.47 a fresh migrate loads a squashed schema dump and then runs only 61 of the 317 files, and that dump is 10 tables and 43 columns behind the release that ships it. And neither source shows almost half the relations: the database declares 138 foreign keys, while 131 more live only in column names such as activities.client_id.

A Laravel ERD should be drawn from the database that php artisan migrate built, not from the migration files. The files are a history written in PHP, and the database is the result of running it: what a conditional decided, what a raw SQL string created, what a squashed schema dump replaced. We measured the gap on Invoice Ninja v5.13.47, an open-source Laravel 12.69.3 app with 317 migration files: on an empty MySQL 8.4 database, php artisan migrate loaded a stored schema dump and then ran only 61 of them.

The comparison came from readers. Under a video comparing Laravel ERD tools, the most-liked comment asked what happens to the diagram over the lifetime of a project, and the replies split between drawing the diagram from the migration files and reading the database itself. Both camps want a diagram that stays true; they disagree on which source is the truth.

What does a fresh php artisan migrate actually run?

On an empty database Laravel runs the stored schema dump first and then only the migrations the dump does not already record, so most migration files never execute.

Laravel's migration docs describe it plainly: when no migrations have been executed, Laravel "will first execute the SQL statements in the schema file of the database connection you are using", then "any remaining migrations that were not part of the schema dump". Invoice Ninja ships that file as database/schema/mysql-schema.sql, and the migrate output says so:

INFO  Loading stored database schemas.
database/schema/mysql-schema.sql ........................... 1s DONE
INFO  Running migrations.

Afterwards the migrations table holds 319 rows: 258 inserted by the dump, recorded as already run, and 61 that actually ran on top of it. The other 256 files are still in database/migrations and do not run, including the 1,397-line 2014_10_13_000000_create_users_table.php, whose 50 Schema::create calls once built most of the schema. On a fresh install none of them execute.

Why is generating an ERD from Laravel migration files unreliable?

Because the files are PHP that has to run to mean anything, and a reader that only parses them has to reproduce Laravel's schema builder, its conditionals and every raw SQL string.

Invoice Ninja's migrations show each kind of logic a parser has to follow:

What the migration does	Files	Example
Alters an existing table	over 190	`Schema::table('invoices', ...)`
Branches on the current schema	15	`if (!Schema::hasColumn('invoices', 'gateway_fee'))`
Drops a column	18	`$table->dropColumn('e_invoicing_token')`
Renames a column or table	13	`renameColumn`, `Schema::rename`
Runs raw SQL	3	`DB::statement('CREATE INDEX client_hash_idx ON clients (client_hash(20));')`

The raw SQL line is the one that matters most. client_hash(20) is a MySQL prefix index, written as a string inside PHP, so a parser of the schema builder never sees it. It is also the index that made MySQL refuse to rebuild Invoice Ninja when an earlier version of Schemity dropped its prefix length, which is how our MySQL round trip on Invoice Ninja found it.

The conditionals are worse for a diagram than the raw SQL. if (!Schema::hasColumn(...)) means the same file produces different schemas on different databases, so the only way to know which branch ran is to look at the database it ran against.

Is the schema dump a good source for a Laravel ERD?

It is an exact picture of the day it was taken and of nothing after it: Invoice Ninja's dump is 10 tables, 43 columns and 13 foreign keys behind the database the same release builds.

php artisan schema:dump writes the output of the database's own dump client (MariaDB, MySQL, PostgreSQL and SQLite only), so it is real DDL rather than PHP, and it imports cleanly. Measured against the migrated database:

	`mysql-schema.sql`	Database after `migrate`
Last migration recorded	October 3 2024	September 9 2026
Tables	70	80
Columns	1,586	1,727
Foreign keys	125	138

The ten tables missing from the dump are e_invoicing_logs, e_invoicing_tokens, elastic_migrations, job_batches, locations, passkey_credentials, referral_earnings, taggables, tags and verifactu_logs, and 43 columns were added to tables the dump does have. A diagram drawn from the dump is a diagram of Invoice Ninja in October 2024.

Laravel migrations to ERD: which source should the diagram come from?

The migrated database, because it is the only one of the three that is the schema rather than a description of how to build one.

Source	What it is	What it misses	What it needs
Migration files	317 PHP files, over 14,000 lines	Whatever the conditionals and raw SQL decide; the dump that replaces 256 of the files	A parser that behaves like Laravel
`schema:dump` file	One SQL file from the database's dump client	Every migration after the day it was dumped	Re-dumping after each migration
Database after `migrate`	The tables, columns, keys and indexes that exist	Laravel-only meaning: model names, casts, Eloquent relations	A database; a local one is enough

The cost of the database route is that you need one, and a local MySQL or PostgreSQL in Docker plus one php artisan migrate is enough. The cost of the file route is a parser that has to know about the dump, read SQL hidden in PHP strings, and guess which branch of each hasColumn ran on a database whose history differs from a fresh install.

What does the database know that the migrations never said?

That almost half of Invoice Ninja's relations have no foreign key: the database declares 138, and 131 more exist only as column names, so no source draws them unless something reads the names.

Those columns were never given a key. activities is created with $table->unsignedInteger('client_id')->nullable(); and an index, and the line that would have made it a foreign key is commented out in the migration:

//$table->foreign('client_id')->references('id')->on('clients')->onDelete('cascade')->onUpdate('cascade');

The same is true of any Laravel app, whatever helper it uses. Laravel's docs say foreignId "creates an UNSIGNED BIGINT equivalent column"; the constraint comes from ->constrained(), which appears five times in Invoice Ninja's 317 files; a column created without it, or with its foreign() line left out, is just a column. A migration parser and a schema dump both see exactly what the database sees here: a column, an index and no relation.

The relations are still in the names. Invoice Ninja's database has 218 _id columns without a key, and Schemity reads them: it infers 131 relations from columns in 37 tables, such as activities.client_id to clients and credits.design_id to designs, each drawn dashed and tagged as inferred, never written into a migration. The other 87 stay plain columns. Most name no table at all: status_id (10 columns) and frequency_id (5) hold constants from the PHP models, such as Invoice::STATUS_DRAFT = 1, and others hold ids from outside services, such as the mail provider's message_id and the bank feed's nordigen_transaction_id. Some name a table but disagree with its key, and inference skips a pair whose types differ: activities.purchase_order_id is int unsigned against a bigint unsigned key on purchase_orders, and bank_transactions.expense_id is text. And four are the id half of a polymorphic pair, such as paymentable_id beside paymentable_type, which points at a different table on every row. While testing this post Schemity inferred paymentables.paymentable_id as a key to paymentables itself; it now skips the id half of any _type pair, a fix that ships in the next release.

On a smaller view the two kinds are easy to tell apart. In the context view of the Activity & ledger tables, backups.activity_id is a declared foreign key and is drawn solid, while company_ledgers.activity_id was never declared and is drawn dashed, read from its name.

Drawing a Laravel ERD in Schemity

Run the migrations into a local database, connect Schemity to it, and refresh after every php artisan migrate, so the diagram is always read from what ran.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Connect it to the MySQL database your Laravel app migrates, and it draws the tables, the declared foreign keys, the indexes with their prefix lengths, and the inferred relations on top, each one tagged as read from column names. A Virtual relations menu shows or hides them with a count, removes the inferred ones in one go, and a connection can turn inference off entirely. That is how you understand your schema when the code that built it is 14,000 lines of history.

After the next php artisan migrate, Refresh & merge re-reads the database and keeps every position, colour, legend and line route you set, with new tables arriving dashed, as one unsaved change you can inspect before saving. A diagram you designed before the tables existed works the same way: connect it to a database that already has them, and the merged diagram opens as an unsaved change, so closing without saving leaves your file as it was.

When there is no database at hand, Import SQL reads the schema dump instead. On Invoice Ninja's mysql-schema.sql it read all 70 tables with nothing left unread, without counting the dump's DROP TABLE statements or its INSERT INTO migrations rows as skipped, and importing a newer dump onto the same diagram marks each table New, Updated or Unchanged and never removes one. It is still a picture of the day the dump was taken. Imported onto the diagram read from the migrated database, Invoice Ninja's dump finds no new table and marks 29 of its 70 tables Updated: users, for one, has 44 columns in the dump and 46 in the database, which added referral_earnings and settings after October 2024. On a connected diagram, importing it would make those 29 tables pending changes that take the database back to the dump.

The other direction is a trade-off rather than a feature. Schemity writes SQL, a migration diff for a connected database, not a Laravel migration file; DrawSQL exports Laravel migrations, as our DrawSQL comparison says. The route that works today is the one in ERD-first database design: design the change on the diagram, hand the SQL to your coding agent to write the Laravel migration, run it, and refresh, so the dashed draft tables turn solid without moving.

Two details above have their own posts: the prefix index client_hash(20) is what broke our MySQL round trip on Invoice Ninja and Sylius, and polymorphic associations explains why a column like paymentable_id can never carry a foreign key.

pgvector: Embedding Column on the Table or a Separate Embeddings Table?

Son Tran — Fri, 30 Oct 2026 02:11:01 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Put the embedding where the row it describes is written least. A document that is chunked needs a chunks table anyway; an embedding that will be regenerated by a second model belongs in its own table keyed by chunk and model, with an untyped vector column. A vector column on a hot parent row costs a 6 KB HNSW entry on every non-HOT edit of any other column: 5,000 title edits took a 31 MB index to 67 MB.

An embedding belongs where the row it describes is written least, and for most RAG schemas that is not the parent table. A document that gets chunked needs a chunks table whatever you decide, and an embedding you expect to regenerate with a newer model belongs in its own table keyed by chunk and model. The one case for a vector column on the parent row is one vector per entity, on a row that nothing else updates.

The reason is what an HNSW index costs on a row that changes for other reasons. We measured it on PostgreSQL 17.11 with pgvector 0.8.7: a documents table with a vector(1536) column and an HNSW index, 5,000 rows loaded in one INSERT, then one UPDATE that appended a few words to every title and never touched the vector. PostgreSQL made zero HOT updates, the heap went from 2.8 MB to 5.7 MB, and the HNSW index went from 31 MB to 67 MB. VACUUM left it at 67 MB. The control was a chunk_embeddings table holding 20,000 vectors of the same size under an 81 MB HNSW index: the same title edit on its parent rows cannot touch it, and did not.

Why a vector column makes every update of the row expensive

Every non-HOT update of a row with an HNSW index adds a new, full-size entry to the index, and after a bulk load at the default fillfactor nothing is HOT.

The zero is not the vector's doing. A copy of the table with no vector column at all, loaded and updated the same way, also made 0 HOT updates out of 5,000: INSERT ... SELECT packs each page to the default fillfactor of 100, so there is no room on the page for a new row version, and a HOT update needs exactly that. The title column had no index either, so the only indexes in play were the primary key and the HNSW index. What the vector decides is the price of each non-HOT update. A vector(1536) value is 6,148 bytes (pg_column_size on the measured table), pgvector declares the type with external storage, so PostgreSQL keeps it out of line and uncompressed in the TOAST table (2.8 MB of heap against 39 MB of TOAST for the 5,000 rows), and the HNSW entry for the new row version carries the whole vector again. That is why one pass of title edits roughly doubled the index.

Lowering the fillfactor helps but does not fix it. A copy of the table created WITH (fillfactor = 90) made 834 of the 5,000 updates HOT: better, and still 4,166 new index entries for an edit that did not change a single vector.

After VACUUM the index stays at 67 MB: PostgreSQL never shrinks an index file, and pgvector reuses the freed slots for later inserts, so a second pass of title edits after the vacuum left it at 67 MB and a third took it to 72 MB. Until vacuum runs, pgvector's README warns that dead elements can cut the number of rows a search returns, and it says vacuuming an HNSW index "can take a while", recommending REINDEX INDEX CONCURRENTLY first. A parent table that is edited every day carries an index that is always part dead and never smaller than its worst day.

Should pgvector embeddings be in a separate table?

Yes when the text is chunked or when you will ever re-embed; a column on the row only when there is exactly one vector per entity and nothing else on that row is updated.

Three layouts cover the real cases:

	Vector column on the parent row	`chunks` table with a vector column	Separate `chunk_embeddings (chunk_id, model)`
One document, many vectors	Impossible: one row holds one vector	Natural: one row per chunk	Natural
Second embedding model	A second column, backfilled by an `UPDATE` of every row	A second column per chunk, same backfill	New rows under a new `model` value
Edits to the document's other columns	New HNSW entry per non-HOT edit (measured above)	Only when the chunk row itself changes	Never
A model with a different dimension	`ALTER COLUMN TYPE` refused once rows exist	Same	An untyped `vector` column and one cast partial index per model
Filter by tenant or document	On the same table, where pgvector's remedies work	Needs `document_id` on the chunk, or a join	Needs the filter column copied onto this table, or a join
Extra joins on read	None	One, chunk to document	Two, embedding to chunk to document

The middle column is the usual answer. A RAG pipeline splits a document into chunks sized for the model's context, so the chunk row already exists with its position and text, and the vector sits beside the text it encodes. The chunk row changes only when the text changes, which is when the vector must change anyway.

The right column earns its second join when models change. ALTER TABLE documents ALTER COLUMN embedding TYPE vector(768) fails the moment a row holds a 1,536-value vector: PostgreSQL answers ERROR: expected 768 dimensions, not 1536. A new column per model is the same ALTER TABLE on the table that holds the text, then a backfill UPDATE of every row, which by the measurement above adds a new entry to the existing HNSW index for every row it touches, then two indexes on one row and a cleanup migration later. A chunk_embeddings table keyed by (chunk_id, model) takes the new model as new rows and nothing in the tables that hold the text changes. To hold two sizes at once the column is declared as an untyped vector, which pgvector's README recommends for mixed dimensions, and each model gets its own partial index over a cast:

CREATE TABLE chunk_embeddings (
    chunk_id  bigint NOT NULL REFERENCES chunks (id) ON DELETE CASCADE,
    model     text   NOT NULL,
    embedding vector NOT NULL,
    PRIMARY KEY (chunk_id, model)
);
CREATE INDEX chunk_embeddings_small ON chunk_embeddings
    USING hnsw ((embedding::vector(1536)) vector_cosine_ops)
    WHERE (model = 'text-embedding-3-small');
CREATE INDEX chunk_embeddings_nomic ON chunk_embeddings
    USING hnsw ((embedding::vector(768)) vector_cosine_ops)
    WHERE (model = 'nomic-embed-text');

Measured on the same server: 200 chunks stored under both models, 1,536 and 768 values side by side in one column, both indexes built. Queries use the same cast and the same WHERE model = ... so the planner picks the right partial index, and the old model's rows are deleted when the switch is done. A model over 2,000 dimensions needs a halfvec cast instead, which is pgvector's index limit for vector.

The left column is for entity embeddings: one vector per product, per user profile, per support ticket, that changes only when the entity's own text changes, on a row nothing else updates. If the table also carries a view_count or a last_seen_at, it is no longer that case, and the index growth above is what you get.

Where does the tenant filter go?

Keep the column you filter on in the same table as the HNSW index, because pgvector's remedies for a filtered search, a partial index per value or an exact B-tree search, only work when the column is on the indexed table.

pgvector's README is direct about what happens otherwise: with an approximate index, "filtering is applied after the index is scanned", and with the default hnsw.ef_search of 40 a condition that matches 10% of rows leaves about 4 candidates. A tenant filter that lives two joins away on documents is in that position with no index to help it. The README's own remedies are a partial index per filter value when there are few of them, a plain B-tree on the filter column for exact search when the condition is selective, and from pgvector 0.8 iterative index scans (SET hnsw.iterative_scan = strict_order) that keep scanning until enough rows pass, which work with a join too.

For the chunk_embeddings layout that means copying tenant_id onto the embeddings row. A CHECK cannot see another table, so the honest way to keep the copy true is a composite foreign key: UNIQUE (id, tenant_id) on chunks, then FOREIGN KEY (chunk_id, tenant_id) REFERENCES chunks (id, tenant_id) ON UPDATE CASCADE on the embeddings table. For the chunks layout it usually means document_id is already there and tenant_id joins in from documents; put the filter on chunks too, the same way, if the tenant filter is on every query.

Designing the three tables in Schemity

Schemity draws a pgvector column with its dimension and an HNSW index with its operator class and options exactly as PostgreSQL stores them, and flags an index PostgreSQL will refuse before the migration runs.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Connect it to a database with pgvector installed, or import the pg_dump of one, and the layout above is drawn with what pgvector actually stores: embedding VECTOR on chunk_embeddings with no dimension, VECTOR(1536) where one was declared, the composite primary key on (chunk_id, model), and each HNSW index with its cast expression, its WHERE condition, its operator class and its WITH (m=16, ef_construction=64) in the table's Indexes list, read from reloptions rather than typed in.

That matters when you design the next one on a schema you did not write. A vector column with no dimension looks fine on a canvas and builds no plain index: pgvector refuses CREATE INDEX ... USING hnsw on it with column does not have dimensions, and refuses it again above 2,000 dimensions for vector and 4,000 for halfvec. Schema lint reports both as "HNSW index on a vector column PostgreSQL will refuse" before the migration runs.

The change preview is what shows the model-switch mistake: changing vector(1536) to vector(768) on a column that holds rows is drawn as an altered column, with the planned ALTER COLUMN ... TYPE statement for you to read before it runs, so the chunk_embeddings rows arrive as a design decision instead of a failed deploy.

Adding a plain vector index is one dialog: pick HNSW, the operator class pgvector needs (there is no default one, CREATE INDEX ... USING hnsw (embedding) fails with data type vector has no default operator class for access method "hnsw"), and the options, and the written SQL is CREATE INDEX ... USING hnsw (embedding vector_cosine_ops) WITH (m=16, ef_construction=64). An agent working over MCP creates the same index with create_index, operator class included, and is declined if it leaves the class out.

The field editor labels the length of a VECTOR column as its dimensions, which is the number the whole decision above turns on.

The numbers above came from a 5,000-row table on one laptop. Scale them by your row count and your edit rate, and the choice usually makes itself: the same way an integer key that outgrows its type forces a rewrite you would rather have planned, an embedding on the wrong row forces an index rebuild you would rather never need.

MySQL to PostgreSQL Schema Conversion: What Each Column Type Should Become, and What Silently Changes

Son Tran — Thu, 29 Oct 2026 17:42:12 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Most MySQL types have an obvious PostgreSQL twin, but eight need a decision: TINYINT(1), UNSIGNED integers, DATETIME vs TIMESTAMP, ENUM, SET, ON UPDATE CURRENT_TIMESTAMP, AUTO_INCREMENT and the collation behind every unique text column. The last one fails silently: a MySQL unique email refuses Ana@Example.com after ana@example.com, and the same constraint on PostgreSQL accepts both.

Converting a MySQL schema to PostgreSQL is mostly mechanical, and the parts that are not mechanical are the ones that break production. Eight kinds of column need a decision, because the obvious PostgreSQL type either refuses data MySQL accepted, or accepts data MySQL refused. The worst of them changes no type at all: a unique text column that ignored case in MySQL stops ignoring it.

Every behaviour below was run on MySQL 8.4.11 and PostgreSQL 18.3 in throwaway containers, against this small shop schema:

CREATE TABLE customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY customers_email_key (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
  options SET('gift','express','fragile') NOT NULL DEFAULT '',
  total DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The eight MySQL columns that need a decision

Eight kinds of MySQL column change behaviour when translated literally: TINYINT(1), BIGINT UNSIGNED, DATETIME, ENUM, SET, ON UPDATE CURRENT_TIMESTAMP, AUTO_INCREMENT, and unique text columns under a _ci collation.

Most columns translate one to one: DECIMAL(10,2) is NUMERIC(10,2), VARCHAR(255) is VARCHAR(255), INT UNSIGNED fits in bigint, and a MySQL TIMESTAMP is timestamptz. These eight do not:

MySQL	Literal PostgreSQL version	What changes	Decide
`TINYINT(1)`	`boolean`	MySQL stored `2` without complaint; `boolean` refuses any integer	`boolean` if the column only holds 0 and 1, `smallint` if not
`BIGINT UNSIGNED`	`bigint`	Values above 9,223,372,036,854,775,807 fail with `bigint out of range`	`bigint` for keys, `numeric(20,0)` for hashes and external ids that use the full range
`DATETIME`	`timestamp`	Nothing yet, but it keeps no zone, unlike MySQL's `TIMESTAMP`	`timestamptz` if the app writes UTC
`ENUM(...)`	`varchar` plus `CHECK`, or `CREATE TYPE`	The value list moves from the type to a constraint or a named type	One of three designs, chosen on purpose
`SET(...)`	`varchar`	MySQL refused unknown members; plain text accepts anything	`text[]` with a containment `CHECK`
`ON UPDATE CURRENT_TIMESTAMP`	nothing	`updated_at` stops moving	A trigger
`AUTO_INCREMENT`	`GENERATED BY DEFAULT AS IDENTITY`	Loaded ids do not move the counter; the next insert collides	`setval` after the load
A unique text column under a `_ci` collation	`UNIQUE`	Case and accents start to count	A `lower()` index or a nondeterministic collation

The last two get their own sections below.

How do you convert a MySQL schema to PostgreSQL?

Convert the schema first, deciding each of those eight columns yourself, then move the rows with pgloader or COPY.

pgloader can do both in one pass with its own default casts, and they differ from a literal mapping in places that matter: it casts DATETIME to timestamptz, turns each ENUM into a PostgreSQL enum type named after the table and column, casts TINYINT(1) to boolean through its own tinyint-to-boolean function rather than refusing odd values, gives AUTO_INCREMENT keys serial or bigserial, and resets sequences after the load. Its create no tables option instead loads into tables that already exist, which is the route when you want to decide each column yourself and write the CREATE TABLE script first.

Whichever tool writes the script, read it against the list above before running it. These are the cases from the test run where a literal translation accepted the script and then behaved differently.

TINYINT(1): boolean only if the data agrees

MySQL has no real boolean, so BOOLEAN is TINYINT(1) and the (1) is a display width, not a limit. On 8.4, INSERT INTO customers (email, is_active) VALUES ('flag@example.com', 2) succeeded and stored 2. PostgreSQL's boolean refuses the same insert with column "is_active" is of type boolean but expression is of type integer. Before choosing boolean, count the rows that would not fit:

SELECT count(*) FROM customers WHERE is_active NOT IN (0, 1);

Run it before the load whichever tool moves the rows. Zero means boolean. Anything else means some code uses the flag as a small number, and smallint keeps it until that code is found. Changing the column type later is its own migration, covered in changing an integer column to boolean in Postgres.

UNSIGNED integers: one size up, except BIGINT

PostgreSQL has no unsigned integers. An INT UNSIGNED holds up to 4,294,967,295, which fits in bigint, so the safe mapping is one size up: TINYINT UNSIGNED to smallint, SMALLINT UNSIGNED and MEDIUMINT UNSIGNED to integer, INT UNSIGNED to bigint. BIGINT UNSIGNED has nowhere to go. MySQL accepted id = 18446744073709551615; PostgreSQL's bigint stops at 9,223,372,036,854,775,807 and refused it with bigint out of range. For an AUTO_INCREMENT key that will never reach 2^63, bigint is right. For a column holding 64-bit hashes or another system's ids, check SELECT max(col) first and use numeric(20,0) if the top half is in use. Why MySQL schemas are full of UNSIGNED in the first place is in signed vs unsigned primary keys in MySQL.

DATETIME vs TIMESTAMP: only one of them is an instant

The MySQL manual puts the difference in one sentence: "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)" In the test, both columns were set to 2026-10-29 09:00:00 in a session at +07:00 and read back at +00:00: the TIMESTAMP returned 02:00:00, the DATETIME still 09:00:00.

So a MySQL TIMESTAMP is an instant and becomes timestamptz without debate. A DATETIME is whatever the application wrote. A literal conversion makes it timestamp, which is correct only for wall-clock values. If the application wrote UTC, which is the usual case, convert it on the PostgreSQL side once the rows are in:

ALTER TABLE customers ALTER COLUMN updated_at TYPE TIMESTAMPTZ(0) USING updated_at AT TIME ZONE 'UTC';

Why the naive type costs you later is in timestamp vs timestamptz.

ENUM and SET: the value list has to live somewhere

A MySQL ENUM becomes either a CHECK constraint on a varchar, a PostgreSQL enum type, or a lookup table. That is a design choice with real trade-offs, and it is the subject of enum vs check constraint vs lookup table. A CHECK is the easiest to change later.

SET is the one that loses a rule quietly. Under MySQL 8.4's default strict sql_mode, INSERT INTO orders (..., options) VALUES (..., 'gift,wrap') failed with Data truncated for column 'options', because wrap is not a member. Converted to a varchar, PostgreSQL stored gift,wrap without a word. Converting the column to an array with a containment check, in place and with its rows, puts the membership rule back:

ALTER TABLE orders ALTER COLUMN options DROP DEFAULT;
ALTER TABLE orders ALTER COLUMN options TYPE TEXT[] USING string_to_array(options, ',');
ALTER TABLE orders ALTER COLUMN options SET DEFAULT '{}';
ALTER TABLE orders ADD CONSTRAINT orders_options_check CHECK (options <@ ARRAY['gift', 'express', 'fragile']);

gift,express became {gift,express}, an empty set became {}, and ARRAY['gift', 'wrap'] then failed on orders_options_check. One MySQL rule does not come back: MySQL stored gift,gift as gift, while the array keeps the duplicate.

ON UPDATE CURRENT_TIMESTAMP: there is no column option to translate

MySQL's updated_at moved on every UPDATE in the test. PostgreSQL has no column clause that does this, so the converted column kept its first value forever, and the UPDATE reported success. A BEFORE UPDATE trigger brings the behaviour back:

CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER customers_set_updated_at BEFORE UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

One function serves every table; each table needs its own trigger. The trigger against the ORM approach, per framework, is in how to auto-update an updated_at column.

AUTO_INCREMENT: identity, and the sequence after the load

Map AUTO_INCREMENT to GENERATED BY DEFAULT AS IDENTITY, then reset each sequence with setval after the rows are loaded, or the next insert collides with a loaded id.

AUTO_INCREMENT keeps a value the row supplies, which is PostgreSQL's GENERATED BY DEFAULT AS IDENTITY, and that is what a load needs, since the rows arrive with their ids. The catch is the counter. After loading ids 1 and 2 into a fresh table, the next insert without an id failed with duplicate key value violates unique constraint "customers_pkey" and Key (id)=(1) already exists, because the identity sequence never saw the loaded rows. One statement per table fixes it:

SELECT setval(pg_get_serial_sequence('customers', 'id'), (SELECT max(id) FROM customers));

pgloader resets the sequences it created unless told reset no sequences; on tables you created yourself, check with SELECT last_value from the sequence, or run the setval anyway. A hand-rolled COPY never does it. Whether to switch to GENERATED ALWAYS once the load is done is covered in serial vs identity in Postgres.

Is a unique constraint case-sensitive in PostgreSQL?

Yes: PostgreSQL compares text exactly, so a unique email that refused Ana@Example.com after ana@example.com in MySQL accepts both, with no error.

This is the change no type mapping shows. MySQL 8's default collation for utf8mb4 is utf8mb4_0900_ai_ci: accent-insensitive and case-insensitive. Every comparison goes through it, unique keys included. In the test:

On the same schema	MySQL 8.4, `utf8mb4_0900_ai_ci`	PostgreSQL 18.3
Insert `ana@example.com`, then `Ana@Example.com`	Second insert fails: `Duplicate entry 'Ana@Example.com' for key 'customers.customers_email_key'`	Both rows stored
Insert `josé@example.com`, then `jose@example.com`	Second insert fails: duplicate entry	Both rows stored
`WHERE email = 'ANA@EXAMPLE.COM'`	Finds the row	Finds nothing

The PostgreSQL manual states it plainly: "All standard and predefined collations are deterministic", meaning two strings are equal only if they are the same bytes. So after the conversion, a sign-up form that MySQL protected now creates a second account for the same person, and a login that typed the address in capitals stops finding it. Nothing errors.

There are two ways to keep the old rule:

A unique index on lower(email), after dropping the plain constraint: CREATE UNIQUE INDEX customers_email_lower_key ON customers (lower(email));. It refused Ana@Example.com after ana@example.com. It does not make WHERE email = ... case-insensitive, so queries have to say lower(email) too, and it does nothing for accents.
A nondeterministic ICU collation on the column. CREATE COLLATION ci_ai (provider = icu, locale = 'und-u-ks-level1', deterministic = false) ignores case and accents, as _ai_ci did. With the column set COLLATE ci_ai and the UNIQUE added back, JOSE@example.com was refused after josé@example.com, and a plain WHERE email = 'Jose@Example.com' found the row. The manual's und-u-ks-level2 variant ignores case but not accents. The cost is that B-tree deduplication cannot be used on such an index.

Not every MySQL column is affected. A column declared COLLATE utf8mb4_bin was already case-sensitive, and only text columns that sit in a primary key, unique constraint or unique index change who gets in. A non-unique column still changes how WHERE matches, which is worth a search through the application's queries.

Converting the schema in Schemity

Schemity writes the PostgreSQL script without changing the diagram, with a report above it that lists every type it changed and the behaviour that does not carry over.

From there, the SQL dialog behind Export > SQL file has a Database dropdown: pick PostgreSQL and the script is rewritten for it, with a conversion report above it. The diagram stays a MySQL diagram, and nothing leaves your machine. For the shop schema the report reads:

-- Converted by Schemity from MySQL to PostgreSQL. The diagram itself is unchanged.
-- Review the notes below before running this script.
--
-- Type mappings:
--   BIGINT UNSIGNED -> BIGINT (2 columns)
--   DATETIME -> TIMESTAMP(0) (1 column)
--   DECIMAL(10,2) -> NUMERIC(10,2) (1 column)
--   ENUM('pending','paid','shipped','cancelled') -> VARCHAR(9) (1 column): Allowed values become a CHECK constraint.
--   INTEGER UNSIGNED -> BIGINT (1 column)
--   SET('gift','express','fragile') -> VARCHAR(21) (1 column): A set of values is stored as comma-separated text, unchecked.
--   TIMESTAMP -> TIMESTAMPTZ(0) (1 column)
--
-- Notes:
--   customers.updated_at: ON UPDATE CURRENT_TIMESTAMP is left out: PostgreSQL has no column that sets itself on update.
--   customers.email: Unique in MySQL under utf8mb4_0900_ai_ci, which ignores case and accents; PostgreSQL compares text exactly, so values that differ only in case and accents are both accepted.
--   2 BIGINT UNSIGNED column(s) hold values up to 2^64-1; PostgreSQL's BIGINT stops at 2^63-1.
--   2 character set or collation setting(s) left out: PostgreSQL picks its own.

The script under it creates is_active as BOOLEAN NOT NULL DEFAULT TRUE, both keys as BIGINT GENERATED BY DEFAULT AS IDENTITY, and status with the CHECK ("status" IN ('pending', 'paid', 'shipped', 'cancelled')). It ran on PostgreSQL 18.3 as written. The report is also saved as a comment block at the top of the file, so whoever runs the script reads the same notes.

The conversion makes the literal choice in every row of the table at the top rather than guessing at your data: TINYINT(1) becomes BOOLEAN, DATETIME stays timestamp, SET stays text, and the email keeps a plain UNIQUE. The report covers most of those choices, each tied to the column it is about. Three are yours to remember: whether a TINYINT(1) really holds only 0 and 1, whether a DATETIME holds UTC, and the setval after the load. Make them in the diagram you are converting into: Import SQL on a PostgreSQL diagram reads the converted script.

From there you design the next one in PostgreSQL's types, change updated_at to TIMESTAMPTZ, add the lower(email) index, and export the final script. The rows still move with pgloader's create no tables or your own COPY, followed by the setval.

MySQL to PostgreSQL, in one list

Most of the conversion is mechanical; these eight columns are not, and each has a short answer.

TINYINT(1): boolean only after SELECT count(*) ... NOT IN (0, 1) returns zero.
UNSIGNED: one size up; BIGINT UNSIGNED to bigint for keys, numeric(20,0) if values pass 2^63.
TIMESTAMP to timestamptz; DATETIME to timestamptz too if it holds UTC, converted with AT TIME ZONE 'UTC'.
ENUM: a CHECK, an enum type or a lookup table, chosen on purpose. SET: text[] with a <@ check.
ON UPDATE CURRENT_TIMESTAMP: a BEFORE UPDATE trigger on each table.
AUTO_INCREMENT: GENERATED BY DEFAULT AS IDENTITY, then setval after the load.
Every unique text column under a _ci collation: a lower() unique index or a nondeterministic collation, or accept that case now matters.

Choosing a key type while you are redesigning anyway is in UUID vs bigint primary keys, and what changes about indexes when you move a unique rule around is in unique constraint vs unique index.

MySQL Workbench Alternative: ER Diagrams Past 250 Tables

Son Tran — Wed, 28 Oct 2026 16:34:03 +0000

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: MySQL Workbench 26, Oracle's rebuild, has no ER diagram: modeling lives on only in Workbench 8.0, which is no longer maintained. And that diagram struggles in two places: at 250 or more objects it may refuse to place them on a diagram, and it draws a relationship only where MySQL stored a foreign key, so schemas from apps that never declared their keys open with no lines. Schemity places every table it reads, infers the missing relations from column names as dashed virtual relations, and re-reads the database each time you open the diagram.

A good MySQL Workbench alternative is one that keeps the diagram correct on the schemas where Workbench's own diagram breaks down: the ones with hundreds of tables, and the ones whose foreign keys MySQL never stored. Those are the two problems people search for after a reverse engineer, and both follow from how Workbench builds its diagram.

This post covers both, what Schemity does differently, and the long list of things MySQL Workbench still does that Schemity does not try to do.

The Honest Summary

MySQL Workbench is Oracle's own tool for MySQL, free under the GPL as the Community edition, on Windows, macOS and Linux. Its manual covers administrative tasks, performance tools, database development, database design and modeling, and a database migration wizard. The modeling part reverse engineers a live database into an EER model saved as an .mwb file, forward engineers a model into a CREATE script, and synchronizes a model with a database in either direction. One caveat from the same manual: Workbench "is developed and tested with MySQL Server 8.0", and on 8.4 and later "some MySQL Workbench features may not function".

Schemity does one of those jobs: the diagram. Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. It connects to MySQL and MariaDB, re-reads the schema each time you open the diagram with its connection available, and turns your edits into a reviewed migration. It has no SQL editor, no server administration and no performance dashboards.

So the comparison is not one tool against another. It is Workbench's modeling chapter against software that does nothing but modeling.

Does MySQL Workbench 26 Have ER Diagrams?

Not as of October 2026. Oracle rebuilt Workbench as version 26, and its product page lists four areas: develop (a script editor and SQL notebooks), administer, analyze performance (performance reports and Visual Explain), and backup and migrate. Data modeling is not one of them, and the same page says earlier Workbench releases have reached end of life and are no longer maintained.

So everything this post says about Workbench's diagram, from the EER model and Reverse Engineer to Synchronize Model, describes Workbench 8.0. Its release notes end at 8.0.47, released in April 2026, and the screenshot below comes from that line. If your ER diagrams live in Workbench, they live in a version that gets no more fixes.

Why Does MySQL Workbench Reverse Engineer Show No Relationships?

Workbench draws a relationship for each foreign key. Its manual files relationships under "Adding Foreign Key Relationships Using an EER Diagram", with six foreign key tools, and double-clicking a line opens the foreign key's properties. A relationship in a Workbench model is a foreign key.

That is a sound design until the database has no foreign keys to read, and plenty of InnoDB databases have none. Older Rails apps, Laravel migrations that add an unsignedBigInteger column without constrained(), and Django models with db_constraint=False all leave the key undeclared; some teams drop foreign keys to speed up bulk loads and online schema changes; and a schema that grew one hand-written migration at a time often declared keys on some tables and not others. Tables like these are ordinary:

CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  KEY idx_orders_customer_id (customer_id)
) ENGINE=InnoDB;

The column is named for customers and indexed for joins, but MySQL has no foreign key on record, so information_schema.REFERENTIAL_CONSTRAINTS has nothing for orders. The dependency is real in the data; the database just never heard about it.

Workbench opens such a model with no lines between the tables, which is exactly what the database declares. You can draw each one with its Place a Relationship Using Existing Columns tool, but in a Workbench model every relationship is a foreign key, so Synchronize Model would then offer to create it in the database as a real constraint.

Here is a 26-table MySQL 8.4 shop reverse engineered into Workbench. Its migrations declared 14 foreign keys and left 16 *_id columns undeclared, the way a schema grows when some migrations used constrained() and later ones did not:

Workbench draws the 14 declared keys, dashed because it draws every non-identifying relationship that way, and nothing for the other 16: wishlist_items.product_variant_id, refunds.payment_id and audit_logs.user_id have no line of their own. The connection tab also shows the 8.4 caveat from the manual in practice: it reads "Warning - not supported".

How Schemity Draws the Relationships MySQL Never Stored

Schemity reads the declared foreign keys first, the same as Workbench. Then it looks at what is left. A column like customer_id is split into tokens, the trailing id is dropped, and the rest is matched against table names and their singular forms, so customer_id finds customers. The referenced column is the table's primary key, or failing that its only single-column unique key, or a column named id. Candidates are skipped when the column types disagree, when two tables could answer to the same name, when either side is a view, or when a real foreign key already covers the column.

Each match becomes a virtual relation: a relation Schemity documents but the database does not declare. A virtual relation is drawn dashed, saved in the diagram file, and never written to the database by a migration, a SQL export or a DBML export. One notification says how many were inferred, with a Remove all action, and a relation you delete is remembered so the next refresh does not bring it back. If you would rather see only what MySQL declares, inference can be turned off per connection.

On the schema above, orders.customer_id opens with a dashed line to customers, because both columns are BIGINT UNSIGNED; a signed customers.id would get no line.

The same 26-table shop in Schemity, connected to the same database:

The status bar counts 30 relations: the 14 the database declares, solid, and the 16 inferred from column names, dashed. In Schemity a dashed line means the database does not enforce the link, so order_coupons.coupon_id and carts.customer_id read at a glance as references nothing protects. The line tells you the dependency exists. The dash tells you MySQL will not enforce it, so an order can still point at a customer that was deleted.

What Happens When You Reverse Engineer More Than 250 Tables?

The reverse engineering page of the Workbench manual states the limit itself: "Importing 250 or more objects could fail to create an EER diagram and instead emit a resource warning with the text 'Too many objects are selected for auto placement. Select fewer elements to create the EER diagram.'"

The documented workaround is to run the wizard again with Place imported objects on a diagram unticked, create the EER diagram by hand, import the objects from the catalog, and then lay them out with Arrange, Autolayout or by hand. The wizard's object list also includes views and routines, not only tables.

Schemity places every table it reads. It was tested on a generated PostgreSQL schema of 1,035 tables and 1,822 relations, which opens in about 10 seconds on a 16 GB M1 Pro because only the tables on screen are drawn up front. A minimap and a fuzzy search over table, column and legend names do the navigating, and context views split a large schema into focused diagrams by domain while the main view keeps every table. A context view is a sub-diagram of the same schema: the tables are shared, only the selection and layout differ.

Does the Diagram Stay in Sync With the Database?

Credit to Workbench here, because it has a real answer. Synchronize Model compares an .mwb model with a live database and lets you set each difference to Update Model, Update Source or Ignore, then save or run the SQL.

The difference is when it happens. A Workbench model is a file that changes when you run the wizard. Schemity re-reads the database each time you open the diagram with its connection available, and folds the changes into your layout: positions, colours, legends and relation routes stay where you put them, and tables the database gained since your last save arrive with a dashed border so you can find them. After an ORM migration runs outside Schemity, Refresh & merge does the same on demand, as one unsaved change you can review before saving.

Going the other way, an edit on the diagram becomes a migration SQL diff against the live database, and impact analysis reads it before anything runs: which statements fail on existing rows, which drop data, and which may rewrite the table, with the row count taken from table_rows so nothing is scanned. On MySQL it treats every column type change as a rewrite, the cautious reading, since MySQL copies the table for most of them. Nothing touches the database until you apply it.

Does It Keep MySQL's Own Column Details?

A diagram that rounds MySQL off to generic SQL is a drawing, not a model. Schemity reads and writes the details MySQL schemas actually carry:

UNSIGNED integers, shown as uBIGINT on the canvas, with foreign key columns following their key's sign, so a relation to a Laravel or WordPress BIGINT UNSIGNED key migrates instead of being refused
ON UPDATE CURRENT_TIMESTAMP, AUTO_INCREMENT and column COMMENT
table and column character sets and collations
prefix indexes such as client_hash(20) and functional indexes
ENUM and SET defaults, and (CURDATE()) and (CURTIME()) defaults

These were tested by rebuilding two real MySQL schemas, Invoice Ninja and Sylius, from Schemity's own SQL, which is where several of them were found missing and fixed. Connections take a pasted MySQL URI or a JDBC string, go direct or over SSH, and log in with IAM tokens on AWS RDS, Azure and Cloud SQL.

MySQL Workbench vs Schemity

	MySQL Workbench	Schemity
What it is	SQL editor, administration, performance tools, modeling and migration in one app	Database design software: the diagram, migrations and their impact
ER diagrams today	Only in Workbench 8.0, end of life; none in Workbench 26	The core of the product, in every release
Price	Free Community edition under the GPL	$129 one-time, 1 year of updates, 2-week free trial
Databases	MySQL, plus a migration wizard from other databases	MySQL, MariaDB, PostgreSQL, Supabase, SQL Server, SQLite
Server versions	Tested with MySQL 8.0; some features may not work on 8.4 and later	Tested against MySQL 8.4
Diagram size	250 or more objects may fail auto placement	Every table placed; tested at 1,035 tables (PostgreSQL)
Relationships	Drawn from stored foreign keys	Stored foreign keys, plus dashed relations inferred from column names
Staying current	Synchronize Model, run by hand	Re-read on open when connected, layout kept
Schema changes	Synchronize or forward engineer, then run the SQL	Migration SQL diff with impact analysis, applied only when you approve
Storage	`.mwb` model file	One JSON file per diagram, in a folder you choose
AI agents	None	Local database MCP server for Claude Code, Cursor and other MCP clients

Where MySQL Workbench Wins

It is free, and it is Oracle's. For a MySQL shop that wants its tools from the database vendor, that is a real reason.

It is more than a diagram. Running queries, managing users, reading performance reports and migrating from another database all live in Workbench. Schemity does none of them, so you will still want Workbench, or another SQL client, for the rest.

Model-first work is built into 8.0. Workbench 8.0 generates CREATE scripts and synchronization SQL from an EER model, and draws identifying and non-identifying relationships as solid and dashed lines.

It reads .mwb files. Schemity does not. Moving an existing model means pointing Schemity at the same database, or exporting the model through File, Export, Forward Engineer SQL CREATE Script and importing that SQL.

What Is a Good MySQL Workbench Alternative for ER Diagrams?

For the diagram specifically, the questions that decide it are the two above. Does the tool still place every table when the schema passes a few hundred? And does it show the dependencies MySQL never stored, without pretending the database enforces them?

For a large or undocumented MySQL schema, Schemity is built to answer both, which is what lets you understand your schema before you change it: every table on one canvas, the declared keys solid, the inferred ones dashed, and a diagram that is current each time you open it.

Who Should Use MySQL Workbench

You want one free tool for queries and administration, and can keep an unmaintained Workbench 8.0 for modeling.
Your schemas are under a couple of hundred objects and declare their foreign keys.
Your team already keeps .mwb models and forward engineers from them.

Who Should Use Schemity

Your MySQL or MariaDB schema has hundreds of tables, or arrived without foreign keys.
You want the diagram to match the database every time you open it, without running a wizard.
You want each schema change reviewed with its cost before it runs, and the diagram kept in Git.

The Bottom Line

MySQL Workbench is a capable free toolbox, but its modeling now lives only in the unmaintained 8.0 line, and works best on a schema that is small and declares its keys. When the reverse engineer shows no relationships or stops at the placement warning, keep Workbench for queries and administration, and connect Schemity to the same database for the diagram.

For how the scale test behaved, read what breaks when a database diagram reaches 1,000 tables; for the MySQL details, round-tripping Invoice Ninja and Sylius through a database diagram; and if your diagram ever had to draw a 1:1 relationship, why ERD tools draw 1:1 relationships as 1:N.