Table Structure

This is a DDL editor with a grid in front of it. Rename a column, add an index, change a key, and what you get is a pending ALTER TABLE you can read before it runs; the table on the server is untouched until you apply it. Open a table and switch the result view to Structure, or right-click it in the sidebar and choose Show Structure. The tabs are Definition, Triggers, DDL, and Parts (ClickHouse only). Triggers is hidden on an engine without triggers, such as Redshift.

​

Definition tab

Definition is one scrolling page of four sections: Columns, Indexes, Foreign Keys and Check Constraints. A section the engine has no concept of is hidden: ClickHouse has no Foreign Keys, Redis no Check Constraints. Each section has a disclosure triangle, a count, its own table, and its own + and − under the table.

• Click a section’s header to collapse it. It stays collapsed on every table until you open it again.
• The Filter field at the top filters every section at once, and opens collapsed ones while it holds text.
• Click a column header to sort that section by the one column.
• A section the server fails to read shows the error and Try Again, and the other sections load as usual. The Triggers and DDL tabs do the same.

Each table shows the fields read at a glance. Columns hides On Update, Auto Inc, Charset, Collation, Generated and Expression until you show them, and Foreign Keys hides Ref Schema. Right-click a column header and choose Show All Columns to bring them in, or Hide Column to take one out. The choice holds for every table, and the inspector always shows every field.

​

The inspector

Select a row in any section and the inspector (View > Show Inspector, Cmd+Option+I) edits it as a form, with the same menus and pickers its cells use. Each field you change is one undo step. Selecting a row in one section clears the selection in the others. With several rows selected the inspector counts them instead, and a row marked for deletion stays read-only there until you undo the delete. With nothing selected, the inspector shows the table itself: Comment where the engine stores table comments, the table options on MySQL and MariaDB, then its size, row count and metadata.

​

Table comment

An edit to Comment is a structure change like any other: Preview SQL shows the statement, Save Changes (Cmd+S) runs it with the rest, and Cmd+Z takes it back. Clearing the field removes the comment. The field is dimmed while safe mode blocks writes. Table Operations lists which engines comment on what.

​

What each object accepts

A view is not a table, and the grid offers only the edits the server will take on the object open in front of it. A refused + or − stays on screen, dimmed, with the reason in its tooltip; a cell the object will not let you change beeps instead of opening an editor. PostgreSQL and PGlite answer this per operation, so the page does too: A view and a materialized view differ in both directions: ALTER TABLE … SET DEFAULT runs on a view and is refused on a materialized view, and CREATE INDEX is the other way round. Every other engine edits a table and a partitioned table only. A view, a materialized view, a foreign table and a system table are read-only there. A MongoDB collection takes two edits, a field rename and a field removal; see MongoDB collections.

​

Columns

Edit a column in its row or in the inspector. Nullable, Primary Key, Auto Inc and On Update are checkboxes; selecting Primary Key deselects Nullable and holds it there until the key comes back off. Type opens a picker of the engine’s types by category: search to filter, or type a parametric value such as VARCHAR(255) and press Return to use it as written. The cell reads the declared type and writes back what was typed. On PostgreSQL and PGlite the picker opens with a User-Defined group of enums, composites, domains and ranges from the schemas the sidebar’s schema filter keeps and the table’s own schema, each listed schema-qualified as sales.status. See User-Defined Types. Which fields a column has is the driver’s choice. Comment is there on most engines and writes the column comment. MySQL and MariaDB add Charset, Collation, and On Update; On Update selected on a TIMESTAMP or DATETIME column adds ON UPDATE CURRENT_TIMESTAMP at that column’s own precision, so TIMESTAMP(6) gets ON UPDATE CURRENT_TIMESTAMP(6).

​

Charset and collation

Both fields are menus of what the server lists. Charset starts with Table Default, which drops the column’s own character set so it takes the table’s, followed by the server’s character sets. Picking one sets Collation to that set’s default. Collation lists the collations of the row’s character set, or of the table’s when the row has none, with the default marked (default). A name typed into either cell has to be one the server lists, and a collation that belongs to another character set moves the column to that set. Both fields are read-only on a type that takes no character set, which is anything but CHAR, VARCHAR, the TEXT types, ENUM and SET. Changing Type to such a type empties both. If the server’s list cannot be read, the menu says Couldn’t read the character sets and the fields stay read-only; open the menu again to retry.

