Structure & Table Actions
Change columns and indexes, preview the generated SQL, and create, rename, copy or drop tables.
Mantidb lets you change a table's columns and indexes from a grid, without writing ALTER TABLE by hand. Like data edits, structure changes stay pending until you apply them, and you can see the exact SQL first.
Structure view
Open a table and click Structure in the bottom bar. Each row is a column of the table:
| Column | Description |
|---|---|
# | The column's position. |
column_name | The column's name. |
data_type | The declared type, such as integer or varchar(255). |
is_nullable | A checkbox: checked when the column Allows NULL. |
default | The default value expression. |
key | Whether the column is part of a key. |
Each row also has a revert button that undoes the pending changes on that column.
Indexes view
Click Indexes in the bottom bar to list the table's indexes:
| Column | Description |
|---|---|
index_name | The index's name. |
columns | The indexed columns. Click to Choose columns… for a new index. |
unique | Whether the index is unique. |
primary | YES for the primary key index. |
method | The index method, such as btree. |
Making changes
In the Structure view you can:
- Add a column with + Column in the bottom bar.
- Rename a column by editing
column_name. - Change its type, nullability or default.
- Drop a column.
In the Indexes view you can:
- Create an index with + Index.
- Drop an index.
The bottom bar shows how many changes are pending. A Pending SQL preview below the grid shows the statements Mantidb will run, updated as you edit. If a change can't be turned into SQL, the preview says Can't build the ALTER.
Primary keys, foreign keys and check constraints can't be edited from the Structure view. Change them with a query instead.
Applying changes
Press ⌘S to apply the pending changes, or click Discard to throw them away. Changes go through safe mode first, like any other write.
How the statements run depends on the engine:
| Engine | How changes are applied |
|---|---|
| PostgreSQL | In one transaction. If any statement fails, nothing changes. |
| SQLite | In one transaction. |
| SQL Server | In one transaction. |
| MySQL / MariaDB | Statement by statement. These engines can't roll back DDL, so changes made before an error stay applied. |
If the table has pending row edits in the Data view, commit or discard them before changing its structure.
When editing is blocked
The Structure view is read-only for:
- Views — "Only tables can be altered".
- Amazon Redshift — "Redshift structure is read-only".
- Connections in Read only safe mode.
Engine notes
- SQLite can only rename, add and drop columns. Changing a column's type, nullability or default isn't supported.
- SQL Server changes a column's type and nullability together, and drops and recreates named default constraints when you change a default.
- MySQL / MariaDB apply changes one statement at a time, as described above.
Table actions
Right-click a table in the sidebar to manage it. Right-click empty space in the list for New Table… only.
New table
New Table… creates a table in the current schema. It starts with an id primary key column and opens in the Structure view, where you add the other columns.
Rename
Rename… asks for the new name.
Queries, views and code that use the old name stop working.
Duplicate
Duplicate… copies the table's structure under a new name (<table>_copy by default). Check Copy the rows too to copy its data as well.
Some engines can't copy everything:
| Engine | What isn't copied |
|---|---|
| SQLite | Indexes and triggers |
| MySQL / MariaDB | Foreign keys |
| SQL Server | Primary key, indexes, defaults and constraints |
Truncate
Truncate… deletes every row and keeps the table. Mantidb asks: "Delete every row of “<table>” on <connection>? This can't be undone." On SQLite, it runs a DELETE.
Delete
Delete… drops the table and all its rows. Mantidb asks: "Delete table “<table>” and all its rows on <connection>? This can't be undone."
Truncate and Delete always ask for confirmation, even in Silent safe mode. On Production connections they also ask for Touch ID.
On PostgreSQL, Truncate and Delete don't cascade. They fail if another table references this one through a foreign key.
Copy DDL
Copy DDL copies the table's CREATE TABLE statement to the clipboard:
| Engine | Source |
|---|---|
| PostgreSQL | Rebuilt from the system catalog |
| CockroachDB | SHOW CREATE TABLE |
| Amazon Redshift | SHOW TABLE |
| MySQL / MariaDB | SHOW CREATE TABLE |
| SQL Server | Rebuilt from the sys.* catalog views |
| SQLite | The original CREATE statement from sqlite_master |
Amazon Redshift tables can be browsed and copied as DDL, but the other table actions aren't available.