SQL and MySQL course Β· Module 4: Creating tables (DDL)

DEFAULT and UNIQUE - additional rules

2 min read Β·
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 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:

dostepna BOOLEAN NOT NULL DEFAULT TRUE

UNIQUE - uniqueness

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

email 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

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. 1. What does the DEFAULT constraint do on a column, e.g. dostepna BOOLEAN DEFAULT TRUE?

  2. 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.

  1. QuestionHow DEFAULT works
  2. QuestionHow UNIQUE works
  3. QuestionPRIMARY KEY versus UNIQUE
  4. Coding taskA reader with a unique e-mail
  5. Put in orderThe dostepna column with DEFAULT
  6. Coding taskA column with a default value

Useful articles