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 we might not be able to add records to the table with the constraint because the requirements of the constraint cannot be met.

 

Consider the tables created in the SQL Example: Create a Table with a Constraint via a Query topic:  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.

Forcing an anomaly

To force an anomaly, we can delete the countries table and save the project as a .map file.  We close the project and then open the project from the .map file again.   The places table loads OK, but we can no longer add records to it.     

 

We cannot insert new records into the places table until either we add a countries table or the constraint is removed from the places table.   If there is no countries table the constraint will always return a NULL value because it refers to a table that does not exist, and that will reject all new records.

 

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.