Integrity Constraints

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. There are many types of integrity constraints that play a role in referential integrity (RI).

• Primary Key Constraints

• Unique Constraints

• Foreign Key Constraints

• NOT NULL Constraints

  
  

  Primary Key Constraints

  Primary key is the term used to identify one or more columns in a table that make a row of data unique. Although the primary key typically consists of one column in a table, more than one column can comprise the primary key. For example, either the employee's Social Security number or an assigned employee identification number is the logical primary key for an employee table. The objective is for every record to have a unique primary key or value for the employee's identification number. Because there is probably no need to have more than one record for each employee in an employee table, the employee identification number makes a logical primary key. The primary key is assigned at table creation.

  The following example identifies the EMP_ID column as the PRIMARY KEY for the EMPLOYEES table:

  CREATE TABLE EMPLOYEE_TBL
  (EMP_ID        CHAR(9)        NOT NULL PRIMARY KEY,
  EMP_NAME       VARCHAR2(40)   NOT NULL,
  EMP_ST_ADDR    VARCHAR2(20)   NOT NULL,
  EMP_CITY       VARCHAR2(15)   NOT NULL,
  EMP_ST         CHAR(2)        NOT NULL,
  EMP_ZIP        NUMBER(5)      NOT NULL,
  EMP_PHONE      NUMBER(10)     NULL,
  EMP_PAGER      NUMBER(10)     NULL);
  
  
  Unique Constraints
  A unique column constraint in a table is similar to a primary key in that the value in that column for every row of data in the table must have a unique value. While a primary key constraint is placed on one column, you can place a unique constraint on another column even though it is not actually for use as the primary key.
  Study the following example:
  CREATE TABLE EMPLOYEE_TBL (EMP_ID        CHAR(9)        NOT NULL     PRIMARY KEY, EMP_NAME       VARCHAR2(40)   NOT NULL, EMP_ST_ADDR    VARCHAR2(20)   NOT NULL, EMP_CITY       VARCHAR2(15)   NOT NULL, EMP_ST         CHAR(2)        NOT NULL, EMP_ZIP        NUMBER(5)      NOT NULL, EMP_PHONE      NUMBER(10)     NULL         UNIQUE, EMP_PAGER      NUMBER(10)     NULL);
  
  Foreign Key Constraints
  A foreign key is a column in a child table that references a primary key in the parent table. A foreign key constraint is the main mechanism used to enforce referential integrity between tables in a relational database. A column defined as a foreign key is used to reference a column defined as a primary key in another table.
  Study the creation of the foreign key in the following example:
  CREATE TABLE EMPLOYEE_PAY_TBL (EMP_ID           CHAR(9)        NOT NULL, POSITION           VARCHAR2(15)   NOT NULL, DATE_HIRE          DATE           NULL, PAY_RATE           NUMBER(4,2)    NOT NULL, DATE_LAST_RAISE    DATE           NULL, CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID));
  The EMP_ID column in this example has been designated as the foreign key for the EMPLOYEE_PAY_TBL table. This foreign key, as you can see, references the EMP_ID co-lumn in the EMPLOYEE_TBL table. This foreign key ensures that for every EMP_ID in the EMPLOYEE_PAY_TBL, there is a corresponding EMP_ID in the EMPLOYEE_TBL. This is called a parent/child relationship. The parent table is the EMPLOYEE_TBL table, and the child table is theEMPLOYEE_PAY_TBL table. Study Table  for a better understanding of the parent table/child table relationship.
   
  Figure 3.2:   The parent/child table relationship.
  In this figure, the EMP_ID column in the child table references the EMP_ID column in the parent table. In order for a value to be inserted for EMP_ID in the child table, there must first exist a value for EMP_ID in the parent table. Likewise, for a value to be removed for EMP_ID in the parent table, all corresponding values for EMP_ID must first be removed from the child table. This is how referential integrity works.
  A foreign key can be added to a table using the ALTER TABLE command, as shown in the following example:
  ALTER TABLE EMPLOYEE_PAY_TBL ADD CONSTRAINT ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID);
  
  
  The options available with the ALTER TABLE command differ among different implementations of SQL, particularly when dealing with constraints.In addition, the actual use and definitions of constraints also vary, but the concept of referential integrity should be the same with all relational databases.
  NOT NULL Constraints
  The keywords NULL and NOT NULL listed on the same line as each column and after the data type. NOT NULL is a constraint that you can place on a table's column. This constraint disallows the entrance of NULLvalues into a column; in other words, data is required in a NOT NULL column for each row of data in the table. NULL is generally the default for a column if NOT NULL is not specified, allowing NULL values in a column.
  

