DataslopeWhat Constraints Are
Learn how constraints encode business rules in the schema so invalid data is rejected before it can enter the database.
A customer signs up without an email. An order points to no customer. A product is saved with a negative price. Should your application remember to catch every one of those mistakes, every time?
A well-designed schema does not merely suggest good data. It uses constraints to make bad data impossible to store.
Constraints are enforced rules
A constraint is a rule the database checks on every write: every
INSERT, every UPDATE, and sometimes every DELETE. If the new data
breaks the rule, PostgreSQL rejects the write.
That rejection is not a failure of the database. It is the database protecting the meaning of your data.
The key idea is that constraints live in the schema. They are part of the database design itself, not a reminder written in a comment or a rule hidden in one application screen.
The database is the last line of defense
Application code should still validate user input. A form can show a friendly message before it ever talks to the database. But application validation is not enough by itself.
Many things may write to one database:
- A web app
- A mobile app
- An admin dashboard
- A background import job
- A one-time data migration
- A human running SQL directly
If the rule exists only in one of those places, another path can forget it. If the rule exists in the schema, every path must obey it.
That is why constraints are a design tool. They encode the business rules that must be true no matter which program touched the data.
Schema rules beat hope
A rule in application code says, "please remember to check this." A constraint says, "this database will not contain rows that violate this rule."
Quick check
Why is it valuable to put an important business rule in the schema?
It makes the rule apply only to the main web application.
It makes the database enforce the rule for every write, no matter where the write came from.
It lets invalid data in faster.
It replaces the need to design tables carefully.
Watch a constraint reject bad data
This table says every product must have a non-negative price. Run the SQL and read the error: PostgreSQL refuses the bad row.
The schema now knows something about the business: a product's price
cannot be negative. Any code path that tries to store -3.00 gets the
same answer: no.
The constraint family
PostgreSQL gives you several common kinds of constraints. Each protects a different part of the design.
- NOT NULL means a column must have a value.
- DEFAULT supplies a value when the insert does not provide one.
- UNIQUE prevents duplicate values such as repeated usernames.
- CHECK enforces an expression such as
price >= 0. - PRIMARY KEY gives every row a unique, non-empty identity.
- FOREIGN KEY prevents references to rows that do not exist.
Together, these rules turn a table from a loose bucket of rows into a model of the real thing.
Constraints express meaning
Suppose your application has orders. The schema can express facts like these:
A customer email is required? Use NOT NULL. No two accounts may share
one email? Use UNIQUE. An order must belong to a real customer? Use a
FOREIGN KEY. An order total cannot be negative? Use CHECK.
The more essential the rule is to the meaning of the data, the more it belongs in the schema.
Quick check
Which rule is best enforced with a database constraint rather than only with a comment in documentation?
A style preference for how a button should look.
An order total must never be negative.
A team's preferred meeting time.
The color of the company logo.
Constraints make errors visible early
Without constraints, bad data can sit quietly for months. A report is wrong, a join loses rows, or a customer cannot sign in — and the cause is an old invalid row.
With constraints, the mistake is caught at the door. The writer gets an error immediately, when the bad value is still close to the code or person that produced it.
That is why constraints are not just syntax. They are how a schema keeps its promises.
Check your understanding
What is a constraint in a database schema?
A query that sorts rows for display.
A rule PostgreSQL enforces on writes so invalid data is rejected.
A note for developers to read later.
A backup copy of a table.
Why is the database often called the last line of defense for data integrity?
It is the only place users can see error messages.
It runs after every report is generated.
Every application and import path ultimately writes to the database, so schema rules protect all of them.
It automatically knows every business rule without being told.
Which constraint type prevents two rows from using the same non-primary-key value, such as the same email address?
NOT NULL.
DEFAULT.
UNIQUE.
FOREIGN KEY.
A table has price NUMERIC CHECK (price >= 0). What happens if a writer tries to insert price = -5?
PostgreSQL stores the row and fixes the value later.
PostgreSQL changes the price to zero.
PostgreSQL rejects the write because the row violates the constraint.
PostgreSQL deletes the whole table.