Data integrity preserves the accuracy, consistency, and completeness of data throughout its lifecycle. In PostgreSQL, transactions and database-level rules help prevent application changes from leaving inconsistent records. section 2.3
PostgreSQL enforces integrity through complementary defenses:
- Data types reject values with incompatible formats, such as text in a NUMERIC price column. section 2.3.1
- NOT NULL requires a value; UNIQUE prevents duplicates; PRIMARY KEY combines uniqueness and non-nullability; FOREIGN KEY links values to another table. section 2.3.1
- CHECK constraints validate custom conditions, such as allowing only known product categories or nonnegative stock. section 2.1.3 section 2.3.1
Rejecting an invalid product price
Add a database rule, then test it: ALTER TABLE products.catalog ADD CONSTRAINT catalog_price_check CHECK (price > 0); UPDATE products.catalog SET price = -16 WHERE name = 'Morning Glory'; PostgreSQL rejects the update with a check-constraint violation instead of storing the negative price. Similar rules can require review text and restrict review ranks to values between 1 and 5.
