© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2021
W. D. AshleyFoundation Db2 and Pythonhttps://doi.org/10.1007/978-1-4842-6942-8_6

6. Business Rules and Constraints

W. David Ashley1  
(1)
Austin, TX, USA
 

Creating business rules and constraints on your SQL tables is actually a part of the database design phase. We treat it as a separate topic as these rules can be added later instead of creating them at the same time as the tables. This is really a good thing as none of us can think of everything during the design phase.

There are two kinds of business rules and constraints – those that perform referential integrity of the tables and those that apply actual business rules. These two kinds of constraints are sometimes confused, but we will try to keep them separate in our explanation.

This chapter will group the constraints by their type. This will include indexes, NOT NULL attribute, primary keys, foreign keys, table checks, and informational constraints. In many cases, a constraint can fit into more than one category, which is why we will group them by Db2 type rather than by their constraint type.

NOT NULL Attribute

When you create a table in Db2, all columns in the table allow NULLs by default. If NULLs should not be allowed, you must explicitly include the NOT NULL phrase in the column definition. Consider the following table definition:
CREATE TABLE employee (
    emp_num CHAR(10) NOT NULL,
    name CHAR(50),
    location VARCHAR(30),
    create_date TIMESTAMP
    );

In this table, the emp_num column must contain data, even if it is just a single blank. The NOT NULL constraint ensures that the column always has some data in it. It may not necessarily be valid data (we will discuss this topic later), but it must contain data of some type.

The columns name, location, and create_date may all have the NULL type assigned to them. This is the default for all columns that do not include the NOT NULL phrase as a part of the column definition.

If you need to add a NOT NULL phrase to a column definition, you can do that with the ALTER TABLE statement:
ALTER TABLE employee ALTER name CHAR(50) NOT NULL;

This will add the NOT NULL clause to the name column without modifying the data type.

Primary Key

A primary key identifies a column that uniquely identifies a single row in the table. You can assign a primary key column to a table using the PRIMARY KEY phrase. For instance, we could have added this phrase to the column definition to make the emp_num column the primary key:
emp_num CHAR(10) NOT NULL PRIMARY KEY,
You can also alter the table later to add this constraint:
ALTER TABLE employee ADD PRIMARY KEY (emp_num);
If the data in a table has no natural column that can act as a primary key, you can artificially create one for the table. Consider the following table definition:
CREATE TABLE employee (
    emp_num INTEGER NOT NULL PRIMARY KEY
    name CHAR(50),
    location VARCHAR(30),
    create_date TIMESTAMP
    );

Here we have modified the emp_num column so that Db2 will add one to the highest existing integer in the entire column and assign that to the new emp_num column automatically when a new row is inserted into the table.

You should not think of the primary key as a unique index even though that is what is created underneath. While they have many of the same characteristics, they are not the same. For one thing, a table can have only one primary key, but it can have many unique and nonunique indexes.

Indexes

A table can have many indexes, but usually has only one or two as indexing everything in a table is counterproductive. There are two kinds of indexes:

  • Unique indexes: This type of index prevents an entry in the indexed column from appearing more than once in the table. The UNIQUE keyword specifies this kind of index.

  • Nonunique indexes: This type of index will improve the ability of Db2 to read the table when it is used in the WHERE clause of a SELECT statement.

Indexes can be created when the CREATE INDEX statement is executed. The following example shows how to create an index on a column with the CREATE INDEX statement:
CREATE INDEX myindex ON employee (location);

An index can also be dropped with the DROP INDEX statement.

A word of warning is needed for indexes at this point. Try not to go overboard when creating indexes. You may think that indexing all columns in a table will give you the best access times for all your queries. This is completely untrue and will instead cause most of the indexes to be ignored.

Foreign Keys

Foreign keys are column values that refer to another column in a different table. This is done to ensure that that all foreign key values exist in the main table. For instance, when you assign a person to a department, you may want to ensure that the department actually exists before the operation can succeed. The following example will demonstrate this principle:
CREATE TABLE department (
    dept_num INTEGER NOT NULL PRIMARY KEY,
    name CHAR(50)
    );
