Constraints
A data type says what kind of value a column holds. A constraint goes further and says which values are acceptable: this one may not be left empty, that one may not repeat. Write the rule once, when you build the table, and the database enforces it on every row anyone ever adds.
1A rule you only have to write once
You could try to be careful instead — always check for a duplicate Aadhaar before adding a teacher, always remember the name is compulsory. People forget, and programs have bugs. A constraint cannot forget. It is checked by the server on every single insert, by every user, for ever.
A constraint is a restriction placed on a column that the database enforces.
2The five you need
| Constraint | What it forbids | Duplicates? | NULLs? |
|---|---|---|---|
| primary key | Two rows that cannot be told apart | Not allowed | Not allowed |
| not null | Leaving the column empty | Allowed | Not allowed |
| unique | The same value appearing twice | Not allowed | Allowed |
| default | Nothing — it supplies a value instead | Allowed | Allowed |
| check | Any value failing a condition you write | Allowed | Allowed |
not null, unique and primary key. default and check come from the class notes; check in particular is not in the Informatics Practices (065) syllabus, and its section below is marked accordingly.unique not null gives you something that behaves like a primary key — which is precisely what an alternate key is.3Writing them into a table
A constraint is written after the data type, on the same line as the column. Here is the teachers table, which uses all four:
Read it as a set of decisions about the school:
Every teacher has an id, and no two share one. This is how a row is identified.
A teacher without a name is not a record worth keeping. It may repeat, though — two people can share a name.
No two teachers can have the same PAN. But a new joiner may not have submitted it yet, so NULL is allowed.
Both rules together: compulsory, and never repeated. An alternate key.
Most teachers live locally, so that is filled in when nobody says otherwise.
No constraints. They may be left empty and may repeat freely.
4Where they show up in desc
Every constraint you wrote is visible here, in one of three columns:
not null is in force. id, name and aadhar all say NO.
PRI is the primary key; UNI marks a unique column — pan and aadhar.
The default value, sitting where every other column says NULL.
5Watch them refuse
This is the part that makes constraints believable. Each of these is a real attempt to break one of the rules above.
A duplicate primary key. Student 1099 already exists:
A NULL primary key. Every row must be identifiable:
A duplicate unique value. That Aadhaar belongs to somebody already:
A missing compulsory value. The teacher's name was left out, and name is not null:
name varchar(30) not null default 'Unknown', the row would have been accepted.6check — a rule of your ownOptional for Informatics Practices
not null, unique and primary key; check comes from the class notes and is worth knowing alongside them. It is not in the Informatics Practices (065) syllabus, so IP students can skip this section.The four constraints so far are fixed rules — no duplicates, no empties. check lets you write your own condition, and the database refuses any row that fails it.
A school admits children between 3 and 20. Nothing about int says that, so say it yourself:
The condition in the brackets is an ordinary one — the same and and comparisons a where clause uses. A sensible age is accepted without comment:
An age of 25 is not:
And neither is an age of 2, at the other end:
Query OK. That is what >= and <= buy you. Written as > and <, a three-year-old could not be admitted.The rule guards update as well, which is easy to forget — a constraint is a promise about the data, not about one command:
admissions_chk_1 is the name MySQL gave the rule, since we did not name it. You can see it, and the exact condition, with show create table:
check and then never enforce it — which is worse than not having one. Everything above was run on MySQL 8.0.46, where it is properly enforced. If a check seems to do nothing on an old lab machine, that is why.7A primary key of two columns
The Keys lesson introduced the composite key: when no single column is unique, a group of columns acts as the primary key together. Here is how that is actually written — primary key moves to its own line at the end, with the columns in brackets:
Two rows of the description say PRI. One student may appear on many dates, and one date covers many students — but the pair occurs once, which is exactly the rule an attendance register needs:
Notice the duplicate value MySQL reports: '101-2026-09-11' — the two columns joined together. That is the clearest possible evidence that the key is the combination, not either column on its own.
8default, doing its job
The other three constraints refuse things. default is the one that helps: leave the column out and it fills itself in.
No city was supplied, and Singtam appeared anyway. Without the default the cell would have been NULL. A default is a sensible guess, not a rule — you can still write any other city, and it will be accepted.
9Recap
No duplicates, no NULLs, one per table. Shows as PRI.
A value is compulsory. Shows as Null: NO.
No duplicates, but NULL is allowed. Shows as UNI. Many per table.
Fills in v when you supply nothing. Shows in the Default column.
What is the difference between unique and primary key?
A column is declared city varchar(30) default 'Singtam'. A row is inserted without a city. What is stored?
Which error appears when you insert a row whose primary key value is already present?
Which constraint would you use for a column that must always be filled in but may repeat?