Using Integrity Constraints

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:

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.

   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;

   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;

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:

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 Chapter 10 “SQL Statements A - D” 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:

   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 Title not available 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 Chapter 12 “SQL Statements S - Z” 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 Chapter 12 “SQL Statements S - Z” for detailed information on the SET DML ATOMICITY and SET CONSTRAINTS statements.