Changing and Creating Consistently with Integrity

Part 5 in a second series on the basics of the relational database and SQL

September/October 2016

This article is the fifth in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine.

The previous Beyond SQL 101 article, “Manipulate, Commit, and Take Back: How Change Occurs,” introduced you to data manipulation language (DML) and three of its statements: INSERT, UPDATE, and DELETE, as well as one data definition language (DDL) statement: TRUNCATE. You learned how an UPDATE statement handles NULL values differently from a SELECT statement. You also learned what a session is and that transaction control consists of making changes permanent with the COMMIT statement or undoing changes with the ROLLBACK statement. Finally, you discovered that a ROLLBACK statement has no effect on either a COMMIT statement or a TRUNCATE statement.

In this article, you’ll learn about one more DML statement: MERGE. MERGE is used to combine multiple DML commands such as INSERT, UPDATE, and DELETE into one statement. You’ll also learn about Oracle’s locking capabilities for handling DML statements issued in a database that’s accessed by multiple users concurrently. You’ll be introduced to the impact that referential integrity has on DML statements and how it’s designed to protect your data from results you don’t intend. You’ll discover how Oracle’s read consistency features help multiple users of an Oracle Database instance obtain and retain their own consistent view of data at all times. Last, you’ll get an introduction to Oracle data definition language (DDL), which you can use to create, alter, and drop schema objects.

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 1 (12.1.0.2.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.)

Suppose that your company requires annual updates of its employee data. You must modify the EMPLOYEE table data to assign certain employees to new managers, give certain employees salary raises, and assign certain employees to new departments. Other employees are part of an annual termination list, and you must remove them from the EMPLOYEE table. One way to accomplish this multichange action is through the MERGE command in Oracle Database.

Together, Listing 1 and Listing 2 illustrate use of the MERGE command to make an annual update to the EMPLOYEE table. In Listing 1, a copy of EMPLOYEE, named ANNUAL_REVIEW, is made. Then all the employee edits are made in the ANNUAL_REVIEW table. In Listing 2, the changes in ANNUAL_REVIEW are merged into the EMPLOYEE table. The MERGE operation compares the two tables’ rows by checking whether the EMPLOYEE_ID value in the EMPLOYEE table matches the EMPLOYEE_ID value in the ANNUAL_REVIEW table. When the EMPLOYEE_ID values match, the SALARY, MANAGER, and DEPARTMENT_ID values from the ANNUAL_REVIEW table are used to update the column values of the corresponding EMPLOYEE rows. When the EMPLOYEE_ID values don’t match, the extra employee rows from the ANNUAL_REVIEW table (the records for Mary Streicher and Ernest Muchmore) are inserted into the EMPLOYEE table.

Code Listing 1: Preparing data for a subsequent MERGE statement

-- Prework: Ready the data changes that will ultimately be merged with the
EMPLOYEE table
-- Create a copy of the EMPLOYEE table and preperform all expected annual
edits upon this table
SQL> create table annual_review as select * from employee;
Table created.
SQL> select *
 2  from annual_review;
EMPLOYEE_ID FIRST_NAME  LAST_NAME  HIRE_DATE  SALARY  MANAGER DEPARTMENT_ID
——————————— ——————————— —————————— ————————— ——————— ———————— —————————————
    6574    Marcy       Tamra      04-MAY-16               28            10
    6575    Sasha       Meyer      04-MAY-16   85000     1234            10
     28     Emily       Eckhardt   07-JUL-04  100000                     10
     37     Frances     Newton     14-SEP-05   7500
    1234    Donald      Newton     24-SEP-06   80000       28            10
    7895    Matthew     Michaels   16-MAY-07   70000       28            10
    6567    Roger       Friedli    16-MAY-07   60000       28            10
    6568    Betsy       James      16-MAY-07   60000       28            10
    6569    michael     peterson   03-NOV-08   90000                     20
    6570    mark        leblanc    06-MAR-09   65000     6569            20
    6571    Thomas      Jeffrey    27-FEB-10  300000                     30
    6572    Theresa     Wong       27-FEB-10   70000     6571            30
    6573    Lori        Dovichi    07-JUL-11               28            10
13 rows selected.
SQL> select *
 2  from department;
DEPARTMENT_ID NAME       LOCATION
————————————— —————————— —————————————
           10 Accounting LOS ANGELES
           20 Payroll    NEW YORK
           30 IT         WASHINGTON DC
3 rows selected.
-- Create a new Marketing department
SQL> insert into department (department_id, name, location)
 2 values (40, 'Marketing', 'CHICAGO');
1 row created.
SQL> select *
 2  from department;