What Are Database Objects?

A database object is any defined object in a database that is used to store or reference data. Some examples of database objects include tables, views, clusters, sequences, indexes, and synonyms.

What Is a Schema?

A schema is a collection of database objects associated with one particular database username. This username is called the schema owner, or the owner of the related group of objects. You may have one or multiple schemas in a database. Basically, any user who creates an object has just created his or her own schema. A schema can consist of a single table and has no limits to the number of objects that it may contain, unless restricted by a specific database implementation.

Say you have been issued a database username and password by the database administrator. Your username is USER1. Suppose you log on to the database and then create a table called EMPLOYEE_TBL. Your table's actual name isUSER1.EMPLOYEE_TBL. The schema name for that table is USER1, which is also the owner of that table. You have just created the first table of a schema.

The good thing about schemas is that when you access a table that you own (in your own schema), you do not have to refer to the schema name. For instance, you could refer to your table as either one of the following:

EMPLOYEE_TBL USER1.EMPLOYEE_TBL

The first option is preferred because it requires fewer keystrokes. If another user were to query one of your tables, the user would have to specify the schema, as follows:

USER1.EMPLOYEE_TBL

Schemas in a database.

There are, in Fig, two user accounts in the database that own tables: USER1 and USER2. Each user account has its own schema. Some examples for how the two users can access their own tables and tables owned by the other user follow:

USER1 accesses own table1:	TABLE1
USER1 accesses own test:	TEST
USER1 accesses USER2's table10:	USER2.TABLE10
USER1 accesses USER2's test:	USER2.TEST

Both users have a table called TEST. Tables can have the same names in a database as long as they belong to different schemas. If you look at it this way, table names are always unique in a database, because the schema owner is actually part of the table name. For instance, USER1.TEST is different than USER2.TEST. If you do not specify a schema with the table name when accessing tables in a database, the database server looks for a table that you own by default. That is, if USER1 tries to access TEST, the database server looks for a USER1-owned table named TEST before it looks for other objects owned by USER1, such as synonyms to tables in another schema.

What Is Data?

Data is a collection of information stored in a database as one of several different data types. Data includes names, numbers, dollar amounts, text, graphics, decimals, figures, calculations, summarization, and just about anything else you can possibly imagine. Data can be stored in uppercase, lowercase, or mixed case. Data can be manipulated or changed; most data does not remain static for its lifetime.

Basic Data Types

The following sections discuss the basic data types supported by ANSI SQL. Data types are characteristics of the data itself, whose attributes are placed on fields within a table. For example, you can specify that a field must contain numeric values, disallowing the entering of alphanumeric strings. After all, you would not want to enter alphabetic characters in a field for a dollar amount.

Note

Every implementation of SQL seems to have its own specific data types. The use of implementation-specific data types is necessary to support the philosophy of each implementation on how to handle the storage of data. However, the basics are the same among all implementations.

The very basic data types, as with most other languages, are

• Character strings

• Numeric strings

• Date and time values

Fixed-Length Characters

Constant characters, those strings that always have the same length, are stored using a fixed-length data type. The following is the standard for an SQL fixed-length character:

CHARACTER(n)

n represents a number identifying the allocated, or maximum length, of the particular field with this definition.

Some implementations of SQL use the CHAR data type to store fixed-length data. Alphanumeric data can be stored in this data type. An example of a constant length data type would be for a state abbreviation because all state abbreviations are two characters.

Spaces are normally used to fill extra spots when using a fixed-length data type; if a field's length was set to 10 and data entered filled only five places, the remaining five spaces are recorded as spaces. The padding of spaces ensures that each value in a field is a fixed length.

Warning

Be careful not to use a fixed-length data type for fields that may contain varying-length values, such as an individual's name. If you use the fixed-length data type inappropriately, problems such as the waste of available space and the inability to make accurate comparisons between data will eventually be encountered.

Variable Characters

SQL supports the use of varying-length strings,strings whose length is not constant for all data. The following is the standard for an SQL varying-length character:

CHARACTER VARYING(n)

n represents a number identifying the allocated, or maximum length, of the particular field with this definition.

