Database Systems
Lecture # 8
Database
Constraints.
Data Integrity
Itis important that column data adhere to a predefined
set of rules, as determined by the database administrator
or application developer.
Types of Data Integrity
Domain Integrity
Data types help determine what values are valid for a particular column. This
is known as domain integrity. The domain is simply the set of valid values
for a particular Column.
Entity Integrity
This means that you can uniquely identify every row in a table. You can do
this with a unique index or with declarative integrity (primary-key or unique
constraints)
Referential Integrity
Referential integrity refers to the maintenance of relationships between data
rows in multiple tables.
Constraints
Types of constraints
– NOT NULL - Ensures that a column cannot have a NULL value
– UNIQUE - Ensures that all values in a column are different
– PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table
– FOREIGN KEY - Prevents actions that would destroy links between tables
– CHECK - Ensures that the values in a column satisfies a specific condition
– DEFAULT - Sets a default value for a column if no value is specified
– CREATE INDEX - Used to create and retrieve data from the database very
quickly
1. MySQL 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.
NOT NULL on CREATE TABLE
NOT NULL on ALTER TABLE
UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are
different but allows some values to be null.
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
UNIQUE Constraint
UNIQUE constraint on CREATE TABLE
To name a UNIQUE constraint, and to define a UNIQUE constraint on
multiple columns:
UNIQUE Constraint
UNIQUE constraint on ALTER TABLE
To name a UNIQUE constraint, and to define a UNIQUE constraint on
multiple columns, use the following SQL syntax:
Note: We can see the index name by using query:
SHOW index from <table_name>;
UNIQUE Constraint
DROP a UNIQUE constraint
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).
PRIMARY KEY Constraint
PRIMARY KEY on CREATE TABLE
PRIMARY KEY Constraint
Can we have multiple primary keys in a single table ?
Let’s try by using the following query:
PRIMARY KEY Constraint
Can we have multiple primary keys in a single table ?
You cannot have more than one primary key in a table.
A table can only have one PRIMARY KEY constraint.
PRIMARY KEY Constraint
Defining a PRIMARY KEY constraint on multiple columns
PRIMARY KEY on ALTER TABLE
DROP a PRIMARY KEY Constraint
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.
FOREIGN KEY Constraint
FOREIGN KEY on CREATE TABLE
FOREIGN KEY Constraint
FOREIGN KEY on CREATE TABLE and Naming the FOREIGN KEY
FOREIGN KEY Constraint
CRETAE table without FOREIGN KEY
FOREIGN KEY on ALTER TABLE
FOREIGN KEY Constraint
Find the name of Foreign key by using the following query:
By using the above query, we will get the default name of foreign key.
Then by using that name, we can drop the foreign key
DROP a FOREIGN KEY Constraint
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.
CHECK Constraint
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:
CHECK Constraint
To allow naming of a CHECK constraint, and for
defining a CHECK constraint on multiple columns,
use the following SQL syntax:
CHECK Constraint
CHECK on ALTER TABLE
DROP a CHECK Constraint
To check the default name of constraint write the
query:
Show create table table_name;
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:
DEFAULT Constraint
DEFAULT Constraint
SQL DEFAULT on ALTER TABLE
DROP a DEFAULT Constraint
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.
CREATE INDEX Statement
If you want to create an index on a combination of
columns, you can list the column names within the
parentheses, separated by commas:
DROP INDEX Statement
MySQL 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
MySQL AUTO INCREMENT Field
To let the AUTO_INCREMENT sequence start with
another value, use the following SQL statement:
MySQL AUTO INCREMENT Field
Set the starting and increment value.
MySQL AUTO INCREMENT Field
DROP the auto increment
MySQL Date Data Types
MySQL comes with the following data types for storing a
date or a date/time value in the database:
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
