DEFAULT and UNIQUE - additional rules

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:

1dostepna BOOLEAN DEFAULT TRUE

A 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:

1dostepna BOOLEAN NOT NULL DEFAULT TRUE

UNIQUE - uniqueness

It forces that no two rows have the same value in a given column:

1email VARCHAR(120) UNIQUE

Two 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

  email

  or

  numer_karty

  .

All together

1CREATE TABLE czytelnicy (
2  id INT AUTO_INCREMENT PRIMARY KEY,
3  imie VARCHAR(50) NOT NULL,
4  email VARCHAR(120) UNIQUE
5);

A primary key, a required field and a unique e-mail - the shelf is ready for readers!