This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

A CHECK Constraint is either a < Table Constraint>, a <Column Constraint> or a <Domain Constraint> and defines a rule that constrains the set of valid values for a Base table. The required syntax for a CHECK Constraint is:

CHECK <Table Constraint> ::=
[ CONSTRAINT <Constraint name> ]  
CHECK (search condition) 
[ <constraint attributes> ] 

CHECK <Column Constraint> ::= 
[ CONSTRAINT <Constraint name> ] 
<Column name> CHECK (search condition) 
[ <constraint attributes> ] 

CHECK <Domain Constraint> ::= 
[ CONSTRAINT <Constraint name> ] 
CHECK (VALUE search condition) 
[ <constraint attributes> ]

A Base table may be constrained by zero or more CHECK Constraints, which specify a rule that a group of one or more Columns of a Table may contain only those values that fall into the set defined by the rule that is, a CHECK Constraint is satisfied if its search condition evaluates to TRUE or to UNKNOWN for all rows within its scope.

Here are some examples of CHECK Constraint definitions:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE);
-- defines a CHECK <Table Constraint> in CREATE TABLE

CREATE TABLE Table_1 ( 
  column_1 SMALLINT 
      CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE, 
  column_2 CHAR(5));
-- defines a CHECK <Column Constraint> in CREATE TABLE

ALTER TABLE Table_2 ADD CONSTRAINT constraint_2 
  CHECK(column_1>100 OR column_2='hello') 
      NOT DEFERRABLE INITIALLY IMMEDIATE;
-- defines a CHECK <Table Constraint> in ALTER TABLE

CREATE DOMAIN domain_1 AS SMALLINT 
  CONSTRAINT constraint_1 CHECK(VALUE IN 50,100,150) 
      DEFERRABLE INITIALLY DEFERRABLE;
-- defines a CHECK <Domain Constraint> in CREATE DOMAIN

ALTER DOMAIN domain_1 ADD CONSTRAINT constraint_2 
  CHECK(VALUE IS NOT NULL);
-- defines a CHECK <Domain Constraint> in ALTER DOMAIN

CHECK <Column Constraint>s may be defined only in a CREATE TABLE statement and must be for a single Column only. CHECK <Table Constraint>s may be defined in a CREATE TABLE or an ALTER TABLE statement and may be for one or more Columns. CHECK <Domain Constraint>s may be defined in a CREATE DOMAIN or an ALTER DOMAIN statement and must contain a search condition that uses the <value specification> VALUE; valid only in a <Domain Constraint> (the <data type> of a given instance of VALUE is the <data type> of the Domain that the <Domain Constraint> belongs to). A <Domain Constraint>'s search condition may not be a recursive search condition (that is, it may not refer, either directly or indirectly, to the Domain that the <Domain Constraint> belongs to).

Once created, a CHECK <Column Constraint> logically becomes a CHECK <Table Constraint>. The <Column Constraint> in this SQL statement:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT CHECK(column_1<400));

is therefore equivalent to the <Table Constraint> in this SQL statement:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  CHECK(column_1<400));

A CHECK Constraint's search condition may specify any conditional expression, subject to the following rules:

  • The search condition may not contain (a) a <target specification> or (b) a set function (i.e.: COUNT, AVG, MAX, MIN or SUM) unless the set function is contained in a subquery or (c) any of these functions: CURRENT_PATH, CURRENT_USER, SESSION_USER, SYSTEM_USER, USER, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, or LOCALTIMESTAMP or (d) any query that is possibly non-deterministic, as defined earlier in this chapter.
  • The search condition may not invoke a non-deterministic routine, or a routine which possibly modifies SQL-data.
  • If a CHECK Constraint belongs to a persistent Base table or to a Domain, its search condition may not refer to any temporary Tables.
  • If a CHECK Constraint belongs to a GLOBAL TEMPORARY Base table, its search condition may refer only to GLOBAL TEMPORARY Base Tables. If a CHECK Constraint belongs to a created LOCAL TEMPORARY Base table, its search condition may refer only to GLOBAL TEMPORARY Base Tables or to created LOCAL TEMPORARY Base tables. If a CHECK Constraint belongs to a declared LOCAL TEMPORARY Base table, its search condition may not refer to any persistent Base Tables.
  • If a CHECK Constraint belongs to a temporary Table defined with ON COMMIT PRESERVE ROWS, its search condition may not contain a subquery that refers to a temporary Table defined with ON COMMIT DELETE ROWS.

[Obscure Rule] If a CHECK Constraint's search condition can't be represented in INFORMATION_SCHEMA without truncation, your DBMS will return the SQLSTATE warning 01009 "warning-search condition too long for information schema".

