HP 3000 Manuals

Using Integrity Constraints [ ALLBASE/SQL Reference Manual ] MPE/iX 5.5 Documentation

ALLBASE/SQL Reference Manual

Using Integrity Constraints

Using integrity constraints helps to ensure that a database contains only
valid data. Integrity constraints provide a way to check data within the
database system rather than by coding elaborate validation checks within
application programs. An integrity constraint is either a unique
constraint, a referential constraint, or a check constraint. All of
these constraints are described in this section.

When a table is created, integrity constraints can be defined at the
column level or at the table level. A constraint can be placed on an
individual column (at the column or table level) or on a combination of
columns (at the table level).

Unique Constraints

A unique constraint requires that no two rows in a table contain the same
value in a given column or list of columns. You can create a unique
constraint at either the table level or the column level. Unique
constraints can be defined as either UNIQUE or PRIMARY KEY. The two types
of unique constraints differ in that if a PRIMARY KEY is placed on a
column or column list, the column name(s) can be omitted from the
referential constraint syntax in the definition of the referencing table.
A given column upon which a unique or primary constraint has been defined
need not be referenced by a referential constraint; but a referential
constraint can only refer to a column upon which a unique or primary key
constraint has been defined. Referential constraints are discussed
below.

Additionally, PRIMARY KEY can be specified only once per table.
Duplicate unique constraints are not allowed. Neither UNIQUE nor PRIMARY
KEY columns can contain null values--they must be defined as NOT NULL.

The following syntax is used to define a unique constraint on an
individual column or column list at the table level:

{UNIQUE } (ColumnName [,...]) [CONSTRAINT ConstraintID]
{PRIMARY KEY}
ConstraintID is the name of the constraint. It is not necessary to
name the constraint. If it is not named, ALLBASE/SQL names it
SQLCON_uniqueid, where uniqueid is a unique string. The constraint names
are maintained in the system catalog table SYSTEM.CONSTRAINT.

A column list cannot contain a column more than once. In the example
below, a constraint is placed on a column at the table level:

CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL,
UNIQUE (ClubName) CONSTRAINT ClubConstrnt)
IN RecFS;

The syntax for defining a unique constraint at the column level is part
of the column definition. NOT NULL and either UNIQUE or PRIMARY KEY are
included along with the other column parameters. In the example below,
one column is defined with a unique constraint:

CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL UNIQUE CONSTRAINT ClubConstrnt)
IN RecFS;

A table defined with a PRIMARY KEY followed by a column list is shown in
the section "Examples of Integrity Constraints."

Referential Constraints

A referential constraint requires that the value in a column or columns
of the referencing table, must either be null or match the value of a
column or columns of a unique constraint in the referenced table. To
establish a referential constraint, a unique or primary key constraint
must first be defined on the referenced table's column or column list and
then a referential constraint must be defined on the referencing table's
column or column list.

The Referenced Table.

The referenced table must contain a unique constraint created with either
a UNIQUE or PRIMARY KEY clause on a column or column list:

CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL
PRIMARY KEY CONSTRAINT Clubs_PK, -- column level constraint
ClubPhone SMALLINT,
Activity CHAR(18))
IN RecFS;

The referenced table must be created before the referencing table unless
the referenced and referencing tables are created within a CREATE SCHEMA
statement or if both the tables are created in the same transaction, the
SET REFERENTIAL CONSTRAINTS DEFERRED statement has been executed and is
still in effect.

The Referencing Table.

A referential constraint is placed on columns which are dependent on
other columns (in the referenced table). You can create a referential
constraint at either the table level or the column level. Referencing
columns need not be NOT NULL.

The following syntax is used to define a referential constraint at the
table level in the CREATE TABLE statement for a referencing table:

FOREIGN KEY (FKColumnName [,...]) REFERENCES RefTableName

[(RefColumnName [,...])] [CONSTRAINT ConstraintID]

FOREIGN KEY identifies a referencing column or column list. REFERENCES
identifies the referenced table and referenced column list. The order
and number of referencing columns in the FOREIGN KEY clause must be the
same as that of the referenced columns in the REFERENCES clause. The
referenced table cannot be a view.

The syntax for defining a referential constraint at the column level for
a referencing column is shown here:

REFERENCES RefTableName [(RefColumnName)] [CONSTRAINT ConstraintID]

Only one RefColumnName is possible.

Note in the following example that the table's column definitions and
table level constraints can be in any order within the parentheses and
are separated from each other with commas:

CREATE PUBLIC TABLE RecDB.Members
(MemberName CHAR(20) NOT NULL, column definition

Club CHAR(15) NOT NULL,
MemberPhone SMALLINT,
FOREIGN KEY (Club) table level

REFERENCES Clubs (ClubName)) referential constraint

IN RecFS;

If the REFERENCES clause does not specify a RefColumnName, then the table
definition referenced must contain a unique constraint that specifies
PRIMARY KEY. The primary key column list is the implicit RefColumnName
list. It must have the appropriate number of columns.

