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;