In this example we create a simple table that includes a simple constraint upon one of its fields.
Start with a new project.
In the Project pane right-click and choose Create - New Table.
In the New Table dialog we will not bother changing the name of the table but will just leave it as the default name, Table. We press the Edit Schema... button to launch the Schema dialog.
The Schema dialog opens up, showing the field and the index that are added by default to any newly-created table. They are shown in provisional, bluish color to indicate they have not yet been added to the table. If we do not want those items in the table we can select them and delete them and add whatever fields, indexes or constraints we prefer instead.
In this case, we like the convenience of having a standard, default mfd_id identity field in our table and a btree index on that field in the form of the mfd_id_x index, so we leave the default identity field and index in place.
Press the Add command button.
Choose Field in the drop down menu. We will repeat this step three times, so except for this first time we will not illustrate every step.
That launches the Field dialog, which allows us to name the field, specify its type and, if we want it to be a computed field, to specify the expression to use to compute the value of the field.
We enter Name as the name of the new field, choose a type of nvarchar and then we press the OK button to add the field to the table's schema.
The field called Name has been added to the table schema, shown in provisional, bluish color. To add another field, we again press the Add button and then choose Field in the drop down menu.
This next field we will name Expenses and choose a type of int32. We press OK to add it to the schema.
The field called Expenses appears in provisional, blue color in the schema. To add another field, we again press the Add button and then choose Field in the drop down menu.
This field we will call Income, also of type int32. We press OK.
The schema now has all the fields we want: the Name, Expenses, and Income fields we added, plus the default mfd_id identity field and the mfd_id_x index. We will now add a constraint.
A constraint is an expression that must evaluate to a boolean TRUE for any record that is to be added to the table. Constraints are a way of forcing any new record to have whatever relationships between fields are required.
We press the Add command
button and then we choose Constraint in
the drop down menu.
In the Constraint dialog we will name new constraint Wisdom. The Template box we leave in the default choice of expression, since we want the constraint to be ruled by an expression. To create the expression for the constraint, we press the Edit Expression button.
In the Expression dialog, the expression we specify is
[Income] > [Expenses]
Expressions use an SQL-like syntax. They must evaluate as TRUE for every record that is added to the table. In this case we are saying that the system will not allow any record for which the Wisdom constraint is not TRUE. The system will not allow any record for which the value of the Expenses field for that record is greater than or equal to the value of the Income field for that record. We press OK to add the expression.
Back in the Constraint dialog we check our work, and then press OK to add the constraint to our schema.
The new constraint appears in provisional, bluish color in the Schema. To commit our changes to the schema and to exit the Schema dialog, we press Save Changes.
That pops us back into the New Table dialog. So far, we have just created the schema that the new table will use. To actually create the table, we click Create Table.
A table called Table is created in the Project pane. We can double-click on the new table to open it.
When we do so we see that it is blank, with no records as yet added and no values in the fields we created in the table.
To add a record we first double-click into the Name cell for the first record.
We enter the name Dieter, pressing Enter to finish editing that cell. Until they are populated with values the other cells will show <NULL> as their value, that is, no value.
We click the right arrow key to move the active cell to the right and then Enter to begin editing the cell. We enter a value of 5 for the Expenses cell followed by an Enter and then a right arrow to move the active cell to the right once more.
We press Enter to begin editing the cell, enter a value of 8 for the Income cell and then press Enter again. Dieter's income is greater than his expenses so the Wisdom constraint will be satisfied once we commit the record.
The changes we have made to the record are shown in blue preview color.
To commit the changes, we can right-click the triangle pointer in the row handle and choose Save Changes, or (as most experienced Manifold users would do), we would simply press the keyboard shortcut Ctrl-Enter.
Because the values for Income and Expenses conform to the Wisdom constraint, that is, because Income is greater than Expenses, the record will be added to the table.
When the record is created, automatically the mfd_id field, the default identity field used in Manifold tables, will be filled with a unique value.
We enter values for the next record by entering Hans for the name, a value of 3 for Expenses and a value of 4 for Income. We click the triangle pointer in the row handle to commit the changes.
Again, because the record for Hans conforms to the Wisdom constraint with Income being greater than Expenses, the record is added to the table.
Let us now see what happens when we try to enter a record that does not conform to the Wisdom constraint.
For our third record we enter the name Fred and we will enter values for Expenses and Income for which Wisdom is not TRUE, that is, for which the Expenses are greater than Income.
As long as we are still editing the record in preview mode we can change values as we desire, but when we try to commit changes by pressing the triangle pointer in the record handle the values must conform to all constraints or the changes will be rejected.
That is what happens in this case because for Fred's record Expenses are higher than Income so the Wisdom constraint expression evaluates to FALSE. To close the error message we press OK.
There are two choices for how to deal with a proposed edit or addition that does not satisfy constraints. One option is to right-click anywhere into the provisional record and to choose Undo Changes, or to right-click onto the triangle pointer in the row handle and to choose Undo Changes.
Choosing Undo Changes abandon adding the record.
Another option is to simply double-click into a cell and to change either the Expenses or the Income value so that Income is greater than Expenses.
That is what we will do in this example, changing Income to a greater value so that the Wisdom constraint expression can evaluate to TRUE.
Press Ctrl-Enter to save changes.
Since the Wisdom constraint is now fulfilled, the record will be added to the table.
Computed Fields and Constraints
Example: Editing Records in a Table - How to edit the contents of an existing record using mouse and keyboard.
Example: Adding Records to a Table - How to add a new record to a table using mouse and keyboard.
Example: Add a Field to a Table and Fill It - we add a field to a table and simultaneously for all records fill the field with a specified value. We do that in two examples, first a very simple one and second, a more elaborate example that also shows restriction to a selection as well as use of the Transform pane.
Example: Add a Computed Field to a Table - In this example we add a computed field to a table, illustrating how the computed field automatically changes when changes are made in the fields it uses for computation. We also show how computed fields can use geometry, automatically updating centroids when areas are changed. Last, we show how geometry can be created using computed fields, to create effective radius circles for antennas based on the power of the antenna.
Example: Add a Second Computed Geom Field to a Table - We can create tables with more than one geom field in the table and then we can create drawings which use those additional geom fields. This topic shows how to create a second geom that is a computed field based on the first geom. The topic also shows some "real world" methods, such as how to remember the use of a geometry function to do what we want, and how to restore a geom that has been moved. We close with some illustrations of how multiple geoms might be used, and how selection from any drawing or labels based on the same record selects the corresponding objects or labels in all other components based on that record.
SQL Example: Create a Table with a Constraint via a Query - A simple example using SQL to create a table with a constraint.
SQL Example: Force an Anomaly in Constraints - Constraints are only evaluated when we insert or update records. If a constraint refers to external data, such as the values in a different table, we can force an anomalous condition where the table with the constraint may contain data that no longer meets the requirements of the constraint.