MySQL – Constraints

MySQL – Constraints

Tested on MySQL 5.5

Constraint: NOT NULL

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Constraint: UNIQUE

The UNIQUE constraint uniquely identifies each record in a database table
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

/* Modify an existing table */
ALTER TABLE Persons
ADD UNIQUE (P_Id)

/* Modify an existing table */
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

/* Drop constraint */
ALTER TABLE Persons
DROP INDEX uc_PersonID

Constraint: PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

/* Modify an existing table */
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

/* Modify an existing table */
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

/* Drop constraint */
ALTER TABLE Persons
DROP PRIMARY KEY

Constraint: FOREIGN KEY

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
It is used to prevent actions that would destroy links between tables.

Example:
“Persons” table: P_Id LastName FirstName Address City
“Orders” table : O_Id OrderNo P_Id

Note that the “P_Id” column in the “Orders” table points to the “P_Id” column in the “Persons” table.

/* Create new table */
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

/* Create new table */
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

/* Modify an existing table */
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

/* Modify an existing table */
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

/* Drop constraint */
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

Constraint: CHECK

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 single column it allows 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.

/* Create new table */
/* "P_Id" must only include integers greater than 0 */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

/* Modify an existing table */
ALTER TABLE Persons
ADD CHECK (P_Id>0)

/* Modify an existing table */
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

/* Drop constraint */
ALTER TABLE Persons
DROP CHECK chk_Person

Constraint: DEFAULT

The default value will be added to all new records, if no other value is specified.

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

/* Create new table */
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

/* Modify an existing table */
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

/* Drop constraint */
ALTER TABLE Persons
ALTER City DROP DEFAULT