CREATE TABLE employee (
    emp_num INTEGER NOT NULL PRIMARY KEY
    name CHAR(50),
    dept INTEGER REFERENCES department (dept_num),
    location VARCHAR(30),
    create_date TIMESTAMP
    );

The department table defines all departments within the organization. The employee table has a dept column that references the dept_num column in the department table. What this means is that when you try to insert a new record in the employee table, the system will check to make sure that the dept column value exists in the dept_num column in the department table. If no corresponding department number is found in the department table, then the INSERT will fail.

Foreign keys are a type of referential constraint, or sometimes known as a foreign key constraint. A foreign key can refer to either a primary key or an index. In the case of an index, the index must be a single column, but it does not need to be a unique index.

The following example shows a foreign key pointing to an index:
CREATE TABLE department (
    dept_num INTEGER NOT NULL,
    name CHAR(50)
    );
CREATE UNIQUE INDEX ON department (dept_num);
CREATE TABLE employee (
    emp_num INTEGER NOT NULL PRIMARY KEY
    name CHAR(50),
    dept INTEGER REFERENCES department (dept_num),
    location VARCHAR(30),
    create_date TIMESTAMP
    );

In the preceding example, the UNIQUE keyword ensures that all dept_num numbers are unique in the department table. An attempt to insert a duplicate department number in the table will be rejected.

CHECK and Unique Constraints

The CHECK constraint ensures that only values in a certain range are allowed to be in a table column. The following shows an example of a CHECK constraint:
CREATE TABLE employee (
    empid INTEGER,
    name VARCHAR(30),
    ssn VARCHAR(11) NOT NULL,
    salary INTEGER CHECK (salary >= 5000),
    job VARCHAR(10) CHECK (job IN ('Engineer', 'Sales', 'Manager')));

The salary column must have a value greater than or equal to 5000 when a row is inserted into or updated in the table. The job column must have one of the three values specified (case sensitive). The constraint expression can have the values as specified previously, or it could be a sub–SELECT statement that returns the set of specified valid values.

DEFAULT Constraint

The DEFAULT constraint allows you to specify a default value to a column when a row is written into the table. You specify a DEFAULT constraint as shown in the following example:
CREATE TABLE employee (
    empid INTEGER,
    name VARCHAR(30),
    ssn VARCHAR(11) WITH DEFAULT '999-99-999');
The following example INSERT statement will show how this constraint works:
INSERT INTO employee (empid, name)
    VALUES (005, 'Smith, James');

Note that the ssn field is not referenced. Normally this could cause a problem with the INSERT statement. But in this case, a default value for ssn has been specified, so no error is raised.

Triggers

Triggers are database objects that respond to events such as an INSERT, UPDATE, or DELETE on a specific table or view. Triggers can be used in addition to all the other referential constraints or CHECK restraints to enforce data integrity business rules. There are five components associated with any trigger:

  • The subject on which the trigger is defined – tables or views

  • The event that initiates the trigger – an INSERT, UPDATE, or DELETE action

  • The activation time of the trigger – a BEFORE or AFTER event

  • The granularity that specifies whether the trigger’s actions are performed once for the table or once for each of the affected rows – a FOR EACH STATEMENT or FOR EACH ROW action

  • The action that the trigger performs – one or more of the following elements:
    1. a.

      CALL statement

       
    2. b.

      DECLARE and/or SET statement

       
    3. c.

      WHILE and/or FOR loop

       
    4. d.

      IF, SIGNAL, ITERATE, LEAVE, and GET DIAGNOSTIC statements

       
    5. e.

      SELECT statement

       
    6. f.

      INSERT, UPDATE, DELETE, and MERGE SQL statements (only for AFTER and INSTEAD OF triggers)

       
