Craig Mullins did an excellent article called “A View Review” on the various uses for VIEWS which applies to SQL, in general, as well as DB2. But he missed another use that has become possible in SQL products as they add more Standard SQL features. Consider a schema for a chain of stores that has three tables, thus:

CREATE TABLE Stores
(store_nbr INTEGER NOT NULL PRIMARY KEY,
store_name CHAR(35) NOT NULL,
..);
 
CREATE TABLE Personnel
(ssn CHAR(9) NOT NULL PRIMARY KEY,
last_name CHAR(15) NOT NULL,
first_name CHAR(15) NOT NULL,
..);

The first two explain themselves. The third table shows the relationship between stores and personnel, namely who is assigned to what job at which store and when this happened. Thus:

CREATE TABLE JobAssignments
(store_nbr INTEGER NOT NULL
   	 REFERENCES Stores (store_nbr)
   	 ON UPDATE CASCADE
   	 ON DELETE CASCADE,
 ssn CHAR(9) NOT NULL PRIMARY KEY
        REFERENCES Personnel( ssn)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
 start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
 end_date TIMESTAMP CHECK (start_date <= end_date),
 job_type INTEGER DEFAULT 0 NOT NULL
        CHECK (job_type BETWEEN 0 AND 99),
 PRIMARY KEY (store_nbr, ssn, start_date));

Let's invent some job_type codes, such as 0 = “unassigned", 1 = “stockboy”, etc., until we get to 99 = "Store Manager” and we have a rule that each store has at most one manager. In Full Standard SQL you could write a constraint like this:

CHECK (1 <= ALL (SELECT    COUNT(*)
   			     FROM JobAssignments
   			WHERE job_type = 99
   			GROUP BY store_nbr))

This is actually a bit subtler than it looks. If you change the <= to =, then the stores must have exactly one manager if it has any employees at all.

But most SQL product still do not allow CHECK() constraints which apply to the table as a whole, nor do they support the scheme level CREATE ASSERTION statement.

So, how to do this? You might use a trigger, which will involve proprietary, procedural code. In spite of the SQL/PSM Standard, most vendors implement very different trigger models and use their proprietary 4GL language in the body of the trigger.

We need a set TRIGGERs that validates the state of the table after each INSERT and UPDATE operation. If we DELETE an employee, this will not create more than one manager per store. The skeleton for these triggers would be something like this.

CREATE TRIGGER CheckManagers
AFTER UPDATE ON JobAssignments -- same for INSERT
IF 1 <= ALL (SELECT COUNT(*)
                        FROM JobAssignments
                   WHERE job_type = 99
                   GROUP BY store_nbr)
THEN ROLLBACK;
ELSE COMMIT;
END IF;

But being a fanatic, I want a pure SQL solution that is declarative within the limits of most current SQL products.

Let’s create two tables. This first table is a Personnel table for the store managers only and it is keyed on their Social Security Numbers. Notice the use of DEFAULT and CHECK() on their job_type to assure that this is really a "managers only" table.

CREATE TABLE Job_99_Assignments
(store_nbr INTEGER NOT NULL PRIMARY KEY
        REFERENCES Stores (store_nbr)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
ssn CHAR(9) NOT NULL 
        REFERENCES Personnel (ssn)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date TIMESTAMP CHECK (start_date <= end_date),
job_type INTEGER DEFAULT 99 NOT NULL
         CHECK (job_type = 99));

This second table is a Personnel table for employees who are not store managers and it is also keyed on Social Security Numbers. Notice the use of DEFAULT for a starting position of "unassigned" and CHECK() on their job_type to assure that this is really a "No managers allowed" table.

CREATE TABLE Job_not99_Assignments
(store_nbr INTEGER NOT NULL
        REFERENCES Stores (store_nbr)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
ssn CHAR(9) NOT NULL PRIMARY KEY
        REFERENCES Personnel (ssn)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date TIMESTAMP CHECK (start_date <= end_date),
job_type INTEGER DEFAULT 0 NOT NULL
        CHECK (job_type BETWEEN 0 AND 98) -- no 99 code
);

From these two tables, build this UNION_ed view of all the job assignments in the entire company and show that to developers.

CREATE VIEW JobAssignments (store_nbr, ssn, start_date, end_date, job_type)
AS 
(SELECT store_nbr, ssn, start_date, end_date, job_type
   FROM Job_not99_Assignments
  UNION ALL 
 SELECT store_nbr, ssn, start_date, end_date, job_type
   FROM Job_99_Assignments)  

The key and job_type constraints in each table working together will guarantee at most one manager per store. The next step is to add INSTEAD OF triggers to the VIEW or write stored procedures, so that the users can insert, update and delete from it easily. Click here to see a simple SQL-99 stored procedure, without error handling or input validation.

Likewise, a procedure to terminate an employee:

CREATE PROCEDURE FireEmployee (IN new_ssn CHAR(9))
LANGUAGE SQL
IF new_job_typ <> 99
THEN DELETE FROM Job_not99_Assignments
      WHERE ssn = new_ssn;
ELSE DELETE FROM Job_99_Assignments
      WHERE ssn = new_ssn;
END IF; 

If a developer attempts to change the Job_Assignments VIEW directly with an INSERT, UPDATE or DELETE, they will get an error message telling them that the VIEW is not updatable because it contains a UNION operation. That is a good thing, in one way, because we can force them to use only the stored procedures.

Again, this is an exercise in programming a solution within certain limits. The TRIGGER is probably going give better performance than the VIEW. Just as Craig's original article showed how to use VIEWs to replace simple column-level CHECK() constraints, this shows how to use them to replace more complex table-level CHECK() constraints.

--

Joe Celko was a member of the ANSI X3H2 Database Standards Committee and helped write the SQL-92 standards. He is the author of over 450 magazine columns and four books, the best known of which is SQL for Smarties (Morgan-Kaufmann Publishers, 1999) and the new book, Trees and Hierarchies in SQL for Smarties (Morgan-Kaufmann Publishers, 2003). For articles, puzzles, resources, and more, go to http://www.celko.com.