DBMS Lab # 5 SQL Constraints
DBMS Lab # 5 SQL Constraints
DBMS Lab # 5 SQL Constraints
SQL Constraints
Notice that each SELECT statement within the UNION must have the same number of
columns. The columns must also have similar data types. Also, the columns in each SELECT
statement must be in the same order.
Note: The UNION operator selects only distinct values by default. To allow duplicate values,
use UNION ALL.
PS: The column names in the result-set of a UNION are always equal to the column names in
the first SELECT statement in the UNION.
"Employees_Norway":
E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari
"Employees_USA":
E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen
Now we want to list all the different employees in Norway and USA.
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen
Note: This command cannot be used to list all employees in Norway and USA. In the
example above we have two employees with equal names, and only one of them will be
listed. The UNION command selects only distinct values.
Result
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen
SQL Constraints
Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or
after the table is created (with the ALTER TABLE statement).
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT
The next chapters will describe each constraint in details
SQL NOT NULL Constraint
By default, a table column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL values.
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.
The following SQL enforces the "P_Id" column and the "LastName" column to not accept
NULL values:
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY
constraint per table.
The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons"
table is created:
To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple
columns, use the following SQL syntax:
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons"
table is created:
Let's illustrate the foreign key with an example. Look at the following two tables:
1 77895 3
2 44678 3
3 22456 2
4 24562 1
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the
"Persons" table.The "P_Id" column in the "Persons" table is the PRIMARY KEY in the
"Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy link between
tables.
The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key
column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table
is created:
MySQL:
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY
constraint on multiple columns, use the following SQL syntax:
To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is
already created, use the following SQL:
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY
constraint on multiple columns, use the following SQL syntax:
MySQL:
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.
SQL CHECK Constraint on CREATE TABLE
The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons"
table is created. The CHECK constraint specifies that the column "P_Id" must only include
integers greater than 0.
My SQL:
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple
columns, use the following SQL syntax:
To create a CHECK constraint on the "P_Id" column when the table is already created, use
the following SQL:
MySQL / SQL Server / Oracle / MS Access:
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple
columns, use the following SQL syntax:
The default value will be added to all new records, if no other value is specified.
The following SQL creates a DEFAULT constraint on the "City" column when the "Persons"
table is created:
To create a DEFAULT constraint on the "City" column when the table is already created, use
the following SQL:
MySQL:
MySQL:
Indexes allow the database application to find data fast; without reading the whole table.
Indexes
An index can be created in a table to find data more quickly and efficiently.
The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because
the indexes also need an update). So you should only create indexes on columns (and tables)
that will be frequently searched against.
The SQL statement below creates an index named "PIndex" on the "LastName" column in the
"Persons" table:
If you want to create an index on a combination of columns, you can list the column names
within the parentheses, separated by commas: