SQL Constraints

SQL Constraints

This page covers SQL constraints.

Constraints enforce data rules inside tables.

The previous tutorial SQL Keys encompassed primary and foreign keys, as well as unique and identity constraints (both related to the SQL keys).

This page continues with other important constraints.

• not null
• default
• create index
• check

Not null

In SQL, row records allow null (empty) values by default.

The “not null” constraint implements a rule of no null values. As such, columns cannot have empty cells.

Create table with not null constraint

The following is an example of creating a new table with 4 columns (id. first name, last name, and age).

CREATE TABLE student_info (
    id int NOT NULL,
    firstName varchar(50),
    lastName varchar(50),
    age int
    );

The “id” column contains the “not null” constraint, and as such cannot have empty data.

Alter table with not null constraint

Not null constraints can also be added through an alter statement. The below instance disallows empty cells under the column “age“.

ALTER TABLE student_info
ALTER COLUMN age int NOT NULL;

Default

As the name suggests, the “default” constraint sets a pre-specified value to a new record.

This occurs only when the new record does not contain data. If the cell contains data, the default value is overwritten.

Create table with default constraint

CREATE TABLE student_info (
    id int NOT NULL,
    firstName varchar(50) DEFAULT 'John',
    lastName varchar(50) DEFAULT 'Smith',
    age int
    );

The above sets default values for first name (as John) and last name (as Smith). As such, if no first name or last name is added when creating new records, the default values will be placed instead of empty cells.

Create index

The constraint of “create index” simply implements indexes.

Indexes can help boost the efficiency and performance of queries.

The prefix “idx” is a common practice of referring to indexes.

Create indexes

This constraint uses the keywords “create index on”. Table and column must be specified.

Regular indexes allow duplicates.

CREATE INDEX idx_age
ON student_info (age);

Create unique indexes

This constraint uses the keywords “create unique index on”. Table and column must be specified.

Unique indexes disallow duplicates.

CREATE UNIQUE INDEX idx_age
ON student_info (age);

Drop indexes

Dropping an index is quite straightforward. The keywords are “drop index”, including the table name as a prefix.

DROP INDEX student_info.idx_age;

Check

The constraint “check” is another useful SQL tool.

It restricts the range of values in a column.

Create table with check

Let’s create the previous table example, containing 4 columns (age columns has the check constraint).

CREATE TABLE student_info (
    id int NOT NULL,
    firstName varchar(50),
    lastName varchar(50),
    age int CHECK (age>=21)
    );

The above scenario ensures the age of the student is at least 21 (equal or greater than 21 years old).

Alter table with check

The constraint “check” can also be added through altering.

ALTER TABLE student_info
ADD CHECK (age<=21);

---

Next: SQL View