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.

 

To see that in action we first create two tables using SQL in the Command Window, a table T that has a constraint as in the example above and also a table V.

 

CREATE TABLE T (

  N NVARCHAR,

  CONSTRAINT Nc AS (N IN (SELECT name FROM [mfd_root]))

);

 

CREATE TABLE V (N NVARCHAR);

 

We twice insert strings into table T that in both cases meet the constraint:

 

INSERT T (N) VALUES ('T');

 

INSERT T (N) VALUES ('V');

 

Both of the above work OK because there are components named T and V in the project.  We now have a table T that has two records in it, and the field named N contains the values of T and V in it.

 

We now delete the table called V from the project:

 

DROP TABLE V;

 

After the above there is no longer a component called V in the project, so this will not work:

 

INSERT T (N) VALUES ('V');

 

...but because the above worked before when there was a table V in the project the record it created is still in table T.   We have forced an anomaly where table T contains data that does not meet the constraint for that field.   It refers to a table that no longer exists.

 

Notes

CPU Parallelization - Manifold automatically runs parallel for internal Manifold tasks and for Transform panel 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

 

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

 

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