Schema

The Schema dialog becomes available in the Edit menu when the focus is on a table window.   We can also launch the Scheme dialog by right-clicking on a table in the Project pane and choosing Schema in the context menu.

A table has one or more fields with each field being a particular data type.  A table can also have zero or more indexes and zero or more constraints. All of these aspects of a table's structure taken together are referred to as a table schema.   We can edit schemas using the Edit - Schema dialog.

Everything in a schema has a name and each name must be unique in the schema.    For example, we may not have a field named x and an index named x in the same table, but we may have a field named x in one table and an index named x in a different table.   We can also have indexes named x within several different tables in the same database.   The names of indexes do not have to be unique within a database in Manifold.

The Schema dialog displays the structure, that is, the schema, of the table.   It provides a list of all fields and indices as well as their types.   It allows us to add new fields, to populate those fields when they are created, and to create new indexes of various types.

Commands

Click on an item in the schema to highlight it.

Indexes

In general, a table must have a btree or btreenull index to be editable.   It is a good idea to have a btree index in a table.  In general, if a drawing's table does not have an rtree index on the geometry field the drawing will not be able to display the objects.  See additional notes in the Indexes topic.

Constraints

Primarily used for data integrity checks, constraints are boolean expressions which evaluate to true for all table records.   Every time we try to insert a record into a table the system evaluates all constraints for the data about to be inserted and allows the insertion only if all constraints are satisfied. The system also checks constraints whenever we try to update a record.   If the attempted update fails one or more constraints it is rejected in full and the record is not modified.    See the Example: Create a Table with a Constraint topic for a step by step look at constraints in a table.

Notes

mfd_ is reserved - All field names beginning with mfd_ (case not significant) are reserved for internal use.   mfd_id is a reserved name for a field in tables, to be used only for the identity field that Manifold maintains as an automatically unique value field.

Works with SELECT results too - Launching Edit - Schema works with the results table returned by SELECT, same as with a regular table.  This allows us to see the schema of the results table.

See Also

Tables

Data Types

Indexes

Example: Create a Table with a Constraint - Create a simple table that includes a simple constraint upon one of its fields.  

Adding an Index to a Table - A basic topic on adding an index using the built-in capabilities of the mfd_id field.

Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This topic provides the step by step procedure for adding a spatial index.

Example: Add a UUID-based Index to a Table - Create a new computed field that is filled with UUID values on creation and then create an index on that field.   This technique creates an indexed field that has guaranteed unique values for all records and thus the indexed field and record values may be used in other projects.