We begin this section by defining the valid identifiers in SQL and then proceeding with the SQL data types.
4.1.1 SQL Identifiers
SQL identifiers are used to identify items in the database; these include table names, view names and the attributes (columns). The characters that can be used consist of the upper-case letters A-Z, the lower case characters a-z, the digits 0-9, and the underscore (_) character.
The identifiers have the following restrictions:-
• It cannot be more than 128 characters
• It must start with a letter
• It cannot contain spaces
4.1
4.1.2 SQL Data Types
Table 4.1 below shows the ISO SQL data types. The data type character is referred to as string data type while exact numeric and approximate numeric data types are referred to as numeric data types.
Table 4.1: ISO SQL Data Types (Connoly and Begg, 2005) Data Type Declarations
boolean BOOLEAN
character CHAR VARCHAR
exact numeric DECIMAL INTEGER approximate numeric FLOAT REAL
date DATE Boolean data
Boolean or logical data consists of two distinct values TRUE or FALSE Character data
A character string can be defined as having fixed or variable length. To define fixed characters we declare it as CHAR.Variable length characters are declared as VARCHAR. For example in our Order Entry Database the EmpNo attribute in the Employee table has a fixed length of 5 characters. It is declared as:
EmpNo CHAR(5)
This column has a fixed length of 5 characters and we insert less than 5 characters the string is padded with blanks to make up for up to 5 characters. The column Name in the Employee relation has a variable length of up to 15 characters. It will be declared as:
Name VARCHAR(15)
This column has a variable length of 15 characters and if we enter less than 15 characters, only the characters entered are stored.
Exact Numeric Data
The exact numeric data type is used to define number with an exact representation. The decimal number representation is declared as DECIMAL (T, R). The T value indicates the total number of digits and the R value indicates the number of digits to the right of the decimal point.
For example the column Salary in the Employee relation can be declared as:
Salary DECIMAL (7,2) which can handle a value up to 99,999.99
For large positive or negative numbers, that is number without a decimal point we declare them as INTEGER.
For example the column QtyOnHand in the Product table can be declared as:
QtyOnHand INTEGER (4) Approximate Numeric Data
Real numbers used for scientific calculations which are of the approximate numeric data type are declared using FLOAT (p) where p is the precision parameter which indicates the number of significant digits. The precision in FLOAT can vary for up to 38 digits, whereas the precision in REAL numbers is fixed at 18 digits.
Date
The date data type is defined in columns such as the DOB (Date of Birth) column in the Employee table. This is declared in SQL as:-
DOB DATE
The default format can be specified for example DD-MON-YY) as used in our example.
INTEGRITY ENHANCEMENT FEATURE
In this section we consider the features provided by SQL for integrity control to ensure that the database is consistent. We examine four types of integrity constraints.
4.2.1 Required Data
A null is not a blank or zero and is used to represent data that is not available or not applicable (Connoly and Begg, 2005). However some columns must contain some valid data. For example every employee in the Employee relation must have a position, whether they are a salesperson or manager or a clerk. SQL provides the NOT NULL clause in the CREATE TABLE statement to enforce the
4.2
1. Describe SQL identifiers.
2. Identify the data types in SQL.
SELF-CHECK 4.1
required data constraint. To ensure that the column position of the Employee table cannot be null, we define the column as:
Position VARCHAR (15) NOT NULL
When NOT NULL is specified the Position column must have a data value.
4.2.2 Domain Constraints
Every column will have a set of allowable values, for instance the gender of the employee is either male (M) or female (F). Therefore the gender column has the domain ÂMÊ or ÂFÊ. In SQL we can use the CHECK clause to enforce this domain constraint on a column in the CREATE and ALTER TABLE statements. To ensure that the gender can only be specified as ÂMÊ or ÂFÊ we define the domain constraint in the Gender column as:
Gender CHAR NOT NULL CHECK (gender in (ÂMÊ, ÂFÊ))
4.2.3 Entity Integrity
Entity integrity is defined as the primary key value of a table must be unique and cannot be null. For example every EmpNo in the Employee relation is unique and identifies the employee. To support entity integrity SQL provides the PRIMARY KEY clause in the CREATE and ALTER TABLE statements. For example to declare EmpNo as the primary key we use the clause as:
PRIMARY KEY (EmpNo)
To define a composite primary key we specify the columns in the PRIMARY KEY clause. For example the OrderDetail table has two primary keys OrderNo and ProductNo.
To specify two primary keys we state it as:
PRIMARY KEY (OrderNo, ProductNo)
4.2.4 Referential Integrity
A foreign key value in a relation must match a candidate key value of the tuple in the referenced relation or the foreign key value can be null (Connoly and Begg, 2005). For example in the Order Entry Database the Product table has the foreign key SuppNo. You will notice that every entry of SuppNo in the rows of the Product table (child table) matches the SuppNo of the referenced table Supplier
(parent table). SQL supports referential integrity constraint with the FOREIGN KEY clause in the CREATE and ALTER TABLE statements. For example to specify the foreign key SuppNo of the Product table we state it as:
FOREIGN KEY (SuppNo) REFERENCES Supplier SQL rejects any attempts to:
• Delete a referenced row. What would happen to the related rows in the child table with the matching foreign key value if the referenced row in the parent table is deleted? Deleting a referenced row is specifying using the ON DELETE clause.
• Updating the primary key of a referenced row. What happens to related rows when the primary key of the referenced row in the parent table is updated?
(Mannino, 2007). Updating the primary key in the parent table is specified using the ON UPDATE clause
SQL supports four actions when a user attempts to delete a referenced row from the parent table and there are one or more matching rows in the child table. The same four actions can be applied when the primary key in the parent table is updated (Mannino, 2007, Connoly and Begg, 2005).
• CASCADE ă Perform the same action to related rows. For example, if a SuppNo in the Supplier table is deleted then the related rows in the Product table will be deleted in a cascading manner.
• SET NULL ă Delete the row from the parent table and set the foreign key value in the child table to NULL. For example if the SuppNo in the Supplier table is deleted then the related rows in the Product table will be set to NULL.
This will be valid only if the foreign key values do not have the NOT NULL clause specified.
• NO ACTION ă Reject the delete operation from the parent table. For example do not allow the SuppNo in the Supplier table to be deleted if there are related rows in the Product table.
• SET DEFAULT ă Delete the row from the parent table and set the foreign key in the child table to its default value. This is valid only if default values are set. For example if the SuppNo in the Supplier table is deleted then set SuppNo in the Product table to a default value such as „TENDERS SOON‰.
For example if we do not allow the rows in the Supplier table to be deleted if there are related rows in the Product table then we can specify it in SQL as:
FOREIGN KEY (SuppNo) REFERENCES Supplier ON DELETE NO ACTION
Similarly if for example, if a SuppNo in the Supplier table is updated then the related rows in the Product table need be updated in a cascading manner. We specify this SQL as:
FOREIGN KEY (SuppNo) REFERENCES Supplier ON UPDATE CASCADE
You must also consider the impact of referenced rows on insert operations. A referenced row (in the parent table) must be inserted before its related rows (in the child table). For example before inserting a row in the Product table, the referenced row in the Supplier must exist.