DEFAULT and UNIQUE Constraint | Database

DEFAULT Constraint

This constraint provides a default value for a column. Default value will be added to all new records if no other value is specified. Below example create a table set default value None for column phone.

CREATE TABLE Person (
  id INT PRIMARY KEY,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  phone VARCHAR(20) DEFAULT 'None'
);

UNIQUE Constraint

UNIQUE constraint ensures that all values in a column are unique. The following statement creates a table whose data in the email column is unique among the rows in the Person table:

CREATE TABLE Person (
  id INT PRIMARY KEY,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  email VARCHAR(20) NOT NULL UNIQUE
);

Above example define UNIQUE constraint as a column constraint. Below example define the UNIQUE constraint as a table constraint,

CREATE TABLE Person (
  id INT PRIMARY KEY,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  email VARCHAR(20) NOT NULL,
  UNIQUE(email)
);

To assign a particular name to a UNIQUE constraint, you use the CONSTRAINT keyword as follows:

CREATE TABLE Person (
  id INT PRIMARY KEY,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  email VARCHAR(20) NOT NULL,
  CONSTRAINT unique_email UNIQUE(email)
);

Reference

SQL Unique Statement