Chapter 7: Logical Consistency

A key strength of any modern relational database is its ability to validate the information stored in the database. One way the database itself can perform validation is by the use of constraints on a column or columns in a table. A constraint on a table column restricts the type of information in the column. A constraint can ensure that data is not omitted from a column, is within a certain range, is unique within the table, or exists in another table.

A second way to maintain the logical consistency in a database is the ability to “group” several SQL statements together in a transaction, where either all of these SQL statements succeed or all of them fail. This group of SQL statements is considered a logical unit of work. You can control transaction processing by using the COMMIT and ROLLBACK statements.

Constraints

Constraints are a way to validate the data in a column or columns of a table. The Oracle database has five distinct types of constraints that can be defined on a column or columns in a table: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY. Only the FOREIGN KEY constraint, as its name implies, does its validation in reference to another table within the database.

constraint

A condition defined against a column or columns on a table in the database to enforce business rules or relationships between tables in the database.

Note

The end-user application frequently validates the data entered into the database, even before an INSERT or UPDATE operation occurs, and this might be the best way to implement complex business rules. The ways in which business rules are implemented in applications can be varied and complex. For more information about data validation through the use of business rules in applications, see the book Business Rules Applied: Building Better Systems Using the Business Rules Approach by Barbara Von Halle. Oracle separates the business rules enforcement from both the client and the server with its Business Components for Java (BC4J) product. More information on BC4J can be found at http://otn.oracle.com/products/ jdev/htdocs/bc4j9irc_datasheet.html.

Constraints, like many other database objects, can be defined when the table is defined or added to the table later. You can also remove, disable, or enable existing constraints.

Any constraint can have a name assigned to it when it is created. If you do not explicitly assign a name, Oracle will give the constraint a system-assigned name.

The NULL constraint can be defined only at the column level. All other constraints can be defined at the column level or at the table level. Some constraints, such as a constraint that compares the values of two columns must necessarily be defined at the table level.

NOT NULL

The NOT NULL constraint is the most straightforward of all the constraints. It specifies that a column will not allow NULL values, regardless of its datatype. The syntax for a NOT NULL constraint is as follows:

[CONSTRAINT <constraint name>] [NOT] NULL

NOT NULL constraint

A constraint that prevents NULL values from being entered into a column of a table.

In Scott’s widget database, the HR table JOBS contains the job identifier, the job description, and the minimum and maximum salary for the job. The table structure is shown here with a DESCRIBE command:

desc jobs Name                         Null?    Type ---------------------------- -------- ----------------- JOB_ID                       NOT NULL VARCHAR2(10) JOB_TITLE                    NOT NULL VARCHAR2(35) MIN_SALARY                            NUMBER(6) MAX_SALARY                            NUMBER(6)

When a new job is added or an existing job is modified, the columns for the job identifier and the job title must contain a value. The salary range columns, however, can remain undefined—either explicitly by assigning NULL values to them or implicitly by not specifying those two column names in an INSERT statement.

The boss, King, wants to make sure that when a new job is created, a minimum salary is always entered for the job. Janice, the DBA, changes the structure of the JOBS table with the ALTER TABLE command, as follows:

alter table jobs modify (min_salary not null); Table altered.

The next time someone from HR tries to add a new JOBS table row without a minimum salary, here is what happens:

insert into jobs (job_id, job_title)      values(‘IT_DBDES’, ‘Database Designer’); insert into jobs (job_id, job_title) * ERROR at line 1: ORA-01400: cannot insert NULL into     ("HR"."JOBS"."MIN_SALARY")

The MIN_SALARY field must be entered with some value, even if it is zero:

insert into jobs (job_id, job_title, min_salary)      values(‘IT_DBDES’, ‘Database Designer’, 12500); 1 row created.

At some point, the HR department may want to update this row in the JOBS table to indicate an upper range for the salary for this job position. However, it would not be unreasonable to expect that some job positions may not have any upper value, and therefore a NULL value in the MAX_SALARY field could reflect the business rule that there is no maximum salary in force for a particular position.

CHECK

A CHECK constraint can apply directly to a specific column, or it can apply at the table level if the constraint must reference more than one column. CHECK constraints are useful if you need to keep values of a column within a certain range or within a list of specific values, such as ensuring that a gender column contains either M or F.