The owner of the table containing referencing columns must have the
REFERENCES authority on referenced columns, have OWNER authority on the
referenced table, or have DBA authority, for the duration of the
referential constraint.

Check Constraints

A check constraint specifies a condition which must be upheld for an
insert or update to be successfully performed on a table or view. A
table check constraint must not be false for any row of the table on
which it is defined. A view check constraint must be true for the
condition in the SELECT statement that defines the view.

A table check constraint is defined in the CREATE TABLE or ALTER TABLE
statement with the following syntax:

CHECK (SearchCondition) [CONSTRAINT ConstraintID]

If a check constraint is added to an existing table, data already in the
table is verified to ensure that the check constraint is satisfied. A
constraint error occurs if the constraint is not satisfied; the ALTER
TABLE statement adding the constraint fails.

The check is also performed when the INSERT or UPDATE statement is
executed. A DELETE statement never causes a check constraint error.

The check search condition must not contain a subquery, aggregate
function, TID function, local variable, procedure parameter, dynamic
parameter, current function, USER, or host variable. The search
condition expression also cannot contain a LONG column unless it is
within a long column function. When adding a new column, the columns
specified in the search condition must be defined in the same CREATE
TABLE or ALTER TABLE ADD COLUMN statement. For the ALTER TABLE ADD
COLUMN statements, the check constraint can only be specified for the
column being added. When adding a constraint, columns specified in the
check constraint search condition must already exist in the table.

The search condition is a boolean expression which must not be false for
a table check constraint to be satisfied. If any value specified in the
search condition expression is NULL, the result of the expression may be
the boolean unknown value rather than true or false. The check
constraint is satisfied if the result is true or unknown.

For example, consider the following check constraint:

CHECK (NumParts > 5)

If NumParts is 5, the result is false and the check is not satisfied. If
NumParts is 10, the result is true and the check constraint for this row
is satisfied. If NumParts is NULL, the result is unknown and the check
constraint is also satisfied for this row.

A table check constraint can be defined at a column level or a table
level. A check constraint defined on a column is specified before the
comma that ends the column definition as shown below. A table constraint
can be placed anywhere--before, after, or among the column descriptions.
These rules apply for columns defined with either the CREATE TABLE or
ALTER TABLE statements.

For example, a column level check constraint on the Date column is
defined as follows:

CREATE PUBLIC TABLE RecDB.Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE DEFAULT CURRENT_DATE No comma here

(CHECK (Date >= '1990-01-01'),
Constraint Check_No_Old_Events),

Time TIME,
Coordinator CHAR(20),
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members (MemberName, Club)
CONSTRAINT Events_FK)
IN RecFS;

However, the same constraint defined at the table level is defined as
follows:

CREATE PUBLIC TABLE RecDB.Events
CHECK (Date >= '1990-01-01') Check Constraint

CONSTRAINT Check_No_Old_Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE DEFAULT CURRENT_DATE,
Time TIME,
Coordinator CHAR(20),
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members (MemberName, Club)
CONSTRAINT Events_FK)
IN RecFS;

This table level constraint could also be defined after the Date or Time
column, or at any point in the parenthesized list. There is one
difference between table and column level check constraints: a column
level check constraint must reference only the column on which it is
defined.

A check constraint that references more than one column must be defined
at the table level. For example, the constraint CHECK (Date >=
'1990-01-01' AND Time > '00:00.000') must be defined at the table level
because both the Date and Time columns are specified in the check
constraint.

A view check constraint is defined with the CREATE VIEW statement using
the following syntax at the end of the view definition:

WITH CHECK OPTION [CONSTRAINT ConstraintID]

The conditions of the SELECT statement defining the view become the view
check constraint search conditions when the WITH CHECK OPTION clause is
specified. A view can have only one WITH CHECK OPTION. This check
constraint checks all of the conditions which are included in the SELECT
statement. These SELECT statement conditions serve two purposes. First,
they originally define the view. They also define the conditions of the
check constraint that is applied when the underlying base table is
modified through the view. When a table is modified through a view, the
view check constraint is checked along with any table constraints. The
view check constraint must be true (not unknown) to ensure that all
changes made through a view can still be displayed. All underlying views
are also checked, whether or not they are defined with check options.
Unique and referential constraints cannot be defined on views.

See the "SQL Statements" chapter for the check constraint syntax, within
the syntax of CREATE TABLE, ALTER TABLE, or CREATE VIEW statements.

Examples of Integrity Constraints

The schema example in this section shows the constraints among three
tables: Clubs, Members, and Events. The tables are created as PUBLIC so
as to be accessible to any user or program that can start a DBE session.

Constraints are placed on the tables to ensure that:

1. Events are coordinated by club members who are listed in the
Members table

2. Clubs sponsoring the events are listed in the Clubs table

3. Events cannot be scheduled earlier than the current date.

CREATE PUBLIC TABLE RecDB.Clubs
(ClubName CHAR(15) NOT NULL
PRIMARY KEY CONSTRAINT Clubs_PK,
ClubPhone SMALLINT,
Activity CHAR(18))
IN RecFS;

