SQL Constraints

• We can use SQL Constraints to specify the rules for the data in a table.
• Constraints are used to limit Which type of data must be stored in a table.
• SQL Constraints ensures the accuracy and reliability of the data in the table.
• Constraints make sure that there is no violation in term of transaction of the data.

Note: – Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

The following constraints are commonly used in SQL:

• SQL NOT NULL Constraint

• By default, a column can hold NULL values.
• The NOT NULL constraint enforces a column to NOT accept NULL values.
• This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

While Creating table

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar (255) NOT NULL,
    FirstName varchar (255) NOT NULL,
    Age int
);

While Altering table

ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;

• SQL UNIQUE Constraint
• The UNIQUE constraint ensures that all values in a column are different.
• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
• A PRIMARY KEY constraint automatically has a UNIQUE constraint.
• However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

While Creating New table

The following SQL creates a UNIQUE constraint on the “ID” column when the “Persons” table is created:

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar (255) NOT NULL,
    FirstName varchar (255),
    Age int
);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar (255) NOT NULL,
    FirstName varchar (255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID, LastName)
);

While Altering table

To create a UNIQUE constraint on the “ID” column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD UNIQUE (ID);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID, LastName);

• SQL PRIMARY KEY Constraint
• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values, and cannot contain NULL values.
• A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

While Creating New table

The following SQL creates a PRIMARY KEY on the “ID” column when the “Persons” table is created:

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar (255) NOT NULL,
    FirstName varchar (255),
    Age int
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

While Altering table

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

• SQL FOREIGN KEY Constraint
• The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
• A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
• The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
• Look at the following two tables:

Persons Table

Orders Table

• Notice that the “PersonID” column in the “Orders” table points to the “PersonID” column in the “Persons” table.
• The “PersonID” column in the “Persons” table is the PRIMARY KEY in the “Persons” table.
• The “PersonID” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.
• The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.

While Creating Table

The following SQL creates a FOREIGN KEY on the “PersonID” column when the “Orders” table is created:

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

While altering table

To create a FOREIGN KEY constraint on the “PersonID” column when the “Orders” table is already created, use the following SQL:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons (PersonID);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

• SQL CHECK Constraint
• The CHECK constraint is used to limit the value range that can be placed in a column.
• If you define a CHECK constraint on a column it will allow only certain values for this column.
• If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

While Creating table

The following SQL creates a CHECK constraint on the “Age” column when the “Persons” table is created. The CHECK constraint ensures that the age of a person must be 18, or older:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City=’Sandnes’)
);

While altering table

To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City=’Sandnes’);

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

• SQL DEFAULT Constraint
• The DEFAULT constraint is used to set a default value for a column.
• The default value will be added to all new records, if no other value is specified.

The following SQL sets a DEFAULT value for the “City” column when the “Persons” table is created:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT ‘Sandnes’
);

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

While altering table

To create a DEFAULT constraint on the “City” column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT ‘Sandnes’ FOR City;

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

1. SQL AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

For example,

The following SQL statement defines the “Personid” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

To insert a new record into the “Persons” table, we will NOT have to specify a value for the “Personid” column (a unique value will be added automatically):

• SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, …);

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, …);

For example,

The SQL statement below creates an index named “idx_lastname” on the “LastName” column in the “Persons” table:

CREATE INDEX idx_lastname
ON Persons (LastName);