| Lesson 10 | Entity & Attribute Constraints |
| Objective | List common entity and attribute constraints and explain how they enforce integrity in a relational model. |
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.
CHECK (Discount BETWEEN 0 AND 1), where the discount is defined as a fraction. Pair it with NOT NULL if a value is required.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.
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.
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.
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.
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.
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.
| Attempt | Expected enforcement |
|---|---|
| Insert a customer with an unlisted country code | Country foreign key rejects it |
| Insert an order without CustomerID | NOT NULL rejects it |
| Insert an order with a nonexistent CustomerID | Customer foreign key rejects it |
| Ship an order before its OrderDate | Date CHECK rejects it |
| Leave ShipDate NULL | Permitted by the shown rules |
| Delete a customer that still has orders | ON 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.
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.
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.