Attributes-Entities   «Prev  Next»

Lesson 10Entity & Attribute Constraints
ObjectiveList common entity and attribute constraints and explain how they enforce integrity in a relational model.

Entity and Attribute Constraints in Relational Databases

Entity and attribute constraints translate business rules into enforceable database rules. Primary keys identify records, attribute constraints restrict values, and foreign keys maintain valid references between records. Together they protect declared rules when applications insert, update, or delete data.

Constraints do not prove that every recorded fact is true. A valid date can still be the wrong birth date, and an existing CustomerID can still identify the wrong customer. Start with clear requirements, then state exactly what each constraint guarantees.

Attribute-level Constraints

  • NOT NULL: Rejects a missing SQL value. In PostgreSQL, an empty string and a string of spaces are not NULL; rejecting those requires a separate rule.
  • CHECK: Tests a condition such as CHECK (Discount BETWEEN 0 AND 1), where the discount is defined as a fraction. Pair it with NOT NULL if a value is required.
  • UNIQUE: Enforces uniqueness of a value or column combination across rows. Treatment of NULL and text comparisons depends on the DBMS and its settings.

DEFAULT is a companion feature, not a validation rule. It supplies a value when an insert omits the column or requests DEFAULT. It does not prevent an explicitly supplied value from violating the intended business rule. Required-value and CHECK constraints remain necessary.

These mechanisms implement parts of an attribute domain. A data type establishes the representation; additional rules define the permitted business values. A format or range restriction alone cannot verify real-world accuracy.

Entity-level Constraints

  • PRIMARY KEY: The selected identifier for rows, enforced as unique and non-null. Choose a stable key, but do not assume the SQL declaration makes its values immutable.
  • Candidate keys: Minimal sets of attributes that uniquely identify an entity instance under the business rules. Minimal means no attribute can be removed while retaining uniqueness.
  • Alternate keys: Candidate keys not chosen as the primary key. They commonly require UNIQUE together with NOT NULL on their components.

A composite key can contain several columns. A UNIQUE rule on (EmployeeID, SkillID) makes the pair unique; it does not make either column individually unique. Also, SQL does not establish whether a declared key is minimal in the business model. That is a design responsibility.

Review entity identifier design when selecting keys. A surrogate identifier does not eliminate the need to enforce other genuine uniqueness rules.

Relationship Constraints

A foreign key requires a qualifying reference to match an existing referenced key. For a simple mandatory reference, combine it with NOT NULL. In this lesson's example, every order must reference a customer, but a customer may exist without an order.

  • RESTRICT / NO ACTION: Reject a deletion that would leave a prohibited reference. In PostgreSQL, NO ACTION can permit deferred checking when configured, whereas RESTRICT cannot be deferred.
  • SET NULL: Clear the reference while retaining the referencing row. The columns and other rules must permit NULL, and the missing relationship must have a defined meaning.
  • CASCADE: Propagate a deletion to referencing rows. Use it only when those records should be deleted with the referenced record.
  • ON UPDATE CASCADE: Propagate changes to a referenced key where supported. Stable identifiers reduce the need for such changes.

A foreign key alone does not require every customer to have an order or limit a customer to one order. Additional participation and cardinality rules need separate enforcement. Likewise, a reference to a status table verifies that the code exists, not that an allowed status transition occurred.

Domains, Code Tables & Table-level CHECKs

  • Reference tables: Maintain allowed codes and their descriptions; foreign keys enforce membership.
  • Table-level CHECKs: Compare values in one row, such as a shipping date and order date.
  • Generated columns: Derive values according to supported DBMS rules. Generation is not, by itself, a general validation mechanism.

In PostgreSQL, CHECK rejects false but accepts true or unknown. A date comparison with a missing operand can therefore pass. Define nullability explicitly rather than expecting a comparison to require both dates.

