SQL Example: Using the mfd_id Field and mfd_id_x Index

Following are a sequence of SQL examples of working with the mfd_id field and mfd_id_x index.   

 

We begin by launching a new instance of Manifold with a new project.   We launch the Command Window via a View - Command Window - SQL menu command.

 

We create a new table called t:

 

CREATE TABLE t (mfd_id INT64, a INT32, ac INT32 AS a+100, INDEX mfd_id_x BTREE (mfd_id))

INSERT t (a) VALUES (2), (3), (4);

 

This will create a table called t and will add three records to it.   We open the table t in the Project pane by double-clicking on it.   The table t appears as a new tab next to the Command window.    

 

While doing this example we would like to see both the table and the Command window at the same time, so we will Ctrl-Click the tab for the Command window to move it to the bottom of the Manifold desktop display with the table t shown above.  

 

Note that the fields mfd_id and a are editable while the  computed field ac is not editable, as indicated by the shaded background color of the ac column.  Values in computed fields like ac are determined by the computed expression and cannot be specified manually via editing the field.

 

We drop the mfd_id_x index:

 

ALTER TABLE t (DROP INDEX mfd_id_x);

 

Since the mfd_id_x field was the only btree index the limitations of tables without any btree index will now apply to our table t: all fields will now become read-only,  records can no longer be selected and so on.  Note that the backgrounds of all of the columns are now shaded since all of the fields are read-only and cannot be edited.

 

It is still possible to insert values either manually or using SQL:

 

INSERT t (a) VALUES (5), (6);

 

It is also possible to insert values which explicitly specify the value of the mfd_id field:

 

INSERT t (mfd_id, a) VALUES (20, 20);

 

We now drop the mfd_id field:

 

ALTER TABLE t (DROP mfd_id);

 

This will "drop"  the mfd_id field, as if it were indeed deleted, but in reality Manifold  simply hides the mfd_id field while internal Manifold machinery has not really deleted it.   It is still possible to insert values:

 

INSERT t (a) VALUES (30);

 

It is not, however, possible to refer to the mfd_id field because even though inside Manifold it is still there it has been "dropped."   Therefore attempted references to mfd_id like the following will fail:

 

INSERT t (mfd_id, a) VALUES (40, 40);

 

The above fails with an Invalid field error.

 

We now add an index called a_x on the a field.   We can do that since the values are all unique.

 

ALTER TABLE t (ADD INDEX a_x BTREE (a));

 

Because the table now contains a btree index it once again becomes editable for the record a and selectable.   The ac computed field remains not editable since it is a computed field.

 

We will now add the mfd_id field and the mfd_id_x index, recovering them from hidden status:

 

ALTER TABLE t (ADD mfd_id INT64, ADD INDEX mfd_id_x BTREE (mfd_id));

 

We can now see the values of the mfd_id field that were produced for the new records while the mfd_id field was hidden.  

 

If we now drop the a_x index,

 

ALTER TABLE t (DROP INDEX a_x);

 

...the table will remain editable because there remains a btree index in the table, the mfd-id-x index.  As long as a table has at least one btree index it is editable and selectable.

 

Many Manifold users will name the indexes they create by adding _x to the name of the field being indexed, for example, using the name a_x for the btree index created fon field a.  Choosing names is a matter of taste, but using a regular mnemonic style can  help our designs for tables be easier to understand in the future.

 

Note that using an underscore character _ in names instead of a hyphen character - avoids any possible parsing confusion over whether we intended an arithmetic minus operation instead of just a name and allows us to skip using square brackets around the name of the index.   Had we used a hypen character, as in a-x, we would have had to use square brackets around the name:

 

ALTER TABLE t (ADD INDEX [a-x] BTREE (a));

 

...to create the index and to drop the index:

 

ALTER TABLE t (DROP INDEX [a-x]);

 

Notes

CPU Parallelization - Manifold automatically runs parallel for internal Manifold tasks and for Transform pane templates and similar operations, using all of the CPU cores available in your system in parallel.   When writing queries manually using the Command Window make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in your system.

See Also

Tables

 

Data Types

 

Command Window

 

Command Window - Query Builder

 

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.

 

SQL Example: Create a Table with a Constraint via a Query -  A simple example using SQL to create a table with a constraint.