Common data types for variable-length character values are the VARCHAR and VARCHAR2 data types. VARCHAR is the ANSI standard, which Microsoft SQL Server uses; VARCHAR2 is used by Oracle and should be used in Oracle, because VARCHAR's usage in the future may change. The data stored can be alphanumeric.

Remember that fixed-length data types typically pad spaces to fill in allocated places not used by the field. The varying-length data type does not work this way. For instance, if the allocated length of a varying-length field is 10, and a string of five characters is entered, the total length of that particular value is only 5. Spaces are not used to fill unused places in a column.

Tip

Always use the varying-length data type for non-constant character strings to save database space.

Numeric Values

Numeric values are stored in fields that are defined as some type of number, typically referred to as NUMBER, INTEGER, REAL, DECIMAL, and so on.

The following are the standards for SQL numeric values:

BIT(n)

BIT VARYING(n)

DECIMAL(p,s)

INTEGER

SMALLINT

FLOAT(p)

REAL(s)

DOUBLE PRECISION(P)

p represents a number identifying the allocated, or maximum length, of the particular field for each appropriate definition.

s is a number to the right of the decimal point, such as 34.ss.

A common numeric data type in SQL implementations is NUMBER, which accommodates the direction for numeric values provided by ANSI. Numeric values can be stored as zero, positive, negative, fixed, and floating-point numbers. The following is an example using NUMBER:

NUMBER(5)

This example restricts the maximum value entered in a particular field to 99999.

Decimal Values

Decimal values are numeric values that include the use of a decimal point. The standard for a decimal in SQL follows, where the p is the precision and the s is the decimal's scale:

DECIMAL(p,s)

The precision is the total length of the numeric value. In a numeric defined DECIMAL(4,2), the precision is 4, which is the total length allocated for a numeric value.

The scale is the number of digits to the right of the decimal point. The scale is 2 in the previous DECIMAL(4,2) example.

34.33 inserted into a DECIMAL(3,1) is typically rounded to 34.3.

If a numeric value was defined as the following data type, the maximum value allowed would be 99.99:

DECIMAL(4,2)

The precision is 4, which represents the total length allocated for an associated value. The scale is 2, which represents the number of places, or bytes, reserved to the right side of the decimal point. The decimal point itself does not count as a character.

Allowed values for a column defined as DECIMAL(4,2) include the following:

12

12.4

12.44

12.449

The last numeric value, 12.449, is rounded off to 12.45 upon input into the column.

Integers

An integer is a numeric value that does not contain a decimal, only whole numbers (both positive and negative).

Valid integers include the following:

1

0

-1

99

-99

199

Floating-Point Decimals

Floating-point decimals are decimal values whose precision and scale are variable lengths and virtually without limit. Any precision and scale is acceptable. The REAL data type designates a column with single-precision, floating-point numbers. The DOUBLE PRECISION data type designates a column that contains double-precision, floating-point numbers. To be considered a single-precision floating point, the precision must be between 1 and 21 inclusive. To be considered a double-precision floating point, the precision must be between 22 and 53 inclusive. The following are examples of the FLOAT data type:

FLOAT

FLOAT(15)

FLOAT(50)

Dates and Time

Date and time data types are quite obviously used to keep track of information concerning dates and time. Standard SQL supports what are called DATETIME data types, which include the following specific data types:

DATE

TIME

INTERVAL

TIMESTAMP

The elements of a DATETIME data type consist of the following:

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

Note

The SECOND element can also be broken down to fractions of a second. The range is from 00.000 to 61.999, although some implementations of SQL may not support this range.

Be aware that each implementation of SQL may have its own customized data type for dates and times. The previous data types and elements are standards to which each SQL vendor should adhere, but be advised that most implementations have their own data type for date values, varying in both appearance and the way date information is actually stored internally.

A length is not normally specified for a date data type. Later in this hour, you learn more about dates, how date information is stored in some implementations, how to manipulate dates and times using conversion functions, and study practical examples of how dates and time are used in the real world.

Literal Strings

A literal string is a series of characters, such as a name or a phone number, that is explicitly specified by a user or program. Literal strings consist of data with the same attributes as the previously discussed data types, but the value of the string is known; the value of a column itself is usually unknown, because there is typically a different value for a column associated with each row of data in a table.

You do not actually specify data types with literal strings—you simply specify the string. Some examples of literal strings follow:

'Hello'

45000

"45000"

3.14

'November 1, 1997'