​

Default

The cell holds the SQL that follows DEFAULT, so a string literal carries its own quotes and an expression is written the way the engine spells it. Click the chevron for the engine’s own list. Typing straight into the cell is the same as SQL expression, so pending is a column reference and 'pending' is the string. Text does the quoting and escaping for you. On MySQL and MariaDB a nullable column declared without a default has DEFAULT NULL, so its cell reads NULL, and No default on a nullable column saves as NULL. Deselecting Nullable, or selecting Primary Key, clears a NULL default. A column you change so that it refuses NULL and defaults to NULL is reported before anything is saved. Auto-increment has its own Auto Inc field. Changing the default of a column that already exists takes a different route per engine. Most run ALTER TABLE … SET DEFAULT. SQLite, libSQL and Cloudflare D1 have no such statement, so the change is made by recreating the table, reviewed first like any other rebuild. Snowflake can only remove a default, and saving one there is refused with Unsupported schema operation.

​

Generated columns

PostgreSQL, CockroachDB, PGlite, MySQL, MariaDB and SQLite add two more fields, both hidden in the table until you show them. Generated is a menu of Not generated, STORED and VIRTUAL; Expression holds the SQL the value is computed from. Set both on a new row and the column is created as GENERATED ALWAYS AS (expression). PostgreSQL 11 and earlier have no generated columns, so on those servers neither field appears. The keyword is always written out. PostgreSQL 17 and earlier accept STORED only, PostgreSQL 18 made VIRTUAL the default, and MySQL and MariaDB default to VIRTUAL, so leaving it implicit would mean different columns on different servers. Two limits apply to a column that already exists. Switching Generated on it is refused by every engine: drop the column and add it back to make it generated, which discards its data. On SQLite, a column added to a populated table is VIRTUAL; ALTER TABLE refuses cannot add a STORED column there, so STORED is reachable only when the table is created. Add a column with + under the Columns table. Cmd+Shift+N adds a row to the section holding the selection, or to Columns when nothing is selected, and Cmd+Shift+D duplicates the selected rows. Select rows and click − or press Delete to mark them for removal. Select Primary Key on one column, or on several for a composite key in one PRIMARY KEY (col1, col2) clause. On an existing table, each column you add to or take out of the key changes the table’s key, saved as a drop of the old constraint followed by an add. Drag a column row by its row number to reorder it, or right-click one and choose Move Column Up or Move Column Down. Both are dimmed with the reason spelled out under them while unsaved changes exist, while the list is filtered or sorted, on anything but a table, and on an engine that cannot change column order. What the drag does depends on the engine: Everything that runs goes to query history rather than the change queue.

​

Indexes

To replace an index, delete it and add the new one under the same name in one save, or choose Duplicate on its row, edit the copy and delete the original. The old index is dropped before the new one is created. Renames in one save may pass names along, such as b to a and c to b: each runs once the name it takes is free, and two indexes that swap names are dropped and created again. On MySQL and MariaDB, an index you change or replace in a save that changes no column runs as one ALTER TABLE, so a replacement the server rejects leaves the original index in place. Neither takes an index named PRIMARY, in any letter case, because that name belongs to the primary key: change the key with Primary Key in the Columns section. DynamoDB takes one index change per save, see DynamoDB.

​

Expression keys

Type an expression into Columns as CREATE INDEX writes it, such as lower(email) or coalesce(first_name, last_name). A comma inside parentheses or a string stays part of its key. Text that names one of the table’s columns is that column, in any case and with or without parentheses around it. A sort order is not typed here: lower(email) DESC is flagged as a column that does not exist. A descending key, a collation or an operator class the index already has stays through a rename or a new condition, and goes when you edit Columns or Type.

​

Invalid indexes

A PostgreSQL CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY that fails or is cancelled leaves an invalid index behind. It keeps its row in the list, and a line under the list names it. Queries skip it, but every write still updates it, and an invalid unique index still refuses duplicates. Delete the row and save to drop it, or run REINDEX INDEX on it in the SQL editor to build it again. The DDL tab, SQL exports and Copy To leave an invalid index out, the same as pg_dump, and Compare & Sync never creates one on the target. An index on a partitioned table that is still waiting for a partition’s index to be attached is not flagged, and exports keep it.

