ALLBASE/SQL Reference Manual (36216-90216)

144 Chapter4
Constraints, Procedures, and Rules
Using Integrity Constraints
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.
8 Members 'Martha Mitchell', 'Energetics', 1605 Valid
9 Events 'Energetics', 'advanced stretching',
'1986-12-04', '15:30:00', 'Martha
Mitchell'
Violates check constraint which
states that an event's date must
be later or the same as January 1,
1990
10 Events 'Energetics', 'advanced stretching',
'1990-01-01','15:30:00','Martha
Mitchell'
Valid
Order Table Values Result