Triggers are classified as BEFORE, AFTER, and INSTEAD OF:

  • BEFORE triggers are activated before an UPDATE or INSERT operation, and the values that are being updated or inserted can be changed before the database is modified.

  • AFTER triggers are activated after an INSERT, UPDATE, or DELETE operation and are used to maintain relationships between data or keep audit trail information.

  • INSTEAD OF triggers define how to perform an INSERT, UPDATE, or DELETE operation on a view where these operations are otherwise not allowed.

The syntax for creating a trigger is as follows:
CREATE or REPLACE TRIGGER [Triggername]
    <NO CASCADE> | <AFTER | BEFORE | INSTEAD OF> [TriggerEvent]
    ON [Tablename | ViewName]
    REFERENCING <OLD AS | NEW AS | OLD TABLE AS | NEW TABLE AS>
    [CorrelationName | Identifier]
    <FOR EACH ROW | FOR EACH STATEMENT>
    <Action>
where

  • TriggerName: Identifies the name to assign to the trigger to be created.

  • TriggerEvent: Specifies the triggered action associated with the trigger to be executed whenever one of the events is applied to the subject table or subject view.

  • TableName: Identifies the name of the table (the subject) on which the trigger is defined.

  • ViewName: Identifies the name of the view (the subject) on which the trigger is defined.

  • CorrelationName: A temporary table name that identifies the row state before triggering the SQL operation.

  • Identifier: A temporary table name that identifies the set of affected rows before triggering the SQL operation.

  • Action: Specifies the action to perform when a trigger is activated. A trigger action is composed of an SQL procedure statement and an optional condition for the execution of the SQL procedure statement.

While there are almost an infinite number of ways to use triggers, we will show only four simple examples. These examples can be used as the basis for other triggers you create.

The following example illustrates a BEFORE trigger that is activated when an INSERT statement is executed on the employee table. The trigger assigns a value of the next day when it detects a NULL being inserted into the empstart column of the employee table:
CREATE OR REPLACE TRIGGER employeeJoinDate
    NO CASCADE BEFORE INSERT ON enployee
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2SQL
    WHEN (N.empstartdate IS NULL)
    SET N.empstartdate = CURRENT DATE + 1 DAY;
In the following example, an AFTER trigger is activated when an INSERT statement is executed on the employee table . If the row being inserted into the employee table is due to a new hire, the trigger statement will update the employee head count in the company statistics table company_stats:
CREATE OR REPLACE TRIGGER employeeNewHire
    NO CASCADE AFTER INSERT ON employee
    FOR EACH ROW
    MODE DB2SQL
    UPDATE company_stats SET emp_tcount = emp_tcount + 1;
What if an HR department wants to check for an employee’s pay raise before the salary change is made in the employee table? For any employee, if the pay raise is double the current salary, it must be recorded in the salary_audit table for analysis purposes. An AFTER trigger can be created to insert the appropriate data in the salary_audit table:
CREATE OR REPLACE TRIGGER employeeSalaryUpdate
    AFTER UPDATE OF salary ON employee
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW
    MODE DB2SQL
    WHEN (N.salary > O.salary * 2)
      INSERT INTO salary_audit
        (empno, old_salary, New_salary, rating)
        VALUES (N.empid, O.salary, N.salary, N.rating);
Triggers can also be used to raise errors through the SIGNAL statement and to prevent specific operations on the tables. If there is a rule that no pay raise can exceed 300%, you can create a trigger that will check for this condition:
CREATE TRIGGER salaryRaiseLimit
    AFTER UPDATE OF salary ON employee
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW
    WHEN (N.salary > O.salary * 3)
    SIGNAL SQLSTATE '75000'  SET MESSAGE_TEXT = 'Salary increase > 300%';

Summary

In this chapter, we have introduced the concept of business rules and constraints. These rules and constraints can help you implement the rules your organization may have established for the upkeep of their data. They can also help implement organizational characteristics for the organization as well as establish relationships between different sections of the company.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.200.86