Oracle also implements the idea of statement-level rollback. If a single statement fails during a transaction, the entire statement will fail. In other words, an INSERT statement for 1000 rows will insert either all 1000 rows or none at all; the entire statement works, or nothing happens. If a statement does fail within a transaction, the rest of the statements in the transaction are still in a pending state and must be committed or rolled back.
If a user process terminates abnormally (the process is killed, for example), the PMON background process automatically rolls back changes. Any changes that the process had committed up to the point of failure remain committed, and only those changes for the current transaction are rolled back.
All locks held by the transaction are automatically released when the transaction commits or rolls back or when the PMON background process rolls back the transaction. In addition, other system resources (such as rollback segments) are released for other transactions to use.
Savepoints enable you to set up markers within a transaction so that you have the option of rolling back just part of the work performed in the transaction. You can use savepoints in long and complex transactions to provide the reversing option for certain statements. However, this causes extra overhead on the system to perform the work for a statement and then reverse the changes; usually, changes in the logic can produce a more optimal solution. When Oracle performs a rollback to a savepoint, the rest of the statements in the transaction remain in a pending state and must be committed or rolled back. Oracle releases the locks taken by those statements that were rolled back.
Data Integrity
Data integrity is about enforcing data validation rules—such as checking that a percentage amount is between 0 and 100—to ensure that invalid data does not get into your tables. Historically, these rules were enforced by the application programs themselves (and the same rules were checked repeatedly in different programs). Oracle, however, enables you to define and store these rules against the database objects to which they relate so that you need to code them only once so they are enforced whenever any kind of change is made to the table, regardless of which tool issues the insert, update, or delete statement. This checking takes the form of integrity constraints and database triggers.
Integrity Constraints
Integrity constraints enforce business rules at the database level by defining a set of checks for the tables in your system. These checks are automatically enforced whenever you issue an insert, update, or delete statement against the table. If any of the constraints are violated, the insert, update, or delete statement is rolled back. The other statements within the transaction remain in a pending state and can be committed or rolled back according to application logic.
Because integrity constraints are checked at the database level, they are performed regardless of where the insert, update, delete statement originated—whether it was an Oracle or a non-Oracle tool. Defining checks using these constraints is also quicker than performing the same checks using SQL. In addition, the information provided by declaring constraints is used by the Oracle optimizer to make better decisions about how to run a statement against the table. The Oracle Forms product can also use constraints to automatically generate code in the front-end programs to provide an early warning to the user of any errors.
The types of integrity constraints that you can set up on a table are NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and indexes.
NOT NULL Constraints
You set the NOT NULL constraint against a column to specify that the column must always have a value on every row; it can never be null. By default, all the columns in a table are nullable. For example, using a NOT NULL constraint on an orders table, you can specify that there must always be an order amount.
PRIMARY KEY
The PRIMARY KEY constraint defines a column or a set of columns that you can use to uniquely identify a single row. No two rows in the table can have the same values for the primary key columns. In addition, the columns for a primary key constraint must always have a value—in other words, they are NOT NULL. If you add a constraint to a table after it has been created, any columns that make up the PRIMARY KEY constraint are modified to NOT NULL. Only one PRIMARY KEY constraint can exist for any table. For example, using a PRIMARY KEY constraint on an orders table, you can specify that a table cannot have two records with the same order number.
UNIQUE
The UNIQUE constraint defines a secondary key for the table. This is a column or set of columns that you can use as another way of uniquely identifying a row. No two rows can have the same values for the UNIQUE key column or columns. Although it is not possible for a table to have more than one primary key, a table can have more than one UNIQUE constraint.
The columns for a UNIQUE constraint do not have to be identified as NOT NULL (although they usually are). If the values for any of the columns that form the unique constraint are null, the constraint is not checked. For example, using a PRIMARY KEY and UNIQUE constraint on a customers table, you can specify that the customer number is a primary key and that the customer name is a unique key (which would mean that you could not have two customers with the same name on your table—a rare situation).
FOREIGN KEY
The FOREIGN KEY or referential integrity constraint enforces relationship integrity between tables. It dictates that a column or set of columns on the table match a PRIMARY KEY or UNIQUE constraint on a different table. For example, you could set up a FOREIGN KEY constraint on the orders table to specify that whenever an order record is inserted or updated, the customer number must also exist in the customers table. This ensures that you don't get orders for nonexistent customers.
You use FOREIGN KEY constraints to enforce parent/child relationships between tables. You can even use them to enforce self-referential constraints, usually in situations where a hierarchical structure is set up with all the rows held in the same table. If any of the columns of the foreign key are null, the constraint is not checked at all. Foreign key columns are usually declared as NOT NULL.
It is possible to specify that when the parent row is deleted, the delete should automatically cascade and delete the child rows—a dangerous situation. The user is informed only about the master rows that were removed, and he might not be aware of the additional rows that were deleted automatically in the background because he is not told that this cascading deletion has happened.
Only this automatic deletion of child rows is supported by specifying the ON DELETE CASCADE clause to the end of the foreign key creation statement. If you change the master table's key value, however, the child rows are not updated automatically to reflect the new key; you can implement this update cascade requirement using database triggers.
CHECK
A CHECK constraint specifies additional logic that must be true for the insert, update, or delete statement to work on the table. The additional logic returns a Boolean result, and in the check constraint, you ensure the values in the row being modified satisfy a set of validation checks that you specify. The syntax of a CHECK constraint is very similar to the syntax found in the WHERE clause of a SELECT statement; however, you cannot use subqueries or other columns that vary over time (such as SYSDATE). You can use database triggers to perform this additional processing that you cannot put into constraints. For example, using a CHECK constraint on the orders table, you can specify that the order amount must be greater than zero and the salesman's commission cannot be greater than 10 percent of the order total.
Indexes
PRIMARY KEY and UNIQUE constraints automatically create an index on the columns they're defined against if the constraint is enabled upon creation. If an index already exists on the columns that form the PRIMARY KEY or UNIQUE constraint, that index is used, and Oracle cannot create a new one. Oracle creates indexes when the constraint is enabled (which is the default when the constraint is first added to the table). Oracle drops the indexes from the table when the constraint is disabled. Enabling and disabling constraints can take significant time and system overhead due to the index creation and removal.
When you set up a FOREIGN KEY constraint, the columns are not indexed automatically. Because the foreign key columns are usually involved in joining tables together, you manually create indexes on those columns.
Database Triggers
A database trigger is a PL/SQL block that you can define to automatically execute for insert, update, and delete statements against a table. You can define the trigger to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. For each of the twelve events, you can define many database triggers for the same event.
A database trigger can call database procedures that are also written in PL/SQL. Unlike database triggers, procedures on the database are stored in a compiled form. For this reason, you should put the longer code segments into a procedure and then call the procedure from the database trigger.
In addition to implementing complex business rules, checking, and defaulting, you can use database triggers to insert, update, and delete other tables. An example of this use is providing an auditing facility where an audit trail is automatically created in an audit table whenever a row is changed on a table. Without database triggers, this function would be implemented in the front-end programs that make the change to the database; however, someone bypassing the code in the front-end programs (using SQL*Plus, for example) would not go through the checks and processing defined.
Database triggers differ from constraints in that they enable you to embed SQL statements within them, whereas constraints do not.
If a user process terminates abnormally (the process is killed, for example), the PMON background process automatically rolls back changes. Any changes that the process had committed up to the point of failure remain committed, and only those changes for the current transaction are rolled back.
All locks held by the transaction are automatically released when the transaction commits or rolls back or when the PMON background process rolls back the transaction. In addition, other system resources (such as rollback segments) are released for other transactions to use.
Savepoints enable you to set up markers within a transaction so that you have the option of rolling back just part of the work performed in the transaction. You can use savepoints in long and complex transactions to provide the reversing option for certain statements. However, this causes extra overhead on the system to perform the work for a statement and then reverse the changes; usually, changes in the logic can produce a more optimal solution. When Oracle performs a rollback to a savepoint, the rest of the statements in the transaction remain in a pending state and must be committed or rolled back. Oracle releases the locks taken by those statements that were rolled back.
Data Integrity
Data integrity is about enforcing data validation rules—such as checking that a percentage amount is between 0 and 100—to ensure that invalid data does not get into your tables. Historically, these rules were enforced by the application programs themselves (and the same rules were checked repeatedly in different programs). Oracle, however, enables you to define and store these rules against the database objects to which they relate so that you need to code them only once so they are enforced whenever any kind of change is made to the table, regardless of which tool issues the insert, update, or delete statement. This checking takes the form of integrity constraints and database triggers.
Integrity Constraints
Integrity constraints enforce business rules at the database level by defining a set of checks for the tables in your system. These checks are automatically enforced whenever you issue an insert, update, or delete statement against the table. If any of the constraints are violated, the insert, update, or delete statement is rolled back. The other statements within the transaction remain in a pending state and can be committed or rolled back according to application logic.
Because integrity constraints are checked at the database level, they are performed regardless of where the insert, update, delete statement originated—whether it was an Oracle or a non-Oracle tool. Defining checks using these constraints is also quicker than performing the same checks using SQL. In addition, the information provided by declaring constraints is used by the Oracle optimizer to make better decisions about how to run a statement against the table. The Oracle Forms product can also use constraints to automatically generate code in the front-end programs to provide an early warning to the user of any errors.
The types of integrity constraints that you can set up on a table are NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and indexes.
NOT NULL Constraints
You set the NOT NULL constraint against a column to specify that the column must always have a value on every row; it can never be null. By default, all the columns in a table are nullable. For example, using a NOT NULL constraint on an orders table, you can specify that there must always be an order amount.
PRIMARY KEY
The PRIMARY KEY constraint defines a column or a set of columns that you can use to uniquely identify a single row. No two rows in the table can have the same values for the primary key columns. In addition, the columns for a primary key constraint must always have a value—in other words, they are NOT NULL. If you add a constraint to a table after it has been created, any columns that make up the PRIMARY KEY constraint are modified to NOT NULL. Only one PRIMARY KEY constraint can exist for any table. For example, using a PRIMARY KEY constraint on an orders table, you can specify that a table cannot have two records with the same order number.
UNIQUE
The UNIQUE constraint defines a secondary key for the table. This is a column or set of columns that you can use as another way of uniquely identifying a row. No two rows can have the same values for the UNIQUE key column or columns. Although it is not possible for a table to have more than one primary key, a table can have more than one UNIQUE constraint.
The columns for a UNIQUE constraint do not have to be identified as NOT NULL (although they usually are). If the values for any of the columns that form the unique constraint are null, the constraint is not checked. For example, using a PRIMARY KEY and UNIQUE constraint on a customers table, you can specify that the customer number is a primary key and that the customer name is a unique key (which would mean that you could not have two customers with the same name on your table—a rare situation).
FOREIGN KEY
The FOREIGN KEY or referential integrity constraint enforces relationship integrity between tables. It dictates that a column or set of columns on the table match a PRIMARY KEY or UNIQUE constraint on a different table. For example, you could set up a FOREIGN KEY constraint on the orders table to specify that whenever an order record is inserted or updated, the customer number must also exist in the customers table. This ensures that you don't get orders for nonexistent customers.
You use FOREIGN KEY constraints to enforce parent/child relationships between tables. You can even use them to enforce self-referential constraints, usually in situations where a hierarchical structure is set up with all the rows held in the same table. If any of the columns of the foreign key are null, the constraint is not checked at all. Foreign key columns are usually declared as NOT NULL.
It is possible to specify that when the parent row is deleted, the delete should automatically cascade and delete the child rows—a dangerous situation. The user is informed only about the master rows that were removed, and he might not be aware of the additional rows that were deleted automatically in the background because he is not told that this cascading deletion has happened.
Only this automatic deletion of child rows is supported by specifying the ON DELETE CASCADE clause to the end of the foreign key creation statement. If you change the master table's key value, however, the child rows are not updated automatically to reflect the new key; you can implement this update cascade requirement using database triggers.
CHECK
A CHECK constraint specifies additional logic that must be true for the insert, update, or delete statement to work on the table. The additional logic returns a Boolean result, and in the check constraint, you ensure the values in the row being modified satisfy a set of validation checks that you specify. The syntax of a CHECK constraint is very similar to the syntax found in the WHERE clause of a SELECT statement; however, you cannot use subqueries or other columns that vary over time (such as SYSDATE). You can use database triggers to perform this additional processing that you cannot put into constraints. For example, using a CHECK constraint on the orders table, you can specify that the order amount must be greater than zero and the salesman's commission cannot be greater than 10 percent of the order total.
Indexes
PRIMARY KEY and UNIQUE constraints automatically create an index on the columns they're defined against if the constraint is enabled upon creation. If an index already exists on the columns that form the PRIMARY KEY or UNIQUE constraint, that index is used, and Oracle cannot create a new one. Oracle creates indexes when the constraint is enabled (which is the default when the constraint is first added to the table). Oracle drops the indexes from the table when the constraint is disabled. Enabling and disabling constraints can take significant time and system overhead due to the index creation and removal.
When you set up a FOREIGN KEY constraint, the columns are not indexed automatically. Because the foreign key columns are usually involved in joining tables together, you manually create indexes on those columns.
Database Triggers
A database trigger is a PL/SQL block that you can define to automatically execute for insert, update, and delete statements against a table. You can define the trigger to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. For each of the twelve events, you can define many database triggers for the same event.
A database trigger can call database procedures that are also written in PL/SQL. Unlike database triggers, procedures on the database are stored in a compiled form. For this reason, you should put the longer code segments into a procedure and then call the procedure from the database trigger.
In addition to implementing complex business rules, checking, and defaulting, you can use database triggers to insert, update, and delete other tables. An example of this use is providing an auditing facility where an audit trail is automatically created in an audit table whenever a row is changed on a table. Without database triggers, this function would be implemented in the front-end programs that make the change to the database; however, someone bypassing the code in the front-end programs (using SQL*Plus, for example) would not go through the checks and processing defined.
Database triggers differ from constraints in that they enable you to embed SQL statements within them, whereas constraints do not.