CHECK constraint

A constraint that evaluates the condition defined in the constraint and permits the INSERT or UPDATE of the row in the table if the condition is satisfied.

The CONSTRAINT clause can be specified at either the column level or at the table level. The constraint can be specified at the column level if the constraint refers only to that column. The format of the CONSTRAINT clause is as follows:

[CONSTRAINT <constraint name>] CHECK (<condition>)

The HR department members are still having some problems with the JOBS table. They sometimes enter the lower and upper ranges for the salary amount backwards. As usual, Janice is tasked with finding a way to fix this problem. She considers changing the data-entry screens to check the salary amounts before they are inserted, but this might not be the best solution, since some of the people in the HR department use the INSERT command against the database, bypassing any business logic that might be in the application that supports the data-entry screen.

Janice decides to add a CHECK constraint to the JOBS table to make sure the salaries are entered in the correct order:

alter table jobs      add constraint ck1_jobs         check (max_salary > min_salary); Table altered.

Tip

It’s good practice to name your constraints with a reference to both the type of constraint and the table it references. This helps both DBAs and developers when tracking down which table is causing a constraint violation in an application that might have hundreds of tables.

Now if the order of the salaries were inadvertently reversed in the INSERT statement, the INSERT would not be allowed, due to the new CHECK constraint:

insert into jobs      (job_id, job_title, min_salary, max_salary)      values      (‘IT_TECHLD’, ‘Technical Lead’, 17500, 10000); insert into jobs (job_id, job_title, min_salary, max_salary) * ERROR at line 1: ORA-02290: check constraint (HR.CK1_JOBS) violated

The HR department decides that the new technical lead position has an open-ended upper salary, so the addition is made with the following INSERT command:

insert into jobs (job_id, job_title, min_salary)      values(‘IT_TECHLD’, ‘Technical Lead’, 10000); 1 row created.

Even though no maximum salary is specified, this INSERT operation still works. A CHECK constraint condition will allow the record to be inserted if the CHECK condition expression evaluates to either true or unknown. In this INSERT statement, the MAX_SALARY column is NULL, and therefore the CHECK condition expression (max_salary > min_salary) is (NULL > 10000), which evaluates to NULL (unknown). Therefore, the CHECK condition will not prevent this row from being inserted. However, explicit NULL checking can be performed in a CHECK constraint by using the IS NULL or IS NOT NULL operator.

Later in the week, Janice learns that the business rule for minimum and maximum salary in the JOBS table has changed; if a minimum salary is specified, then a maximum salary must also be specified. Therefore, either both salaries are NULL or both salaries are NOT NULL. Janice decides that a new CHECK constraint is needed to enforce this business rule, so her first step is to drop the existing constraint on the table:

alter table jobs drop constraint ck1_jobs; Table altered.

The new check constraint will compare min_salary and max_salary only if both values are NOT NULL, otherwise both values must be NULL to pass the CHECK constraint:

alter table jobs add constraint ck1_jobs     check ((max_salary is not null and             min_salary is not null and             max_salary > min_salary)            or            (max_salary is null and min_salary is null)           ); Table altered.

In rare circumstances, there is an exception to this business rule. Occasionally, the boss still wants to enter a minimum salary without a maximum salary. Janice can temporarily disable the constraint:

alter table jobs disable constraint ck1_jobs; Table altered. insert into jobs (job_id, job_title, min_salary)      values(‘IT_RSRCH’, ‘IT Research and Development’,                25000); 1 row created.

By default, if Janice re-enables the constraint, this new row in the JOBS table will fail the constraint check, so she must use the NOVALIDATE option when re-enabling the constraint:

alter table jobs enable novalidate constraint ck1_jobs; Table altered.

Using NOVALIDATE doesn’t check to see if any existing rows violate the CHECK constraint; only new or updated rows are checked. As you’d expect, the default is VALIDATE when re-enabling a constraint. When a constraint is re-enabled with VALIDATE, the data in every row is checked to make sure it passes the CHECK constraint.

UNIQUE

The UNIQUE constraint can be applied at the column level or at the table level. It ensures that no two rows contain the same value for the column or columns that have the UNIQUE constraint.

UNIQUE constraint

