gQuest

Constraints

Draft

Constraints move basic data rules into PostgreSQL. They make invalid states harder to store, no matter which application sends the SQL.

Insert valid data

The row passes NOT NULL, UNIQUE, PRIMARY KEY, and CHECK constraints.

Loaded Database:constraints.database-init
INSERT INTO products (sku, name, price_cents)
VALUES ('TEE-001', 'PgQuest T-shirt', 3200)
RETURNING *;
Loading plan...

Inspect constraints

PostgreSQL stores constraints in system catalogs that you can query.

Loaded Database:constraints.database-init
SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'products'::regclass
ORDER BY conname;
Loading plan...

Try Yourself

Loaded Database:constraints.database-init

The database for this lesson is already loaded. Write any query you want and run it directly in your browser.

Exercises

0 OF 2 DONE
1.Insert a product with sku BAG-001, name PgQuest Tote, price 4200, and return it.
2.List the constraint names for the products table ordered by name.

What We Learned

  • PRIMARY KEYidentifies each row and prevents duplicate ids.
  • UNIQUEprevents duplicate values in selected columns.
  • CHECKrequires each row to satisfy a boolean expression.
  • pg_constraintis a PostgreSQL catalog containing table constraints.