​

Materialized views

A materialized view’s indexes list, add and drop the same way a table’s do. The line under the list says whether Refresh Materialized View can refresh it concurrently, which keeps other sessions reading the view while it runs: The server answers the question, so an index left invalid by a failed CREATE UNIQUE INDEX CONCURRENTLY does not count, even though it lists with Unique selected. The line updates after every save and every refresh.

​

Foreign keys

Right-click a foreign key and choose Open [table] to jump to the referenced table. Right-click a row in any section for Copy (the cell under the pointer), Copy Name, Copy Definition, Copy As (CSV, JSON, SQL), Duplicate, and Delete (Delete). A row already marked for deletion offers Undo Delete. The same menu appears whether or not the row was already selected, and its row commands act on the whole selection. The section’s + and − are dimmed on an object that cannot carry a key and on an engine that cannot edit foreign keys, with the reason in the tooltip. What the save does depends on the engine: A rebuild carries everything else staged in the same save, and ends in PRAGMA foreign_key_check scoped to the table. Rows that do not match the new key roll the whole rebuild back, and the count comes back in the error. A column renamed or dropped in the same save runs as its own ALTER TABLE after the rebuild, so the new name reaches every index, trigger and view. A drop that would leave one of those naming a column that is gone fails the whole save rather than committing it broken.

​

Check constraints

Check constraints are table-level, so a rule spanning two columns is one row here rather than something hidden on a column. Columns is filled from the catalog on PostgreSQL and SQL Server. MySQL, MariaDB and SQLite publish no such catalog, so the cell stays empty there. Renaming a constraint runs a single RENAME CONSTRAINT where the engine has one. Changing the expression drops and re-adds it, because no engine can alter a check in place. Both the re-add and a brand-new constraint scan every existing row and fail if any row violates the rule, so a failed save means the data disagrees with the constraint. Every drop runs first, and a rename waits for the rename that frees its name, so a constraint added or renamed in the same save can take the name of one you deleted or renamed. Two constraints that swap names are dropped and added back, which scans the table. A constraint named C beside one named c is refused before anything runs, on every engine, because SQLite and MariaDB treat the two as one name. Give the second constraint another name. The section is hidden on engines with no check constraints. SQL Server lists and edits them, but has computed columns rather than generated ones, so it gets this section and not the two column fields. It is hidden on MySQL before 8.0.16, MariaDB before 10.2.1, TiDB before 7.2 and OceanBase before 4.0 as well. Those servers accept a CHECK clause, answer Query OK, and throw the rule away, so there is nothing to list and nothing a save could keep. Upgrade, or enforce the rule with a trigger. SQLite is the one engine where listing and editing part company. The section appears on every version, but + and − need SQLite 3.53.0 or later, the release that added ADD CONSTRAINT and DROP CONSTRAINT to ALTER TABLE. The driver links the system SQLite, so the version is the one macOS ships. On an older one the constraints still list, read-only. SQLite also accepts two constraints under one name, treats c and C as the same name, and DROP CONSTRAINT removes the first one it finds. When two listed constraints share a name, a save that changes or deletes one of them and leaves the other is refused before anything runs: change or delete all of them in the same save. A save that leaves them both alone goes through.

​

Table options

A MySQL or MariaDB table has a default character set and collation, the pair a new column takes when it names none, and an AUTO_INCREMENT counter. Change any of the three under Options in the inspector, with nothing selected on the Definition tab. Views, temporary tables, and tables on TiDB or OceanBase have no Options. Edits queue with the rest of the structure: Preview SQL shows the ALTER TABLE, Save Changes runs it, and Cmd+Z takes it back. At the Read-Only safe mode level the fields are dimmed, with the reason under them.

​

Converting existing columns

With Convert existing columns off, the save runs ALTER TABLE … DEFAULT CHARACTER SET = … COLLATE = … and no stored text changes. Turn it on and the save runs ALTER TABLE … CONVERT TO CHARACTER SET … COLLATE … instead. Every text column is rewritten to the new pair, and a character the new set cannot hold is lost. A TEXT column can come back as MEDIUMTEXT, and the server copies the whole table while the statement runs. The save asks first, even at the Silent Safe Mode level. A convert cannot go out in the same save as a changed column or a changed primary key. Save Changes refuses it with Save column and primary key changes before converting existing columns. Save those changes first, then convert. Adding or dropping columns in the same save is fine.

