SQL Constraints

SQL - Constraints

This tutorial will explain how to create /remove Constraints in SQL and how to use them with the examples.

Constraints are the rules enforced on the data columns of a table. These are used to prevent invalid data to be entered into the column.

Column level constraints;

NOT NULL Constraint − Prevents NULL value to be entered into the column.

The following example ensures Column1 value cannot be entered as null
CREATE TABLE Table1 (
Column1 VARCHAR(50) NOT NULL
);

DEFAULT Constraint − Defines a default value for a column when no data entered.

The following example ensures if Column1 value entered as null on first insertion, it will be replaced with Test value
CREATE TABLE Table2 (
Column1 VARCHAR(50) NOT NULL DEFAULT 'Test'
);

UNIQUE Constraint − Prevents duplicate record to be entered into the column.

The following example ensures Column1 cannot have duplicate values
CREATE TABLE Table3 (
Column1 VARCHAR(50) NOT NULL UNIQUE
);

Table level constraints;

PRIMARY Key − Uniquely identifies each row/record in a database table.

The following example ensures Column1 cannot have duplicate values and can be used as identifier of a row
CREATE TABLE Table4 (
Column1 VARCHAR(50) NOT NULL,
CONSTRAINT PK_Table4 PRIMARY KEY (Column1)
);

FOREIGN Key − Uniquely identifies a row/record in any of the given database table.

CHECK Constraint − The CHECK constraint ensures that all the values in a column satisfies certain conditions.

The following example ensure year value cannot be entered less than 2022
CREATE TABLE Table5 (
Year int CHECK (Year>=2022)
);


INDEX − Improves create and data retrieval performance.

Lets create a sample table work on it
CREATE TABLE Table6 (
FirstName VARCHAR(50) NOT NULL
);

Lets create the index on FirstName column
CREATE INDEX IX_FirstName ON Users (FirstName);

Drop Constraint

Lets create a table with constraint (Primary key)

CREATE TABLE Table7 (
Column1 VARCHAR(50) NOT NULL,
CONSTRAINT PK_Table7 PRIMARY KEY (Column1)
);

The following statement will remove PK_Table7 constraint from Table6
ALTER TABLE Table7 DROP CONSTRAINT PK_Table7;