Attribute Constraints

Specifying the DEFAULT Value of an Attribute

To set a default value for an attribute, we use theDEFAULT keyword followed by the desired default value in the CREATE TABLE statement:

CREATE TABLE table_name (
    column datatype constraints(s) DEFAULT <VALUE>,
    ...
);

NOT NULL (Attribute cannot be empty)

The NOT NULL constraint is used to specify that a column should not contain any NULL values. This constraint can be applied to any column in a table, including the primary key column.

CREATE TABLE table_name (
  column datatype constraint(s) NOT NULL
  ...
);

The CHECK Clause

The CHECK constraint is a feature in SQL that allows you to specify a condition that must be true for each row in a table. It is used to restrict the values that can be inserted or updated in a column by specifying a condition that must be true for the data to be entered into a column.

CREATE TABLE employees (
    id INT NOT NULL PRIMARY KEY UNIQUE,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(10),
    department VARCHAR(50),
    salary INT CHECK (salary > 0)
);

Alternatively, we can create a CHECK clause at the end of the CREATE TABLE statement that will apply to each tuple individually.

CREATE TABLE employes (
	...
	salary INT,
	...
	CHECK (salary > 0)
)

UNIQUE Clause

the UNIQUE constraint is used to specify that a column or a group of columns must have unique values across all rows in a table.

CREATE TABLE example_table (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE
);