For restrictions involving other rows or tables, choose the appropriate key, relationship, or DBMS-specific mechanism instead of treating an ordinary row CHECK as a universal solution. See the PostgreSQL constraint documentation for implementation details.

Practical SQL Examples

The following illustrative PostgreSQL 18 definitions retain the customer, country, and order example. Assume each customer account requires a distinct email value under the configured comparison rules. This is an application policy, not a universal rule about people or email ownership. Country membership does not verify a postal address.

CREATE TABLE Customers (
  CustomerID   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Email        VARCHAR(254) NOT NULL UNIQUE,
  CountryCode  CHAR(2) NOT NULL,
  CreatedAt    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Countries (
  CountryCode  CHAR(2) PRIMARY KEY,
  Name         VARCHAR(80) NOT NULL
);

ALTER TABLE Customers
  ADD CONSTRAINT fk_customers_country
  FOREIGN KEY (CountryCode) REFERENCES Countries(CountryCode);

CREATE TABLE Orders (
  OrderID      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  CustomerID   BIGINT NOT NULL,
  OrderDate    DATE   NOT NULL,
  ShipDate     DATE,
  TotalAmount  DECIMAL(12,2) NOT NULL CHECK (TotalAmount >= 0),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
      ON DELETE RESTRICT,
  CONSTRAINT chk_order_dates
    CHECK (ShipDate IS NULL OR ShipDate >= OrderDate)
);

The order-date rule permits an unshipped order and prevents a recorded shipping date from preceding its required order date. The amount rule assumes ordinary nonnegative orders; credits or adjustments may require a different model. DECIMAL controls numeric representation, not currency or factual accuracy.

Cases to test in a disposable practice database
AttemptExpected enforcement
Insert a customer with an unlisted country codeCountry foreign key rejects it
Insert an order without CustomerIDNOT NULL rejects it
Insert an order with a nonexistent CustomerIDCustomer foreign key rejects it
Ship an order before its OrderDateDate CHECK rejects it
Leave ShipDate NULLPermitted by the shown rules
Delete a customer that still has ordersON DELETE RESTRICT rejects it

These are expected outcomes from the definitions, not captured database results. Supply otherwise valid values when testing each case so that an unrelated error does not obscure the rule being examined.

Design Guidance

  1. Write the rule first: For example, “every order belongs to an existing customer” is more precise than “validate customers.”
  2. Identify its scope: One value, several columns in one row, uniqueness across rows, or a relationship between tables.
  3. Choose enforcement: Prefer declarative keys and constraints where they express the requirement. Application validation can provide friendly feedback but should not be the only protection for database uniqueness.
  4. Define exceptions and lifecycle: Decide how missing information, deletion, and historical records should behave.
  5. Document and test: Record the rule, owner, enforcement location, and valid and invalid examples in the data dictionary.

When adding constraints to existing data, inspect violations before changing the schema. Resolve errors or documented exceptions and validate existing rows as well as future writes. A disabled or unvalidated rule may not establish the guarantee readers expect.

Some rules require procedures, triggers, or coordinated application logic. Cross-row checks must also account for concurrent transactions; a separate “check first, insert later” sequence can race with another writer.

Common Pitfalls

  • Assuming NOT NULL rejects empty text or verifies that a supplied value is meaningful.
  • Treating a default as a restriction on values supplied by callers.
  • Using a nullable foreign key for a relationship that must always exist.
  • Assuming a primary key cannot change or automatically proves real-world identity.
  • Making email unique without deciding how shared addresses, case, and account identity should work.
  • Choosing cascade deletion merely for convenience when referencing records must remain.

Summary

Use primary and alternate keys for identity, types and value constraints for attribute rules, and foreign keys for references. Defaults and generated values complement those mechanisms but do not replace validation. State the guarantee precisely and test both permitted and rejected cases.

The module conclusion brings together entities, attributes, instances, identifiers, domains, repeating facts, and the constraints that protect the resulting design.


SEMrush Software 10 SEMrush Banner 10