SQL Example: Create a Table with a Constraint via a Query

Using SQL in the Command Window we can create tables with constraints.

 

Suppose we start with a new, blank project.  The following query will create a table with a string field and a constraint which requires the values of that field be component names:

 

CREATE TABLE T (

  N NVARCHAR,

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

);

 

Now that we have a table called T we can try to insert a string that the constraint does not allow:

 

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

 

That fails because there is no component named X in the project.   But the following query is OK:

 

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

 

That works because there is a table called T in the project which we created at the beginning of this example.

 

This example uses constraints for string values, but a constraint could be any other type of value, for example, geom type.

 

Notes

CPU Parallelization - Manifold automatically runs parallel for internal Manifold tasks and for transforms 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: 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.