Studio can build schema through dialogs or through SQL, and the dialogs show you the SQL they are about to run. Neither is a shortcut around the other: the dialog is faster for a table with eight columns, and the editor is faster for something you have written before.
Creating a table
A row per column, with the properties as columns of their own. The generated CREATE TABLE is
shown before anything runs, so what the dialog is about to do is readable rather than implied.
A single primary key column is written inline; several become a table-level PRIMARY KEY clause.
Creating an index
Key columns with sort directions, included columns for a covering index, an expression instead of a
column, and a WHERE for a partial one.
Note before building a partial index: the planner never chooses one. It is maintained on every write and does nothing for reads. See indexing.
Creating a trigger
The dialogs cover tables, views and indexes. A trigger is written in the editor, and the rules worth knowing before you start are these:
- a body may contain only
SELECT,INSERT,UPDATE,DELETEandMERGE NEWandOLDcan be read; assigning to them is not in the language yet- a
WHENcondition is written in brackets
There is no ALTER TRIGGER, so changing a body is a drop and a create. See DDL for
the statement itself, and routines for what a body may do.
What a change costs
This is the part worth knowing before planning a migration. Every edit you make in the designer is marked with what it takes before anything runs: the row carries the marker, and the line under the grid names the change and its cost. Nothing is sent to the database until you apply.
| Change | What it takes |
|---|---|
Add a column, including UNIQUE, CHECK, REFERENCES, DEFAULT and computed |
In place |
| Drop a column | In place |
| Rename a column or a table | In place |
Change DEFAULT or NOT NULL |
In place |
Add or drop UNIQUE, CHECK, FOREIGN KEY |
In place, and the constraint has to be named |
| Change a column's type | Rebuild |
| Add a primary key to an existing table | Refused by the engine |
| Drop a column that is part of the primary key | Refused by the engine |
| Reorder columns | Rebuild. The language has no way to move a column |
| Replace a view's body | Drop and create. There is no ALTER VIEW |
| Replace a trigger's body | Drop and create. There is no ALTER TRIGGER |
Two of these have reasons worth reading in full.
Dropping a column takes its foreign key with it and does not take its index. Studio drops the index first, because the alternative is an index over a column that no longer exists.
Changing a column's type is a rebuild rather than an ALTER COLUMN ... TYPE, and that is
Studio's decision rather than the engine's limit. The engine's version rewrites every row and stops
at the first value it cannot convert, with nothing changed and nothing said about the rest. Studio
rebuilds instead, so the conversion is a CAST you can read and the rows that will not survive it
are counted before anything happens.
Rebuilds are handed to you, not run
When a change needs a rebuild, Studio plans it in full before anything runs: four steps with their SQL, the objects that will be put back afterwards, the ones that point at the table and will not be, what the catalogue cannot carry across, and how many values the type conversion will destroy.
It produces the script and puts it in a query tab rather than running it.
A rebuild is a sequence of DDL statements, and an interrupted sequence of DDL statements is exactly what this engine cannot currently survive. Having the script in front of you means knowing what is about to happen and having the chance to take a copy first. See limitations.
Applying a set of edits
The designer collects edits and applies them together, and the report afterwards says what landed.
That report matters here more than it would elsewhere. This engine does not roll DDL back, so a set of edits cannot be all-or-nothing: it runs a statement at a time, stops at the first refusal, and then says which statements are in the database and which never ran. A set that half applied is a state you have to know about rather than one you can undo.
The order is not the order you typed them in either. An index on a column has to be dropped before the column, and renames go first because everything after them names columns.
Destroying things
Every destructive action asks a question that states its consequences rather than asking whether you are sure.
For a table or a view, Studio walks what depends on it:
- foreign keys in other tables that reference it
- views that read it
- how many indexes go with it
- how many triggers go with it
- how many rows are in it
An empty list is stated out loud as "nothing else refers to this", which is information. And if working out the consequences fails, the failure becomes an entry in the list rather than an empty list, because an empty list would be a lie in the one direction that matters.
The setting controlling whether these questions are asked is consulted in one place, so a command cannot forget to ask.
The structure tab
F4 on a tree node, or the menu. A tab per object, showing what it is made of and its CREATE
statement as the catalogue renders it.
Renaming, truncating
F2 renames in place. Truncate empties a table without walking its rows, and therefore without
firing row triggers, which is the difference between it and DELETE. Both ask first.
Where to go next
- DDL, the statements these dialogs write
- Indexing, what to index and what the planner will use
- Data and editing, the rows underneath the schema
- Limitations, why a rebuild is handed over rather than run