Free 30-day beta access · no payment · progress saved
The business problem
Negative prices, duplicate emails and orphaned orders make reports unreliable.
What you will be able to do
Choose and write the right constraint for a simple business rule.
Use NOT NULL, UNIQUE and CHECK
Connect two tables with a foreign key
Choose a constraint instead of a trigger
Scope of this micro-course
This micro-course targets one focused skill. It does not replace a complete path or server administration training.
Prerequisites
Tables, columns and primary keys
Project and practice
8 verified exercises are available. Validation is based on the programme exercises.
Focused path
Your programme, in order.
Content remains centralised: completing an activity here also updates the path that contains it.
Micro-lessons
01
The problem to solve before syntax
Validation only in the interface can be bypassed by an import, an API or another process. The base must protect invariants which remain true whatever the writing path.
Use the simplest constraint that expresses the rule: type for technical domain, NOT NULL for obligation, UNIQUE for uniqueness, CHECK for a local condition, and FOREIGN KEY for a relationship.
A UPDATE without WHERE can modify an entire table. A restarted import can also create duplicates if the uniqueness and conflict behavior are not defined.
First write the SELECT that identifies the lines, run the change in a transaction, use RETURNING to observe the effects, and explicitly choose the conflict key of the UPSERT.
Check the number of records returned before and after, check the returned values and test resuming the import. A safe operation is repeatable or explicitly non-repeatable.
Create a controlled table for invoicing 2 training scenario
Create a structure that rejects duplicate IDs and negative values. You must produce the result, check it on the source data and explain what it allows you to decide in this billing system.
Create a controlled table for logistics 3 training scenario
Create a structure that rejects duplicate IDs and negative values. You must produce the result, check it on the source data and explain what it allows you to decide in this national expeditions.
Create a controlled table for rh 4 · training scenario
Create a structure that rejects duplicate IDs and negative values. You must produce the result, control it on the source data and explain what it allows you to decide in this human resources management.
Create a controlled table for audit 5 · training scenario
Create a structure that rejects duplicate IDs and negative values. You must produce the result, check it on the source data and explain what it allows you to decide in this audit log.