Privileges

In order to create a CHECK Constraint, the <AuthorizationID> that owns the Schema to which the Constraint will belong must be the current <AuthorizationID> and must have the REFERENCES Privilege on every Column that is explicitly named in the CHECK Constraint's search condition. If the search condition doesn't explicitly name any Columns, the current <AuthorizationID> must have the REFERENCES Privilege on at least one Column of every Table referred to in the search condition.

A CHECK Constraint makes it impossible to COMMIT any operation that would cause the Constraint's search condition to evaluate to FALSE. (This means, of course, that if the condition evaluates to TRUE or to UNKNOWN, the Constraint is satisfied.) Thus, for example, the Constraint defined in this CREATE TABLE statement is violated if any row of TABLE_1 contains a COLUMN_1 value that is greater than 99:

CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  column_2 VARCHAR(4), 
  CONSTRAINT constraint_1 CHECK(column_1<100) NOT DEFERRABLE);

This SQL statement would therefore violate CONSTRAINT_1:

  • INSERT INTO Table_1 VALUES (105);

because a search condition that evaluates to FALSE violates the Constraint. Both of these SQL statements, however, would satisfy CONSTRAINT_1:

INSERT INTO Table_1 VALUES (-30); 
-- a search condition that evaluates to TRUE satisfies the Constraint

INSERT INTO Table_1 VALUES (NULL); 
-- NULL is allowed; a search condition that evaluates to UNKNOWN satisfies the Constraint

The first use of a CHECK <Table Constraint> is to restrict what range of values is allowed in a Column, for example:

ALTER TABLE Table_1 ADD CONSTRAINT constraint_1 
  CHECK(column_1 BETWEEN 5 AND 9) NOT DEFERRABLE;

You'll often see Column values restrained like this; it's a feature in dialog boxes. The second use of a CHECK <Table Constraint> is to see that two Columns within the same Table agree with each other, for example:

ALTER TABLE Films ADD CONSTRAINT constraint_1 
  CHECK(film_type <> 'Action' OR star = 'Stallone') NOT DEFERRABLE;

The third use is to find out whether some relation is true between a row in one Table, and a row in another Table or a different row in the same Table, for example:

ALTER TABLE Table_1 ADD CONSTRAINT constraint_1 
  CHECK (column_1 > (SELECT MAX(column_2) FROM Table_2) NOT DEFERRABLE;

This sort of thing was once illegal, but in modern variations of SQL, you'll see inter-table Constraint references on an occasional basis. The fourth use of a CHECK <Table Constraint> is documentary, for example:

ALTER TABLE Table_1 ADD CONSTRAINT comment_1 
  CHECK ('this is a comment ...' IS NOT NULL) 
      DEFERRABLE INITIALLY DEFERRED;

Most DBMSs allow comments to be added to the metadata some other way, so this final use is rare.

For <Domain Constraint>s, the general idea is that object X doesn't belong in type Y or, to put it positively: certain things go in certain classes. These two examples both express the theme "values based on this Domain must not be space":

CREATE DOMAIN domain_1 AS CHAR(1) 
  CONSTRAINT constraint_1 CHECK (VALUE <> ' ');

CREATE DOMAIN domain_2 AS CHAR(1); 

ALTER DOMAIN domain_2 ADD CONSTRAINT constraint_2 
  CHECK (VALUE <> ' ');

In a <Domain Constraint>'s CHECK condition, the word VALUE is a placeholder: your DBMS replaces it with the appropriate <Column name> when checking the Constraint. The second of these two SQL statements would force a Constraint check:

CREATE TABLE Table_1 ( 
  column_1 domain_1, 
  column_2 CHAR(10)); 
-- makes a Column based on DOMAIN_1

INSERT INTO Table_1 
VALUES (' ', 
        'bob');
-- fails; CONSTRAINT_1 stops it

A <Domain Constraint> applies to every Column that's defined on the Domain, now or in the future. This makes sense since it's rare that a Column is in a Domain all on its own and if the name of a manager is subject to some Constraint (must be alphabetic, say), then surely the employees' names and spouses' names should be subject to the same Constraint. There's a case for suggesting that "data type checking" is just a vague form of "<Domain Constraint> checking"; the error messages are different, but the point is the same you are restricted as to what you can put in.

If you want to restrict your code to Core SQL, don't use a subquery in a CHECK Constraint's search condition. Also, for Core SQL, the REFERENCES Privilege isn't needed to create a CHECK Constraint.

Comments

Comments loading...