Database Management · Intermediate level

Intermediate level · Module 3: Views, Constraints and Transactions · Lesson 1 of 3

Constraints and Defaults

Let the database enforce rules with NOT NULL, UNIQUE, CHECK, defaults and foreign key actions.

In this lesson you will

  • Add constraints that enforce business rules
  • Choose foreign key actions for deletes

Key concepts

CHECK constraintUNIQUE across columnsDEFAULTON DELETE RESTRICT / CASCADE

The lesson

Constraints keep invalid data out no matter which program writes it. CHECK (quantity > 0) rejects zero or negative quantities; UNIQUE (household_id, distribution_id) stops a household receiving the same distribution twice.

DEFAULT supplies a value when none is given, such as DEFAULT CURRENT_DATE for a registration date.

Foreign key actions decide what happens when a referenced row is deleted: RESTRICT refuses the delete; CASCADE deletes dependent rows too. Use CASCADE carefully — deleting a household should probably not silently delete its distribution history.

Step by step

  1. Add CHECK and UNIQUE constraints to the NGO schema.
  2. Add defaults for dates and status columns.
  3. Choose foreign key actions and justify them.
  4. Try invalid inserts and deletes and confirm they are rejected.

Practice activity

Add all constraints needed by the NGO's rules and prove each one with a rejected operation.

Expected result: Every rule is enforced by the database and demonstrated with a failing statement.

Check your understanding

  1. Why enforce rules in the database rather than only in applications?

    Show answer

    Every program and person writing data is then held to the same rules.

  2. What does ON DELETE CASCADE do?

    Show answer

    Deletes dependent rows when the referenced row is deleted.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.