In this example we create a simple table that includes a simple constraint upon one of its fields.
Create a new project and then in the Project pane right-click and choose 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.
In the Schema dialog we will click on the <new field> choice, which opens up boxes to name the field, specify its type and, if we want it to be a computed field, an 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 Add button to add the field to the table's schema.
Note that the field called Name has been added to the table schema. The next field we will name Expenses and choose a type of int32. Again, we press the Add button.
Note that the field called Expenses has been added to the table schema. We now will add another field called Income, also of type int32.
To add a constraint we will click on the <new contraint> choice. When we do so boxes to add a constraint appear.
We will call our new constraint wisdom and the expression we specify for it is
[Income] > [Expenses]
Expressions use an SQL-like syntax. They must evaluate as TRUE for every record in the table. In this case we are saying that the table will not allow any record for which the wisdom constraint is not TRUE, that is, the table will not allow any record for which the value of the Income field for that record is not greater than the value of the Expenses field for that record.
When we press the Add button we see that the wisdom constraint has been added to our table's schema. Press OK to exit the Schema dialog.
Back in the New Table dialog we press Create Table to create the table with this schema, that is, with these fields and with this constraint. 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 and 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 is satisfied.
The changes we have made to the record are shown in blue preview color. We then click the triangle pointer in the row handle or we press Ctrl-Enter to commit the changes. 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.
Note that when the record is created automatically the mfd_id field, the ID field in every Manifold table, is 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's 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 Jason 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.
So 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's what happens in this case because for Jason's record Expenses are higher than Income so the wisdom constraint expression evaluates to FALSE.
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 onto the triangle pointer in the row handle and to choose Undo Changes - that will 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.
When we now click on the triangle pointer row handle the record will be added to the table.
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 dialog.
Example: Add a Computed Field to a Table - In this example we add a field to a table. We first set the values for a field dynamically with a computed field using the Add Computed Field option in the New Field dialog. We then illustrate what happens when we fill a field statically with values using the Add Field option. Last, we show what the Add Component choice does in the New Field dialog.
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.