The alphanumeric strings are enclosed by single quotation marks, whereas the number value 45000 is not. Also notice that the second numeric value of 45000 is enclosed by quotation marks. Generally speaking, character strings require quotation marks, whereas numeric strings don't. You see later how literal strings are used with database queries.

NULL Data Types

a NULL value isa missing value or a column in a row of data that has not been assigned a value. NULL values are used in nearly all parts of SQL, including the creation of tables, search conditions for queries, and even in literal strings.

The following are two methods for referencing a NULL value:

• NULL (the keyword NULL itself)

• '' (single quotation marks with nothing in between)

The following does not represent a NULL value, but a literal string containing the characters N-U-L-L:

'NULL'

BOOLEAN Values

A BOOLEAN value is a value of either TRUE, FALSE, or NULL. BOOLEAN values are used to make data comparisons. For example, when criteria are specified for a query, each condition evaluates to either a TRUE, FALSE, or NULL. If theBOOLEAN value of TRUE is returned by all conditions in a query, data is returned. If a BOOLEAN value of FALSE or NULL is returned, data may not be returned.

Consider the following example:

WHERE NAME = 'SMITH'

This line might be a condition found in a query. The condition is evaluated for every row of data in the table that is being queried. If the value of NAME is SMITH for a row of data in the table, the condition returns the value TRUE, thereby returning the data associated with that record.

User-Defined Types

A user-defined type is a data type that is defined by the user. User-defined types allow users to customize their own data types based on existing data types. The CREATE TYPE statement is used to create a user-defined type.

For example, you can create a type as follows:

CREATE TYPE PERSON AS OBJECT (NAME       VARCHAR2(30),  SSN    VARCHAR2(9));

You can reference your user-defined type as follows:

CREATE TABLE EMP_PAY (EMPLOYEE   PERSON,  SALARY     NUMBER(10,2),  HIRE_DATE      DATE);

Notice that the data type referenced for the first column EMPLOYEE is PERSON. PERSON is the user-defined type you created in the first example.

Domains

A domain is a set of valid data types that can be used. A domain is associated with a data type, so that only certain data is accepted. After a domain is created, you can add constraints to the domain. The domain is used like the user-defined type.

You can create a domain as follows:

CREATE DOMAIN MONEY_D AS NUMBER(8,2);

You can add constraints to your domain as follows:

ALTER DOMAIN MONEY_D  ADD CONSTRAINT MONEY_CON1  CHECK (VALUE > 5);

You can reference the domain as follows:

CREATE TABLE EMP_PAY (EMP_ID        NUMBER(9),  EMP_NAME      VARCHAR2(30),  PAY_RATE      MONEY_D);

Types of SQL Commands

The basic categories of commands used in SQL to perform various functions. These functions include building database objects, manipulating objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access, and overall database administration.

The main categories are

• DDL (Data Definition Language)

• DML(Data Manipulation Language)

• DQL (Data Query Language)

• DCL (Data Control Language)

• Data administration commands

• Transactional control commands

  
  

  Defining Database Structures (DDL)

  Data Definition Language, DDL, is the part of SQL that allows a database user to create and restructure database objects, such as the creation or the deletion of a table.

  The main DDL commands discussed during following hours include the following:

  CREATE TABLE

  ALTER TABLE

  DROP TABLE

  CREATE INDEX

  ALTER INDEX

  DROP INDEX

  
  

  Manipulating Data (DML)

  Data Manipulation Language, DML, is the part of SQL used to manipulate data within objects of a relational database.

  There are three basic DML commands:

  INSERT

  UPDATE

  DELETE

  
  

  Selecting Data (DQL)

  Data Query Language (DQL) is the most concentrated focus of SQL for a relational database user. The command is as follows:

  SELECT

  
  

  
  

  Data Control Language (DCL)

  Data control commands in SQL allow you to control access to data within the database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows:

  ALTER PASSWORD

  GRANT

  REVOKE

  CREATE SYNONYM

  
  

  Data Administration Commands

  Data administration commands allow the user to perform audits and perform analyses on operations within the database. They can also be used to help analyze system performance. Two general data administration commands are as follows:

  START AUDIT

  STOP AUDIT

  
  

  Transactional Control Commands

  In addition to the previously introduced categories of commands, there are commands that allow the user to manage database transactions.

  • COMMIT Used to save database transactions

  • ROLLBACK Used to undo database transactions

  • SAVEPOINT Creates points within groups of transactions in which to ROLLBACK

  • SET TRANSACTION Places a name on a transaction