A constraint that prevents duplicate values from being specified in a column or combination of columns in a table. NULL values may be specified for columns that have a UNIQUE constraint defined, as long as the column itself does not have a NOT NULL constraint.

The syntax for a UNIQUE constraint clause is as follows:

[CONSTRAINT <constraint name>]      UNIQUE [(<column>, <column>, ...)]

For ensuring that a combination of two or more columns is unique within the table, the optional column specification portion of the above syntax is used at the table level.

To more easily report salaries and bonuses to the IRS, King has asked Janice, the DBA, to add a social security number column to the EMPLOYEES table. Since no two employees should have the same social security number, Janice uses a UNIQUE constraint when she adds this column to the EMPLOYEES table:

alter table employees      add (ssn varchar2(11)            constraint uk1_employees unique); Table altered. 

Janice is doing two things in one statement: adding the SSN column and adding the named constraint. The column will still allow NULL values, but when it is populated for an employee, it must not duplicate any other SSN value in the EMPLOYEES table.

When the HR department tries to update two records with the same social security number, the constraint prevents the second UPDATE command from completing successfully:

update employees      set ssn = ‘987-65-4321’      where employee_id = 116; 1 row updated.      update employees      set ssn = ‘987-65-4321’      where employee_id = 117; update employees * ERROR at line 1: ORA-00001: unique constraint (HR.UK1_EMPLOYEES) violated

PRIMARY KEY

A PRIMARY KEY constraint is similar to a UNIQUE constraint, with two exceptions: a PRIMARY KEY constraint will not allow NULL values, and only one PRIMARY KEY constraint is allowed on a table. A PRIMARY KEY constraint can be defined at either the column level or the table level. A PRIMARY KEY constraint is important when you want to find a way to uniquely reference a row in the table with the primary key in another table. The syntax for a PRIMARY KEY constraint is similar to that of the UNIQUE constraint:

[CONSTRAINT <constraint name>]      PRIMARY KEY [(<column>, <column>, ...)]

PRIMARY KEY constraint

A constraint that uniquely defines each row of a table and prevents NULL values from being specified in the column or combination of columns. Only one PRIMARY KEY constraint may be defined on a table.

If the PRIMARY KEY constraint is applied at the table level (usually due to the primary key of the table consisting of more than one column), the optional column specification portion of the above syntax is used.

Because of tighter budgets and layoffs, many employees at Scott’s widget company are performing duties in other departments, but the structure of the EMPLOYEES table supports an employee assigned to only one department at a time. Janice, the DBA, has been tasked with creating a new table that can reflect the new business rule that an employee can be working in more than one department at a time.

She decides to create a table that has three columns: an employee number, a department number, and the starting date for the employee in that department. What should the primary key be? She can’t use just the employee number (EMPLOYEE_ID), since this column won’t be unique in this table; an employee may be associated with more than one department. The same holds true for the department number column (DEPARTMENT_ID); a department will most likely have more than one employee assigned to it. Janice realizes that the combination of the two columns in this table will always be unique, and not NULL, and therefore this will be the primary key. The table definition for this new table is as follows:

create table employees_departments (employee_id   number(6),  department_id number(4),  start_date    date,  constraint pk_empdept       primary key (employee_id, department_id) ); Table created.

The names for the employee number and department number columns do not need to be identical to the names given in the EMPLOYEES and DEPARTMENTS tables, but it is good design practice to make them the same if the columns will hold the same type of information as the corresponding EMPLOYEES and DEPARTMENTS table columns.

The HR department staff performs the following INSERT operations on the new table:

insert into employees_departments      (employee_id, department_id, start_date)      values (103, 60, ‘15-sep-2002’); 1 row created.      insert into employees_departments      (employee_id, department_id, start_date)      values (104, 60, ‘12-sep-2002’); 1 row created. insert into employees_departments      (employee_id, department_id, start_date)      values (104, 50, ‘15-sep-2002’); 1 row created. insert into employees_departments      (employee_id, department_id, start_date)      values (103, 60, ‘19-sep-2002’); insert into employees_departments * ERROR at line 1: ORA-00001: unique constraint (HR.PK_EMPDEPT) violated

The fourth row is not allowed in the table, because the same combination of employee number and department number is already in the table. The PRIMARY KEY constraint of the table prevented the INSERT operation from completing successfully.

