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

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

 

We create a table containing a list of countries.  Next, we create a table that will contain places that also has a constraint.

 

The countries table:

 

CREATE TABLE countries (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  name NVARCHAR, INDEX name_x BTREE (name),

  footballperf NVARCHAR);

INSERT INTO countries (name, footballperf) VALUES

  ('France', 'champion'),

  ('Croatia', '#2'),

  ('Belgium', '#3'),

  ('United Kingdom', '#4');

 

The places table:

 

CREATE TABLE places (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  place NVARCHAR,

  country NVARCHAR,

  CONSTRAINT countryvalid_c

    AS [[ country IN (SELECT name FROM countries) ]]

);

 

The constraint in the places table allows adding records only if the country is in the countries table.


We can illustrate the constraint in action by adding records to the places table, including attempting to add a record that the constraint does not allow:

 

INSERT INTO places (place, country)

  VALUES ('Paris', 'France');

INSERT INTO places (place, country)

  VALUES ('London', 'United Kingdom');

INSERT INTO places (place, country)

  VALUES ('Lisbon', 'Portugal'); -- fails

 

The first two records go through, while the third fails due to the constraint firing, as expected.  There is no country called Portugal in the countries table.

 

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

 

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.