Constraints in SQL
PRIMARY KEY, FOREIGN KEY and REFERENCES, UNIQUE, NOT NULL, CHECK, column and table constraints, named constraints, and the ON DELETE and ON UPDATE actions (RESTRICT, CASCADE, SET NULL).
- 24 cards
- 4 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Understand
A few cards from the deck
Integrity constraint: what is it?
Show the answer
A rule declared in the schema, which the database checks on every write and which makes it refuse any row that breaks it.
The rule holds for every application that writes to the database, whereas a check written in one program's code can be forgotten by another.
What do these two INSERT statements give?
```sql
CREATE TABLE client (
id INTEGER PRIMARY KEY,
email VARCHAR(80) UNIQUE,
age INTEGER CHECK (age >= 18)
);
INSERT INTO client VALUES (1, NULL, NULL);
INSERT INTO client VALUES (2, NULL, 20);`- Both pass: NULL is accepted by CHECK as well as by UNIQUE
- The first fails on CHECK, because NULL >= 18 is not true
- The second fails on UNIQUE, because two NULL emails are equal
- The first fails: a UNIQUE column must always be filled in
Show the answer
Both pass: NULL is accepted by CHECK as well as by UNIQUE
CHECK only rejects a false condition; NULL >= 18 is unknown, so it is accepted. UNIQUE does not compare NULLs with each other. To forbid emptiness, declare NOT NULL.
A constraint on a single column can be written in its definition; a constraint over several columns, such as a composite primary key, is written at the … level.
Show the answer
A constraint on a single column can be written in its definition; a constraint over several columns, such as a composite primary key, is written at the table level.
A column constraint covers only its own column. As soon as a rule links several, it has to be declared after the list of columns.
PRIMARY KEY: what is it?
Show the answer
A constraint that designates the column or columns identifying each row: their values differ from row to row and are never NULL.
A table has only one. It implies NOT NULL and uniqueness, and it is what the other tables point at. Over several columns it is written at table level:
PRIMARY KEY (order_id, line_no).How does a PRIMARY KEY differ from a column simply declared UNIQUE?
- It also forbids NULL, and there is only one per table
- Only the primary key creates an index to speed up lookups
- UNIQUE is only checked at the end of the transaction, the primary key on every row
- A primary key can only be put on an integer that counts up
Show the answer
It also forbids NULL, and there is only one per table
UNIQUE also creates an index, and is checked on every row like the primary key. The real difference: PRIMARY KEY implies NOT NULL and designates the table's identifier (only one); you can add as many UNIQUE constraints as you like.