DEPARTMENT_ID NAME       LOCATION
————————————— —————————— —————————————
           10 Accounting LOS ANGELES
           20 Payroll    NEW YORK
           30 IT         WASHINGTON DC
           40 Marketing  CHICAGO
4 rows selected.
-- Create a new manager for the Marketing department
SQL> insert into annual_review (employee_id, first_name, last_name, hire_date,
salary, manager, department_id)
 2 values (6576, 'Mary', 'Streicher', to_date('16-MAY-2016', 'DD-MON-YYYY'),
200000, null, 40);
1 row created.
-- Assign the employee Sasha Meyer to the new employee manager, Mary Streicher
SQL> update annual_review
 2   set manager      = 6576,
 3     department_id  = 40
 4  where employee_id = 6575;
1 row updated.
-- Assign the employee Frances Newton to the Accounting department employee
manager,  Donald Newton
SQL> update annual_review
 2   set manager     = 1234,
 3     department_id = 10
 4  where manager      is null
 5   and department_id is null;
1 row updated.
-- Give 10% salary raises to all employees hired before June 1, 2006
SQL> update annual_review
 2   set salary = salary + (salary * .1)
 3  where hire_date < to_date('01-JUN-2006', 'DD-MON-YYYY');
2 rows updated.
-- To illustrate how MERGE works with DELETE, insert an employee that will meet
the criteria for termination 
SQL> insert into annual_review (employee_id, first_name, last_name, hire_date,
salary,
manager, department_id)
 2 values (6577, 'Ernest', 'Muchmore', to_date('01-OCT-2000', 'DD-MON-YYYY'),
1000000, null, 40)
 3 ;
1 row created.
SQL> commit;
Commit complete.
SQL> select *
 2  from annual_review;
EMPLOYEE_ID FIRST_NAME LAST_NAME  HIRE_DATE  SALARY MANAGER DEPARTMENT_ID
——————————— —————————— —————————— ————————— ——————— ——————— —————————————
       6574 Marcy      Tamra      04-MAY-16              28            10
       6575 Sasha      Meyer      04-MAY-16   85000    6576            40
         28 Emily      Eckhardt   07-JUL-04  110000                    10
         37 Frances    Newton     14-SEP-05   82500    1234            10
       1234 Donald     Newton     24-SEP-06   80000      28            10
       7895 Matthew    Michaels   16-MAY-07   70000      28            10
       6567 Roger      Friedli    16-MAY-07   60000      28            10
       6568 Betsy      James      16-MAY-07   60000      28            10
       6569 michael    peterson   03-NOV-08   90000                    20
       6570 mark       leblanc    06-MAR-09   65000    6569            20
       6571 Thomas     Jeffrey    27-FEB-10  300000                    30
       6572 Theresa    Wong       27-FEB-10   70000    6571            30
       6573 Lori       Dovichi    07-JUL-11              28            10
       6576 Mary       Streicher  16-MAY-16  200000                    40
       6577 Ernest     Muchmore   01-OCT-00 1000000                    40
15 rows selected.

Code Listing 2: The MERGE statement used for multiple conditional and dependent DML commands combined into one DML statement

SQL> merge into employee e
 2 using (select employee_id, first_name, last_name, hire_date, salary, manager,
department_id
 3      from annual_review) ar
 4   on (e.employee_id = ar.employee_id)
 5 when matched then
 6 update set e.salary   = ar.salary,
 7       e.manager       = ar.manager,
 8       e.department_id = ar.department_id
 9 when not matched then
 10 insert (e.employee_id, e.first_name, e.last_name, e.hire_date, e.salary,
e.manager, e.department_id)
 11 values (ar.employee_id, ar.first_name, ar.last_name, ar.hire_date,
ar.salary, ar.manager, ar.department_id);
15 rows merged.
SQL> select *
 2  from employee;
EMPLOYEE_ID FIRST_NAME LAST_NAME  HIRE_DATE  SALARY MANAGER DEPARTMENT_ID
——————————— —————————— —————————— ————————— ——————— ——————— —————————————
       6574 Marcy      Tamra      04-MAY-16              28            10
       6575 Sasha      Meyer      04-MAY-16   85000    6576            40
         28 Emily      Eckhardt   07-JUL-04  110000                    10
         37 Frances    Newton     14-SEP-05   82500    1234            10
       1234 Donald     Newton     24-SEP-06   80000      28            10
       7895 Matthew    Michaels   16-MAY-07   70000      28            10
       6567 Roger      Friedli    16-MAY-07   60000      28            10
       6568 Betsy      James      16-MAY-07   60000      28            10
       6569 michael    peterson   03-NOV-08   90000                    20
       6570 mark       leblanc    06-MAR-09   65000    6569            20
       6571 Thomas     Jeffrey    27-FEB-10  300000                    30
       6572 Theresa    Wong       27-FEB-10   70000    6571            30
       6573 Lori       Dovichi    07-JUL-11              28            10
       6576 Mary       Streicher  16-MAY-16  200000                    40
       6577 Ernest     Muchmore   01-OCT-00 1000000                    40