CREATE PUBLIC TABLE RecDB.Members
(MemberName CHAR(20) NOT NULL,
Club CHAR(15) NOT NULL,
MemberPhone SMALLINT,
PRIMARY KEY (MemberName, Club) CONSTRAINT Members_PK,
FOREIGN KEY (Club) REFERENCES RecDB.Clubs
CONSTRAINT Members_FK)
IN RecFS;

CREATE PUBLIC TABLE RecDB.Events
(Event CHAR(30),
Coordinator CHAR(20),
SponsorClub CHAR(15),
Date DATE DEFAULT CURRENT_DATE,
CHECK (Date >= '1990-01-01'),
Time TIME,
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES RecDB.Members
CONSTRAINT Events_FK)
IN RecFS;

Note that updating the Members table before the Clubs table could cause a
referential constraint error when error checking is at statement level.
The RecDB.Members.Club column references the RecDB.Clubs.ClubName column
which is not yet updated. However, if you deferred referential checking
to the end of the transaction, no error would occur. A value could then
be inserted into the RecDB.Clubs.ClubName column that would resolve the
reference. When a COMMIT WORK statement is executed, no constraint
errors will exist.

The illustration in Figure 4-1 shows the referential constraints
based on this sample schema. The arrows point to the columns with unique
constraints.

Figure 4-1. Referential Constraints in a Set of Tables

The Events table contains information about events. The combination of
values in the Coordinator and SponsorClub columns of the Events table
must be either be null or match the combination of values in the
MemberName and Club columns of the Members table, as shown by the
Events_FK constraint.

The Members table contains the names of members and clubs. A member can
be in more than one club. For every Coordinator/SponsorClub pair of
values exists a corresponding MemberName/Club match.

The Clubs table contains information about clubs. For every club entry
in the Members table, a corresponding entry must exist in the Clubs
table, as shown by the Members_FK constraint.

Inserting Rows in Tables Having Constraints

There are two ways you can insert data in tables having constraints. You
can insert values in referenced columns before inserting values in
referencing columns, or you can defer constraint error checking in a
transaction until all constraints referring to each other have been
resolved.

With the first method, using the tables defined in the previous example,
the Clubs data should be loaded first, then the Members data, because the
MemberName column is dependent on the ClubName column. The Events table
should be loaded last as the Coordinator and SponsorClub columns are
dependent on the MemberName and Club columns of the Members Table.

If the Clubs, Members, and Events tables were empty and you attempted to
insert the values in the order shown below, you would receive the
following corresponding results:

Values cannot be inserted into Members or Events without the references
being satisfied. To insert rows, either NULLs must be inserted and then
the tuples updated after the referenced rows are inserted, or the
referenced rows must be inserted first. Note that a NULL cannot be
inserted into the Members_FK column Club because that column also
participates in Members_PK--and therefore was declared NOT NULL.

With the second method, you can also perform these inserts in one
transaction, deferring constraint checking to the end of the transaction.
While you are inserting data, constraint error violations are not
reported because they will be resolved by the time the COMMIT WORK
statement is executed. Use a SET CONSTRAINTS statement after a BEGIN
WORK statement to defer constraint checking, as follows:

BEGIN WORK

SET REFERENTIAL CONSTRAINTS DEFERRED

Modify all tables that refer to each other.

COMMIT WORK

You can issue the SET CONSTRAINTS statement to defer several types of
operation at one time. Refer to the "SQL Statements" chapter for the
syntax of the SET CONSTRAINTS statement.

How Constraints are Enforced

Constraints are controlled and checked by ALLBASE/SQL once they are
defined. Once a constraint is placed on a column, ALLBASE/SQL performs
the necessary checks each time a value is inserted, altered, or deleted.
By default, integrity constraints are enforced on a statement level
basis. That is, if an integrity constraint is not satisfied after the
execution of an INSERT, UPDATE, or DELETE statement, then the statement
has no effect on the database and an error message is generated.

You can, however, use the SET CONSTRAINTS DEFERRED statement to defer
constraint enforcement until either the end of a transaction or a SET
CONSTRAINTS IMMEDIATE statement is encountered. Deferred constraint
enforcement avoids concern over the order of inserting or updating when a
foreign key and primary key exist in the same table or different tables.
The table can be modified without constraint violations being reported
until either the end of a transaction or SET CONSTRAINTS IMMEDIATE
statement is encountered. While a constraint check is deferred, you are
responsible for ensuring that data placed in the database is free of
constraint errors.

In addition, you can temporarily use the SET DML ATOMICITY statement to
set the DML error checking level to row level. However, you must handle
partially processed statements yourself, as statements that get errors
will not undo their partial execution.

Constraint error checking is part of general error checking but you can
override the checking level by setting constraint checking to deferred.
However, when you set constraint checking back to IMMEDIATE, the level of
constraint checking returns to the current level specified by the most
recent SET DML ATOMICITY statement.

Refer to the "SQL Statements" chapter for detailed information on the SET
DML ATOMICITY and SET CONSTRAINTS statements.

MPE/iX 5.5 Documentation