SQL and MySQL course Β· Module 4: Creating tables (DDL)
DEFAULT and UNIQUE - additional rules
In this lesson4
You already know PRIMARY KEY, AUTO_INCREMENT and NOT NULL. The scriptorium has two more valuable rules that bring order to data: DEFAULT and UNIQUE.
DEFAULT - a default value
When we do not provide a value for a column while saving a new row, MySQL inserts a default value for us:
dostepna BOOLEAN DEFAULT TRUEA new book is available by default, until someone borrows it. We write DEFAULT after the type of the column, giving the value that should appear automatically. We can combine it with NOT NULL:
dostepna BOOLEAN NOT NULL DEFAULT TRUEUNIQUE - uniqueness
It forces that no two rows have the same value in a given column:
email VARCHAR(120) UNIQUETwo readers cannot provide the same e-mail address - the Library will not accept such a record.
UNIQUE versus PRIMARY KEY
They are close relatives, but there are differences:
- A table has only one PRIMARY KEY, but it may have many UNIQUE columns.
- A PRIMARY KEY can never be empty; a UNIQUE column (without NOT NULL) may take NULL.
- PRIMARY KEY is the main way of identifying a row; UNIQUE guards the uniqueness of additional fields, like
emailornumer_karty.
All together
CREATE TABLE czytelnicy (
id INT AUTO_INCREMENT PRIMARY KEY,
imie VARCHAR(50) NOT NULL,
email VARCHAR(120) UNIQUE
);A primary key, a required field and a unique e-mail - the shelf is ready for readers!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does the DEFAULT constraint do on a column, e.g. dostepna BOOLEAN DEFAULT TRUE?
2. What does the UNIQUE constraint placed on the email column guarantee?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a CREATE TABLE statement that creates the czytelnicy table with columns: id of type INT with AUTO_INCREMENT and PRIMARY KEY, imie of type VARCHAR(50) NOT NULL and email of type VARCHAR(120) with the UNIQUE constraint.
- Horizontal ordering
Arrange the elements of a column definition with a default value on one line, from left to right:
- Code editor
Write a CREATE TABLE statement that creates the egzemplarze table with columns: id of type INT with AUTO_INCREMENT and PRIMARY KEY, and dostepna of type BOOLEAN with a default value of TRUE.