​

Next Auto Increment

The field takes a whole number from 1 to the largest value the AUTO_INCREMENT column’s type holds: 2147483647 for INT, 4294967295 for INT UNSIGNED. Outside that range, Enter a whole number from 1 to … appears under the field and Save Changes refuses until you fix or clear it. The range is the column’s as the save leaves it, so a column widened to BIGINT in the same save takes a larger value. The server never sets the counter at or below the highest value already in the column: a value there becomes that value plus one. After the save, the field shows the counter read back from the server, not the number typed.

​

Saving changes

Nothing here reaches the server on its own. Column, index and key changes queue on the tab, Preview SQL (Cmd+Shift+P) shows the statements they will produce, and Cmd+S runs them. Change Tracking covers the queue, undo (Cmd+Z) and redo (Cmd+Shift+Z). Edit > Undo names the step it takes back, such as Undo Add Column or Undo Change Primary Key. A save runs on the tab’s own connection, database, and schema, the ones it was opened on, and never moves the sidebar or the toolbar.

• Save Changes (Cmd+S or the toolbar checkmark) applies the queue.
• Preview SQL (Cmd+Shift+P) shows the statements Save Changes would run, on the tab’s own schema. Save Changes on its sheet runs them, and Cancel runs none. A queue holding an incomplete change, which Save Changes would refuse, opens Some Changes Are Incomplete instead. A save that recreates the table previews as its rebuild script, with what the rebuild cannot carry over.
• File > Discard Changes… empties the queue after a confirmation that counts the changes it drops.

Switch the tab to Data with changes queued and Save Changes still applies them, as long as the grid holds no edits of its own. A save asks at most once, on a sheet showing the statements it will run. One that removes a column, an index, a key or a check constraint, changes a column’s type or character set, adds NOT NULL, changes the primary key, adds or rewrites a check constraint, or converts existing columns asks even at the Silent Safe Mode level. Any other save asks at Alert and above. A save that recreates the table asks on its rebuild script instead. At Safe Mode and Safe Mode (Full), Touch ID follows the answer. From Save Changes until the save ends, the tab shows Saving Changes…, takes no edits, and ignores a second Save Changes. A rebuild script runs only while the queue is still the one it was built from; edit the queue with the script on screen and the rebuild stops, so save again to review the new one. The queue outlives everything short of an explicit discard: closing the tab, closing the window, quitting, and Refresh all ask first. A save that never reaches the server leaves the tab open with its queue intact. A save runs on a connection of its own, so it never joins a transaction you have left open in a query tab. Once a save has run, even partway, every tab on the table loads its rows again, in any window: the tab on screen at once, its Data view included while Structure is in front, and the rest when you next switch to them. A tab holding unsaved row edits keeps its rows, and asks first if it is the one on screen in Data. Tabs on other tables are left alone.

​

When one statement fails

A save is often several statements, run in order. Engines with transactional DDL roll the whole set back; MySQL, MariaDB, and Oracle commit each one as it runs, so everything before the failure has landed while the queue still holds all of it. An Error Applying Changes sheet reports what the server said: refresh before saving again, or the second save replays work the server already did.

​

Triggers tab

Lists Name, Timing (BEFORE, AFTER, INSTEAD OF), Event (INSERT, UPDATE, DELETE), and Enabled where the engine reports it. Select one to read its CREATE TRIGGER statement from the catalog, with Copy and Open in Editor. New Trigger, Edit, and Delete sit in the action bar; the editor opens the trigger’s real DDL, including the trigger function on PostgreSQL. Trigger changes run on their own connection, like a structure save. A materialized view or a system table cannot carry a trigger, so the tab has no action bar there.

​

DDL tab

Read-only CREATE TABLE with syntax highlighting and font size controls. Copy, Export as a .sql file, and Open in Editor send it onward. On PostgreSQL the CREATE SEQUENCE and CREATE TYPE … AS ENUM statements the table depends on are prepended, so the script runs on an empty database, and the table’s COMMENT ON statements follow it. Open the tab on a view and it carries that view’s own CREATE VIEW or CREATE MATERIALIZED VIEW instead, the same statement Show DDL opens. A materialized view’s indexes follow it.