15 rows selected.

MERGE statements can also use a DELETE clause. This clause is an optional condition used with the WHEN MATCHED THEN UPDATE clause. The rows that satisfy both the DELETE and ON conditions are the only rows deleted. In Listing 3, the salary value for Ernest Muchmore is updated to 2000000 in the ANNUAL_REVIEW table. Then all employee records that match between the ANNUAL_REVIEW table and the EMPLOYEE table are merged, based on their EMPLOYEE_ID values. Because all EMPLOYEE_ID values match between the two tables, each salary value for every employee in the EMPLOYEE table is updated with the corresponding employee salary value from the ANNUAL_REVIEW table. Before the MERGE action is completed, the DELETE condition is applied and one row is found to satisfy both the ON and DELETE conditions. Because the record for Ernest Muchmore has a salary value greater than 1000000, it is deleted immediately after its salary value is updated to 2000000, per the UPDATE clause. Because no records that are unmatched between the two tables are found, the WHEN NOT MATCHED THEN INSERT clause is not applied.

Code Listing 3: The MERGE statement with a DELETE condition

SQL> update annual_review
 2   set salary = 2000000
 3  where employee_id = 6577;
1 row updated.
SQL> merge into employee e
 2 using (select employee_id, first_name, last_name, hire_date, salary,
manager, department_id
 3      from annual_review) ar
 4  on (e.employee_id = ar.employee_id)
 5 when matched then
 6 update set e.salary = ar.salary
 7 delete where salary > 1000000
 8 when not matched then
 9 insert (e.employee_id, e.first_name, e.last_name, e.hire_date, e.manager,
e.department_id)
 10 values (ar.employee_id, ar.first_name, ar.last_name, ar.hire_date,
ar.manager, ar.department_id);
15 rows merged.
SQL> commit;
Commit complete.
SQL> select *
 2  from employee
 3  where employee_id = 6577;
No rows selected. 

Use the MERGE statement only with small tables (those with no more than a few thousand records). It’s also a good idea to use MERGE only for seldom-executed actions (as in the annual update scenario) and only during nonpeak business processing hours. The reason for these precautions is that Oracle Database employs locking mechanisms to protect your edits while you perform them. When locks are in place, other users of your database might not be able to access data in the tables you’re updating. The larger the tables, the longer the merge will take and the longer the locks will be in place. The benefits of a multiuser database are undermined if access is restricted for extended periods, especially during regular business hours.

In a multiuser database, more than one user (or session) might want to manipulate the same piece of information. Oracle Database uses locking to ensure data consistency in such situations. When you issue DML statements such as INSERT, UPDATE, DELETE, or MERGE, the rows that satisfy the conditions for these statements are automatically locked while they are being modified. The lock prevents other sessions from modifying the records until your modifications are complete. Other sessions can only read the last-committed version of those records.

Any session attempting to modify the same row must wait for the current session’s changes to be either committed or rolled back. The commit or rollback releases the lock on the row and allows the waiting session to acquire a new lock on the row and begin modification. (Unlike the other DML statements, the SELECT statement does not lock rows, because other sessions are allowed to see committed data only.)

DML statements are not the only types of actions that acquire locks. DDL statements acquire locks on an entire table (instead of individual rows) when a DDL operation, such as ALTER TABLE (used to modify or add columns or add constraints, for example), is issued. In many cases, a DDL operation cannot take place on a table while DML locks on the table are being held, and vice versa. (This rule admits a few exceptions you’ll read about later in this series, when you learn how to create indexes.)

Oracle Database uses referential integrity to ensure that relationships between tables remain consistent. As you learned in your initial introduction to referential integrity in the SQL 101 article “Modeling and Accessing Relational Data,” the values of a foreign key correspond to values of a primary key. Unlike INSERT and UPDATE, a DELETE action can be prevented, depending on the action’s effect on dependent child records (that is, records associated via a foreign key constraint). The effect on child records is based on how the foreign key constraint is defined. The three ways you can specify how a foreign key constraint should behave with respect to DELETE actions are

• RESTRICT the parent row from being deleted if any child rows exist
• CASCADE the delete action to the child rows and delete them before deleting the parent row
• SET the foreign key value in a child row to NULL before deleting the parent row (only if NULL values within the foreign key constraint are allowed)

