Stage 2 · L11

Define entities, row identity and constraints

Core · original Session 2; extended normalization/cardinality visuals

An entity is a kind of fact with an identity. Equipment identifies a catalogue item; User identifies a customer profile; Rental identifies one rental record. At this stage a User row is not a login account and carries no password. The name is retained to align the later authenticated owner relationship; authentication arrives in stage 06.

Equipment.id is an integer primary key generated by PostgreSQL. User.id and Rental.id are UUIDs generated by the application default when a row is inserted. A primary key uniquely identifies a row and cannot be null. Equipment.name and User.email have separate uniqueness rules: a name is not the equipment identity, and a uniqueness rule can reject a second row even when its primary key differs.

Rental.equipment_id and Rental.owner_id are foreign-key columns. PostgreSQL checks that those values refer to existing equipment and customer rows. The Rental.equipment relationship is an ORM navigation declaration, not another stored column. It does not replace the foreign key. A direct SQL writer that never uses Python relationships is still constrained by the database.

Quantity must be at least one and daily_rate must be nonnegative. Input validation later gives clients readable errors, but database CHECK and NOT NULL constraints protect other writers too. The baseline submits raw SQL that bypasses Pydantic and proves each constraint rejects an invalid row. Numeric(10,2) stores bounded decimal money; use Decimal rather than binary floats for the request and model boundary.

Deletion is restricted for referenced equipment/customer rows. Rental history must not disappear because somebody deletes a catalogue item. An ORM cascade or delete-orphan rule controls session behavior; ON DELETE CASCADE controls database behavior for all writers. This schema deliberately uses neither destructive cascade for business history. Choose a policy based on the fact's ownership and retention requirements, not to silence a foreign-key error.

passive_deletes='all' tells SQLAlchemy not to null the child references when equipment is deleted. PostgreSQL then applies its RESTRICT foreign key. Without that ORM setting, a parent delete can first attempt an invalid NULL update and report a different constraint failure; the stage09 regression review caught and corrected that behavior.

For the raw SQL exercise, INSERT INTO equipment(name, quantity, daily_rate) names the target columns; VALUES supplies one matching tuple. Quoted text and numeric values have different SQL syntax. SELECT lists the columns to read FROM that table, and ORDER BY id makes this simple inspection deterministic. The deliberately invalid INSERT demonstrates stored constraints without a Pydantic request or a Session implementation.

INSERT INTO equipment(name, quantity, daily_rate)
VALUES ('Invalid stock', 0, 5);
-- quantity CHECK rejects this row.
SELECT id, name, quantity, daily_rate FROM equipment ORDER BY id;

quantity=0 violates CHECK; a duplicate catalogue name violates UNIQUE; NULL name violates NOT NULL; a missing parent violates the FOREIGN KEY. The primary key identifies a row independently of its display name. Decimal money is distinct from binary float. This answer does not implement a session or related insert.