​

Parts tab (ClickHouse)

Lists partitions and parts from system.parts. Optimize merges parts; Drop Partition and Detach Partition act on the selected partition. Detached data stays on disk and is unreadable until it is re-attached.

​

Creating a new table

Click + at the foot of the sidebar and choose New Table…, or choose Database > New Table…. The tab opens on Definition, the same page an existing table’s structure uses, with a Table Name field at the top and one empty column to fill in. DDL beside it shows the statements the new table takes, and follows every edit. On an engine with a form of its own, such as DynamoDB, the pair reads Form and DDL. Click Create Table in the toolbar, or press Cmd+S or Cmd+Enter, to run it. The new table opens in a tab of its own. Where the engine cannot roll DDL back, as on MySQL, a statement that fails after CREATE TABLE leaves the table created: it opens on its Structure tab with the error, so finish the change there. A row that is started and unfinished is never dropped from the generated SQL. A list under Table Name names what still stands in the way, section and row first, such as Foreign Keys row 1: This foreign key needs a referenced table., and Create Table stays dimmed until the list is empty. Click an entry that names a row to select that row. With nothing selected, the inspector holds the new table’s Comment on engines that store table comments. On MySQL, MariaDB, TiDB and OceanBase it adds Engine, Character Set and Collation menus. They list what the server has: the engines it can create a table with, its character sets, and the collations of the chosen set, with the default engine and the set’s default collation marked (default). They start at what a new table in the target database gets anyway, the database’s own character set and collation and the server’s default engine, and the CREATE TABLE writes the engine, or the character set and collation as a pair, only when you change them. The Charset and Collation fields of a column work as they do on an existing table; see Charset and collation. In the Foreign Keys section, Columns, Ref Table, and Ref Columns open menus of the new table’s own columns, the database’s tables, and the referenced table’s columns. Each menu ends in Custom… for a name the list does not carry, and picking a second entry in a comma-separated cell appends to it. Name is optional on a foreign key. Left blank, no CONSTRAINT clause is written and the engine names the constraint itself. The Check Constraints section appears where the engine can add a check constraint. Indexes and check constraints run as their own statements after the CREATE TABLE, in one transaction where the engine has transactional DDL.

​

MongoDB collections

A collection’s fields are inferred from its first 200 documents: top-level field names are unioned across the sample, and each field takes its most common BSON type. Fields the collection’s $jsonSchema validator declares follow, including ones no sampled document holds. A field is nullable when the validator takes null in it, as missing fields and null describes; required decides only whether a document must have the field. _id comes first, marked as the primary key. The DDL tab shows indexes as createIndex() commands for mongosh, plus the validator and capped-collection options where present. Edit a field’s Name to rename it in every document, or remove its row to remove it from every document. Type, Nullable, adding a field and the Indexes section stay read-only. Renaming and removing fields shows the statements a save runs and what refuses one.

​

Limitations

• Changing a primary key on an existing table works on MySQL, MariaDB, PostgreSQL, PGlite, SQL Server, DuckDB, Snowflake, and Dameng. Elsewhere a save that changes the key is refused before anything runs, with the change named in the error: rebuild the table by hand.
• Cassandra / ScyllaDB: add and drop column only, no index editing, no visual table creation.
• ClickHouse: the Indexes section drops a data-skipping index but cannot add or change one. A save or a new table that tries is refused before anything runs. Run ALTER TABLE … ADD INDEX … TYPE bloom_filter in the SQL editor instead.
• Redshift, CockroachDB, BigQuery, Bigtable, Spanner, Elasticsearch, Typesense, SurrealDB, Beancount, Firestore, Firebase Realtime Database: structure is read-only.
• Redis, etcd, DynamoDB: no table schema to edit.

​

Refreshing

Database > Refresh (Cmd+R) or the toolbar refresh button reloads structure from the server; changes made through TablePro refresh on their own. A refresh reads the tab’s own database and schema, not the sidebar’s current selection. A tab with queued changes keeps them when another tab or window changes the table, and refreshes once they are saved, undone or discarded.