7.3 Modification of Constraints

Modification of Constraints

  • Discusses the ability to add, modify, or delete constraints at any time.
  • Explains that the method of modification depends on whether the constraint is associated with an attribute, a table, or a database schema.
#1

read: Section 7.3: Modification of Constraints (section)

model: pro
rules: bullet definition math presentation short


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

- Discusses the ability to add, modify, or delete constraints at any time.
- Explains that the method of modification depends on whether the constraint is associated with an attribute, a table, or a database schema.

  • It is possible to add, modify, or delete constraints at any time after the initial schema definition.

    A database schema is not static. The requirements for data integrity may evolve, necessitating changes to the constraints that enforce business rules. SQL provides data definition language (DDL) commands to manage these modifications.

  • The method used to express such modifications depends on whether the constraint is associated with a specific attribute, a table, or the entire database schema.

    For constraints associated with a table, such as tuple-based CHECK constraints, keys, or foreign keys, the ALTER TABLE statement is the primary mechanism for modification. Schema-level constraints, known as assertions, are handled differently.

  • To modify or delete a constraint, it must first be given a name.

    An existing constraint can only be referenced for alteration or deletion if it has a unique name. The CONSTRAINT keyword is used to assign a name during the constraint’s definition. If a name is not explicitly provided, the database system typically assigns an internal, system-generated name.

    -- Naming a primary key constraint
CREATE TABLE MovieStar (
    name CHAR(30) CONSTRAINT NameIsKey PRIMARY KEY,
    ...
);

-- Naming a tuple-based CHECK constraint
ALTER TABLE MovieStar
ADD CONSTRAINT RightTitle CHECK (gender = 'F' OR name NOT LIKE 'Ms.%');

  • Constraints are managed using the ALTER TABLE statement, which supports adding and dropping constraints.

    One can introduce a new constraint using the ADD clause or remove an existing one using the DROP clause. Note that when adding a constraint, it must be satisfied by all existing tuples in the table; otherwise, the operation will fail.

    -- Dropping a constraint
ALTER TABLE MovieStar DROP CONSTRAINT NameIsKey;

-- Adding a constraint
ALTER TABLE MovieStar ADD CONSTRAINT NameIsKey PRIMARY KEY (name);
  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-10-15 23:17:22
  • Duration: 17.34000587463379
  • Cummulative cost: $1.60e-02

Giving Names to Constraints

  • Explains that a constraint must have a name to be modified or deleted.
  • Demonstrates how to name a constraint using the CONSTRAINT keyword followed by a name.
#2

read: Section 7.3.1: Giving Names to Constraints (section)

model: pro
rules: bullet definition math presentation short


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

- Explains that a constraint must have a name to be modified or deleted.
- Demonstrates how to name a constraint using the `CONSTRAINT` keyword followed by a name.

To modify or delete a constraint, it is necessary for the constraint to possess a name. The name serves as a unique identifier, allowing for direct reference in ALTER TABLE statements. One should make it a practice to name all constraints upon their creation.

  • The CONSTRAINT Keyword

    A name is assigned to a constraint by preceding its definition with the keyword CONSTRAINT followed by the chosen name. This name must be unique within the schema.

  • Examples of Named Constraints

    Naming can be applied to any type of constraint, including primary keys, foreign keys, and check constraints.

    • An attribute-based constraint:
    gender CHAR(1) CONSTRAINT NoAndro CHECK (gender IN ('F', 'M'))
    • A tuple-based constraint:
    CONSTRAINT RightTitle
CHECK (gender = 'F' OR name NOT LIKE 'Ms.%')
Definition: Named Constraint

A named constraint is a constraint declaration that includes an explicit identifier. The general form is:

\[ \text{CONSTRAINT} \quad \mathit{constraint\_name} \quad \mathit{constraint\_definition} \]

where \(<\mathit{constraint\_name}>\) is the identifier for the constraint and \(<\mathit{constraint\_definition}>\) is the specification of the constraint rule (e.g., PRIMARY KEY, CHECK (...)).

  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-10-15 23:17:34
  • Duration: 11.6324942111969
  • Cummulative cost: $1.35e-02

Altering Constraints on Tables

  • Describes using the ALTER TABLE statement to change constraints.
  • Explains how to drop a constraint using the DROP CONSTRAINT clause.
  • Explains how to add a constraint using the ADD CONSTRAINT clause.
  • Notes that an added constraint must hold for every existing tuple in the table.
#3

read: Section 7.3.2: Altering Constraints on Tables (section)

model: pro
rules: bullet definition math presentation short


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

- Describes using the `ALTER TABLE` statement to change constraints.
- Explains how to drop a constraint using the `DROP CONSTRAINT` clause.
- Explains how to add a constraint using the `ADD CONSTRAINT` clause.
- Notes that an added constraint must hold for every existing tuple in the table.

Changes to constraints are effected with an ALTER TABLE statement. This statement allows one to add or remove constraints associated with a table.

  • Drop a constraint from a table.

    To remove a constraint, one must specify its name. This is accomplished with the DROP CONSTRAINT clause. It is therefore imperative to name constraints upon their creation if one anticipates they might need to be modified or dropped later.

    Definition: Dropping a Constraint

    The syntax to drop a constraint is as follows:

    ALTER TABLE <table-name> 
DROP CONSTRAINT <constraint-name>;

  • Add a constraint to a table.

    One may also add a new constraint using the ADD CONSTRAINT clause. The added constraint must be of a kind that can be associated with a table, such as a tuple-based CHECK constraint, a PRIMARY KEY constraint, or a FOREIGN KEY constraint.

    Definition: Adding a Constraint

    The syntax to add a constraint is as follows:

    ALTER TABLE <table-name> 
ADD CONSTRAINT <constraint-name> <constraint-definition>;

  • A crucial condition for adding constraints.

    A constraint cannot be added to a table unless it holds for every tuple currently in the table. The database system will verify the new constraint against all existing data before permitting the schema alteration. If any tuple violates the constraint, the ALTER TABLE statement will fail.

  • Version: 1
  • Model: gemini-2.5-pro
  • Created: 2025-10-15 23:17:48
  • Duration: 14.524648904800415
  • Cummulative cost: $1.58e-02