If you try to delete a record that has child records, the default behavior in Oracle Database is to stop you from deleting the record, thereby preventing child records from being orphaned. This default behavior employs the RESTRICT foreign key constraint DELETE condition.

Consider the example in Listing 4. The first two DDL commands place a primary key constraint for department data on the DEPARTMENT table and a foreign key constraint on the table that references department data (in this case, the EMPLOYEE table). After executing the commands in Listing 4, you have a constrained relationship between the DEPARTMENT_ID value in the DEPARTMENT table and the DEPARTMENT_ID value in the EMPLOYEE table. Because you haven’t specified a certain DELETE condition on your foreign key constraint, the RESTRICT method is applied and you’re prevented from deleting any record in the DEPARTMENT table that has a child record in the EMPLOYEE table.

Code Listing 4: DELETEs and referential integrity

SQL> alter table department add constraint department_pk
primary key (department_id);
Table altered.
SQL> alter table employee add constraint department_fk
foreign key (department_id) references department (department_id);
Table altered.
SQL> delete from department
 2  where name = 'Marketing';
delete from department
*
ERROR at line 1:
ORA-02292: integrity constraint (SQL_201.DEPARTMENT_FK) violated -
child record found

The error message in Listing 4 informs you that you’ve tried to violate the integrity between parent and child and you are not allowed to complete your DELETE action as such. In this case, you must either delete all associated child records first or change your foreign key constraint DELETE condition to one that is less restrictive. (Creating and altering primary and foreign key constraints will be covered more fully in a future article in this series.)

Oracle Database guarantees each executed SQL statement a read-consistent view of data. This means that when a statement begins processing, the values it obtains via a query are guaranteed not to change—even if another session changes the values in the meantime—until the session reissues its query. Recall that in Listing 6 of the previous Beyond SQL 101 article, “Manipulate, Commit, and Take Back: How Change Occurs,” you establish a second, separate connection to the database without exiting your first session. In your first session, you insert a new employee record without committing the record to the database. When you query the EMPLOYEE table for the record, you retrieve the newly inserted record.

However, your second session is unable to retrieve the same record. Only after you issue a COMMIT statement in your first session is your second session able to retrieve the new record. When a user changes data with a DML operation, Oracle Database has to keep track of the old values. Until the user commits the changes, all other users querying the data will see the old values. And if the user rolls back the changes, the old values must be available to return the data to its previous state.

Oracle stores the old values for changed, uncommitted data in undo segments. Sessions querying data that another user has changed but not yet committed must read the data from the undo segments to receive their read-consistent view of the data. When a session commits its changes, the undo segment is made available for other session DML changes and the committed changes are overwritten on the undo segment. If a session rolls back its changes, the old values from the undo segment are reapplied to the table data, effectively undoing the changes.

You’re already somewhat familiar with DDL commands, because you’ve used them in your SQL_201 schema and object creation script to create the tables for the SQL_201 schema. The most basic type of object you create as an Oracle SQL developer is a table. Consider the example in Listing 5. The CREATE TABLE command, followed by the name of the table, is used to create tables. You can either list the columns and their associated datatypes explicitly (as shown in Listing 5) or create a table based on the definition of an existing table.

Code Listing 5: Creating a table with DDL

SQL> create table employee_example (
 2   employee_id   number,
 3   first_name    varchar2(30),
 4   last_name     varchar2(30),
 5   hire_date     date,
 6   salary        number(9,2),
 7   manager       number,
 8   department_id number);
Table created.

When you define a table name and a column list explicitly, you must be aware of naming and length conventions and restrictions. No database object name, including table names, can exceed 30 characters (as of Oracle Database 12c Release 1). The name must begin with a letter, cannot include spaces or hyphens, and should accurately describe the data it contains. For example, the name T should never be used for a table that contains actual data for your business. Consistency is also important. All of your tables should have plural names (INVOICES and ORDERS, for example), or all should have singular names (INVOICE and ORDER, for example); never mix plural and singular table names in your database.

Column names must be unique within a table and also must not exceed 30 characters (as of Oracle Database 12c Release 1). Columns are explicitly defined by name, datatype, and length (where appropriate); column names, like table names, should also accurately describe the data values they represent. Note in Listing 5 that you use a comma to separate column definitions within a CREATE TABLE statement.

This article has demonstrated how the MERGE statement can be used to combine multiple INSERT, UPDATE, and DELETE commands into one statement. You also learned when it’s appropriate to use the MERGE statement and when it isn’t. You discovered locking mechanisms in Oracle Database and learned how the database guarantees its users read consistency. You also discovered that Oracle Database enforces automatic referential integrity when it comes to deleting data constrained by a foreign key constraint. Last, you were introduced to data definition language and saw how to use it to create a table. In the next article in this series, you’ll learn more about DDL.