As a result of the three successful INSERT operations, employee number 103 (Hunold) is only working in department number 60 (IT), but employee number 104 (Ernst) is working in department number 60 (IT) and department number 50 (Shipping).

FOREIGN KEY

A FOREIGN KEY constraint helps maintain the data integrity between a parent table and a child table. It allows you to define a column in the child table that exists as a primary key or a unique key in the parent table. When a value is entered into a column with a FOREIGN KEY constraint, the value is checked against the primary key or unique value in the parent table to make sure it exists there; if not, the row cannot be inserted.

FOREIGN KEY constraint

A constraint that establishes a parent-child relationship between two tables via one or more common columns. The foreign key in the child table refers to a primary or unique key in the parent table.

The syntax for specifying a FOREIGN KEY constraint is as follows:

[CONSTRAINT <constraint name>]      REFERENCES [<schema>.]<table>             [(<column>, <column>, ...)]      [ON DELETE {CASCADE | SET NULL}]

As the syntax indicates, a different user can own the parent table that contains the primary or unique key referenced, and therefore the parent table name referenced must be qualified with the owner name. The column list can be omitted if the referenced key is a primary key.

The last part of the syntax, [ON DELETE {CASCADE | SET NULL}], specifies what happens when the row in the parent table is deleted. If this clause is omitted, the row in the parent table cannot be removed until all the rows containing foreign key references in all child tables are either removed or the foreign key column is set to NULL. If ON DELETE CASCADE is specified and the parent table’s row is deleted, all rows in the child table that contain the primary key of the parent table’s row are deleted. If ON DELETE SET NULL is specified, a much more benign action occurs: If a parent table row is deleted, the foreign key column in all child table rows that contain the parent row’s primary key value is set to NULL.

For about a month now, the HR department has been using the new SSN column in the EMPLOYEES table. Now the boss decides that this is not a good idea, because of privacy concerns. Other departments use the EMPLOYEES table, and the social security information should not be visible to the other departments.

Janice needs to create an entirely new table to hold the social security number values for the employees and remove the SSN column from the EMPLOYEES table. The new table must be linked to the EMPLOYEES table, so she wants to have a column with the employee number that is a foreign key to the EMPLOYEES table. She also needs the SSN column itself. She’ll put in a date field to hold the date that the social security number was entered into this table. No other columns are necessary now (columns can always be added later).

What should be the primary key of this new table? The SSN column looks like a suitable candidate for a primary key, since it is unique and not empty. Rows will not be inserted into this table until the social security number is known. Janice creates the new table, EMPLOYEES_SSN, as follows:

create table employees_ssn (ssn           varchar2(11),  employee_id   number(6)      constraint fk_empl_ssn          references employees (employee_id),  add_date      date,  constraint pk_empl_ssn primary key (ssn) ); Table created.

This new table has two constraints: a column constraint (the FOREIGN KEY constraint on the EMPLOYEE_ID column) and a table constraint (the PRIMARY KEY constraint on the SSN column, which could have also been defined as a column constraint since the primary key is only one column).

The HR department inserts the first few rows into this new table, as follows:

insert into employees_ssn (ssn, employee_id, add_date)      values(‘987-65-4321’, 101, ‘13-sep-02’); 1 row created. insert into employees_ssn (ssn, employee_id, add_date)      values(‘123-45-6789’, 102, ‘13-sep-02’); 1 row created. insert into employees_ssn (ssn, employee_id, add_date)      values(‘222-44-6666’, 303, ‘13-sep-02’); insert into employees_ssn (ssn, employee_id, add_date) * ERROR at line 1: ORA-02291: integrity constraint (HR.FK_EMPL_SSN)        violated - parent key not found insert into employees_ssn (ssn, employee_id, add_date)      values(‘999-99-9999’, 104, ‘13-sep-02’); 1 row created.

The third INSERT operation failed due to the FOREIGN KEY constraint on the table. The employee number specified (303) does not exist in the EMPLOYEES table; therefore, the row is not inserted into the EMPLOYEES_SSN table.

Once all of the social security numbers and employee numbers have been entered into the EMPLOYEES_SSN table, the SSN column in EMPLOYEES can be dropped.