7.2 Constraints on Attributes and Tuples

Constraints on Attributes and Tuples

  • Introduces two kinds of constraints declarable within a SQL CREATE TABLE statement: constraints on a single attribute and constraints on a tuple as a whole.
  • Outlines the structure of the section, covering not-null constraints, attribute-based CHECK constraints, and tuple-based constraints.
#1

read: Section 7.2: Constraints on Attributes and Tuples (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduces two kinds of constraints declarable within a SQL `CREATE TABLE` statement: constraints on a single attribute and constraints on a tuple as a whole.
- Outlines the structure of the section, covering not-null constraints, attribute-based CHECK constraints, and tuple-based constraints.

Within a SQL CREATE TABLE statement, we can declare two fundamental kinds of constraints to enforce data integrity.

  • Constraint on a single attribute.

    This type of constraint restricts the set of possible values for a single attribute. The constraint is declared along with the attribute’s data type and is checked whenever a value for that attribute is inserted or updated.

  • Constraint on a tuple as a whole.

    This form of constraint imposes a condition on the entire tuple, often involving relationships between multiple attributes within that tuple. It is checked whenever a new tuple is inserted or an existing tuple is updated.

The discussion will proceed by first introducing the simple NOT NULL constraint. We will then examine attribute-based CHECK constraints, followed by tuple-based constraints.

  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 20:22:35
  • Duration: 9.21758484840393
  • Cummulative cost: $1.30e-02

Not-Null Constraints

  • Describes the NOT NULL constraint, which disallows tuples where the specified attribute is NULL.
  • Explains that the constraint is declared using the keywords NOT NULL after the attribute’s declaration in a CREATE TABLE statement.
#2

read: Section 7.2.1: Not-Null Constraints (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Describes the `NOT NULL` constraint, which disallows tuples where the specified attribute is `NULL`.
- Explains that the constraint is declared using the keywords `NOT NULL` after the attribute's declaration in a `CREATE TABLE` statement.

  • The NOT NULL constraint is a fundamental attribute-level constraint.

    It mandates that a specific attribute in a relation cannot hold a NULL value. This ensures that every tuple in the relation possesses a defined value for the constrained attribute.

  • The constraint is declared directly within the CREATE TABLE statement.

    To enforce this constraint, the keywords NOT NULL are appended to the attribute’s declaration line. For example: presC# INT REFERENCES MovieExec(cert#) NOT NULL.

Definition: Not-Null Constraint

Let \(R\) be a relation with schema \((A_1, A_2, \ldots, A_n)\). A NOT NULL constraint on an attribute \(A_i\) is a rule that must be satisfied by every tuple \(t \in R\). The constraint is formally expressed as:

\[ \forall t \in R, t[A_i] \neq \text{NULL} \]

Any data modification language (DML) operation, such as an INSERT or UPDATE, that would result in a violation of this condition must be rejected by the database management system.

  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 20:22:43
  • Duration: 7.864001750946045
  • Cummulative cost: $9.53e-03

Attribute-Based CHECK Constraints

  • Introduces attribute-based CHECK constraints, which attach a complex condition to an attribute that must hold for every value of that attribute.
  • Explains that the condition can be anything that could follow WHERE in a SQL query, and can reference other relations via subqueries.
  • Details when the constraint is checked: whenever a tuple gets a new value for the specific attribute, either through an INSERT or UPDATE.
  • Highlights a key limitation: the constraint is not re-checked if a database modification changes data in another table that would cause the constraint to be violated.
#3

read: Section 7.2.2: Attribute-Based CHECK Constraints (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduces attribute-based `CHECK` constraints, which attach a complex condition to an attribute that must hold for every value of that attribute.
- Explains that the condition can be anything that could follow `WHERE` in a SQL query, and can reference other relations via subqueries.
- Details when the constraint is checked: whenever a tuple gets a new value for the specific attribute, either through an `INSERT` or `UPDATE`.
- Highlights a key limitation: the constraint is not re-checked if a database modification changes data in another table that would cause the constraint to be violated.

revise: add an example at the end to illustrate a real CREATE TABLE query that uses attribute based check.

More complex constraints can be attached to an attribute declaration using the CHECK keyword. This allows for the enforcement of a condition that must hold for every value of the attribute.

Definition: Attribute-Based CHECK Constraint

An attribute-based CHECK constraint is a predicate \(P(v)\) associated with an attribute \(A\). For any tuple \(t\) in the relation, the condition \(P(t.A)\) must not evaluate to FALSE.

  • Constraint Condition

The constraint is specified by the keyword CHECK followed by a parenthesized condition. In principle, this condition can be anything that could follow WHERE in a SQL query. The condition may refer to the attribute being constrained by its name. If the condition must refer to other relations or attributes, those relations must be introduced in the FROM clause of a subquery.

  • Constraint Checking

An attribute-based CHECK constraint is evaluated whenever a new value is provided for the attribute. This occurs during an INSERT operation or an UPDATE operation that modifies the attribute. The constraint is checked against the new value; if the condition is not met, the modification is rejected.

  • Limitation of Scope

It is important to understand that an attribute-based CHECK constraint is not re-evaluated if a database modification does not directly change the attribute with which the constraint is associated. This limitation can result in the constraint becoming violated if other data, referenced by the constraint in a subquery, is altered. This behavior is a design choice to allow for efficient implementation.

  • Example

Suppose we wish to require that certificate numbers for movie studio presidents be at least six digits. We could modify the attribute declaration within the CREATE TABLE statement for the Studio relation.

CREATE TABLE Studio (
    name VARCHAR(255) PRIMARY KEY,
    address VARCHAR(255),
    presC# INT REFERENCES MovieExec(cert#)
               CHECK (presC# >= 100000)
);
  • Version: 2
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 20:25:29
  • Duration: 15.02230978012085
  • Cummulative cost: $3.12e-02

Tuple-Based CHECK Constraints

  • Describes how to declare a constraint on the tuples of a single table using the CHECK keyword followed by a parenthesized condition.
  • Explains that the condition is interpreted for the entire tuple and can reference any of its attributes.
  • States that the constraint is checked every time a tuple is inserted or updated.
  • Notes that, like attribute-based checks, tuple-based checks are invisible to changes in other relations mentioned in subqueries.
#4

read: Section 7.2.3: Tuple-Based CHECK Constraints (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Describes how to declare a constraint on the tuples of a single table using the `CHECK` keyword followed by a parenthesized condition.
- Explains that the condition is interpreted for the entire tuple and can reference any of its attributes.
- States that the constraint is checked every time a tuple is inserted or updated.
- Notes that, like attribute-based checks, tuple-based checks are invisible to changes in other relations mentioned in subqueries.
Definition: Tuple-Based CHECK Constraint

A constraint on the tuples of a single table \(R\) may be declared within the CREATE TABLE statement. The declaration consists of the keyword CHECK followed by a parenthesized condition. This condition can be any predicate that could appear in a WHERE clause and is interpreted as a condition about a tuple in the table \(R\). The attributes of \(R\) may be referred to by name in the expression.

  • Declaration and Scope

    A tuple-based CHECK constraint is added to the list of attribute and key declarations within a CREATE TABLE statement. The condition applies to the tuple as a whole, allowing it to reference multiple attributes of that tuple.

  • Constraint Enforcement

    The condition of a tuple-based CHECK constraint is evaluated every time a tuple is inserted into the table or an existing tuple is updated. If the condition evaluates to false for the new or updated tuple, the database system rejects the insertion or update statement.

  • Limitations with External Data

    If the constraint condition references another relation via a subquery, the check is not activated when that other relation is modified. A change in the other relation that causes the condition to become false for an existing tuple in the constrained table will not be inhibited. The check is “invisible” to changes in other relations.

  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 20:23:06
  • Duration: 11.541538953781128
  • Cummulative cost: $1.25e-02

Comparison of Tuple- and Attribute-Based Constraints

  • States that a constraint involving more than one attribute of a tuple must be a tuple-based constraint.
  • Explains that if a constraint involves only one attribute, it can be written as either a tuple- or attribute-based constraint.
  • Compares the checking frequency, noting that a tuple-based constraint is checked more frequently (on any attribute change) than an attribute-based constraint (only on its specific attribute change).
#5

read: Section 7.2.4: Comparison of Tuple- and Attribute-Based Constraints (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- States that a constraint involving more than one attribute of a tuple must be a tuple-based constraint.
- Explains that if a constraint involves only one attribute, it can be written as either a tuple- or attribute-based constraint.
- Compares the checking frequency, noting that a tuple-based constraint is checked more frequently (on any attribute change) than an attribute-based constraint (only on its specific attribute change).

  • A constraint that involves more than one attribute of a tuple must be written as a tuple-based constraint.

    An attribute-based constraint is linked to the declaration of a single attribute. Therefore, any condition that references multiple attributes of the same tuple, such as CHECK (price < 1000 OR stock > 0), cannot be defined at the attribute level and must be declared as a tuple-based constraint.

  • A constraint that involves only one attribute of a tuple can be written as either a tuple-based or an attribute-based constraint.

    A condition like CHECK (bore <= 16) can be appended to the bore attribute definition or declared as a separate CHECK clause at the table level. The presence of other attributes or relations within subqueries does not alter this fact.

  • For a single-attribute constraint, the primary distinction is the frequency of validation.

    A tuple-based constraint is checked more frequently. It is evaluated whenever any attribute of the tuple is modified by an INSERT or UPDATE operation. In contrast, an attribute-based constraint is evaluated only when the specific attribute to which it is attached is modified.

  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-09-20 20:23:19
  • Duration: 12.58626389503479
  • Cummulative cost: $1.57e-02