The following topics cover data management:

This chapter discusses the following topics:

In most cases, you can use SQL statements to perform these database management tasks.

You can also enter the SQL statements using the SQL Data Manager. For more information about using SQL Data Manager, see PSQL User's Guide.

You can use referential integrity (RI) with PSQL to define how each table is related to other tables in the database. RI assures that when a column (or group of columns) in one table refers to a column (or group of columns) in another table, changes to those columns are synchronized. RI provides a set of rules that define the relationships between tables. These rules are known as referential constraints. (Referential constraints are also informally referred to as relationships.)

When you define referential constraints for tables in a database, the MicroKernel Engine enforces the constraints across all applications that access those tables. This frees the applications from checking table references independently each time an application changes a table.

You must name your database in order to use RI. Once you have defined referential constraints, each affected data file contains the database name. When someone attempts to update a file, the MicroKernel Engine uses the database name to locate the data dictionary containing the applicable RI definitions and checks the update against those RI constraints. This prevents PSQL applications from compromising RI, since the MicroKernel Engine blocks updates that do not meet referential constraints.

To define referential constraints on the tables in a database, use CREATE TABLE and ALTER TABLE statements. For syntax information, see the following topics in SQL Engine Reference:

The following definitions are useful in understanding referential integrity.

To use RI, you must define keys. There are two types of keys: primary and foreign.

A primary key is a column or group of columns whose value uniquely identifies each row in a table. Because the key value is always unique, you can use it to detect and prevent duplicate rows.

A foreign key is a column or set of columns that is common to the dependent and parent tables in a table relationship. The parent table must have a matching column or set of columns that is defined as the primary key. Foreign keys reference primary keys in a parent table. It is this relationship of a column in one table to a column in another table that provides the MicroKernel Engine with its ability to enforce referential constraints.

A good primary key has these characteristics:

You create a referential constraint by creating a foreign key on a table. However, before creating the foreign key, you must create a primary key on the parent table to which the foreign key refers.

A table can have only one primary key. You can create a primary key using either of the following:

The following example creates the primary key ID on the Person table in the sample database:

When creating a primary key, remember that PSQL implements the primary key on the table using a unique, non-null, non-modifiable index. If one does not exist for the specified columns, then PSQL adds a non-named index with these attributes containing the columns specified in the primary key definition.

You can delete a primary key only after you have dropped all foreign keys that depend on it. To drop a primary key from a table, use a DROP PRIMARY KEY clause in an ALTER TABLE statement. Since a table can have only one primary key, you do not have to specify the column name when you drop the primary key, as the following example illustrates:

To change a table’s primary key, follow these steps:

A foreign key is a column or set of columns that is common to the dependent and parent tables in a table relationship. The parent table must have a matching column or set of columns that is defined as the primary key. When you create a foreign key, you are creating a referential constraint, or a data link, between a dependent table and its parent table. This referential constraint can include rules for deleting or updating dependent rows in the parent table.

The foreign key name is optional. If you do not specify a foreign key name, PSQL tries to create a foreign key using the name of the first column in the foreign key definition. For more information about naming conventions for foreign keys and other database elements, see Naming Conventions.

Because PSQL keywords are reserved words, you cannot use them in naming database elements. For a list of keywords, see SQL Reserved Words in SQL Engine Reference.

To create a foreign key in an existing table, follow these steps:

To create a foreign key when creating the table, follow these steps:

To delete a foreign key from a table, use a DROP CONSTRAINT clause in an ALTER TABLE statement. You must specify the foreign key name since a table can have more than one foreign key.

Databases on which you define referential constraints must meet the following requirements:

In order for a database to support referential integrity it must also support the concept of foreign keys. A foreign key is a column or set of columns in one table (called the dependent table) that is used to reference a primary key in another table (called the parent table). The RI rule requires all foreign keys’ values to reference valid primary key values. For example, a student cannot enroll in a nonexistent course.

You can use a CREATE TABLE or ALTER TABLE statement to define keys on a table in a named database. The following sections explain how to create and modify keys. These sections also provide examples of referential constraints.

After you define referential constraints on a database, applications that do not perform data updates according to referential rules may fail. For example, if an application tries to insert a row into a dependent table before inserting the corresponding parent row into the parent table, the insertion fails. See Referential Integrity Rules for more information.

Certain rules apply to inserting and updating rows in dependent tables and updating and deleting rows in parent tables when you define referential constraints on database tables. PSQL supports the restrict and cascade rules as follows:

The insert rule is a restrict rule. For each foreign key in the row being inserted, the foreign key value must be equivalent to a primary key value in the parent table. The parent table must contain a parent row for the foreign key in the row you are inserting; otherwise, the insertion fails. PSQL causes the MicroKernel Engine to automatically enforce the insert rule on dependent tables.

The update rule is also a restrict rule. A foreign key value must be updated to an equivalent primary key value in the parent table. If the parent table does not contain a parent row for the foreign key value, the update fails.

You can explicitly specify the update rule as restrict when you define a foreign key on a table; however, PSQL causes the MicroKernel Engine to enforce the rule by default if you do not specify it.

You can explicitly specify the delete rule as either restrict or cascade when you define a foreign key. If you do not specify the delete rule explicitly, PSQL assumes a default of restrict for the delete rule.

The following guidelines govern the delete rule for foreign keys:

PSQL enforces these guidelines on databases that have referential constraints defined. If you attempt to declare delete rules that violate these guidelines, PSQL returns a status code to indicate an error occurred.

PSQL enforces the delete rule guidelines to avoid certain anomalies that might otherwise occur when you delete dependent rows from tables. Following are examples of anomalies that might occur without these guidelines.

A cycle with two or more tables cannot be delete-connected to itself. Consequently, the delete rule for at least two of the dependent tables in the cycle must be restrict.

Assume you want to execute the following statement.

Because of the relationships between the Faculty and Department tables, deleting a row from Faculty first deletes a row from Faculty, then from Department, where the cascaded delete stops because of the restrict rule on the name of the department.

The results could be inconsistent, depending on the order in which PSQL deletes rows from the Faculty table. If it attempts to delete the row in which the ID is 181831941, the delete operation fails. The restrict rule on the Department name prevents PSQL from deleting the first row in the department table in which the primary key value equals Mathematics, since the second row in Faculty continues to reference this row’s primary key.

If instead, PSQL deletes the Faculty rows in which the primary keys equal 179321805 and 310082269 first (in either order), all the rows in Faculty and Department are deleted.

Since the result of the example DELETE statement is consistent, no rows are deleted.

Delete rules from multiple delete-connected paths must be the same. Figure 2 shows an example of one anomaly that might occur without this guideline. In the figure, the arrows point to the dependent tables.

Faculty is delete-connected to Room through multiple delete-connected paths with different delete rules. Assume you want to execute the following statement.

The success of the operation depends on the order in which PSQL accesses Faculty and Department to enforce their delete rules.

To avoid problems, PSQL insures that the delete rules for both paths that lead to Faculty are the same.

This section demonstrates the table and referential constraint definitions on the sample database.

The following statement creates the Course table.

The following statement adds a primary key (Name) to the Course table.

The following statement creates the Student table and defines its referential constraints.

PSQL security capabilities allow you to protect data by limiting operations on data columns to particular users. These limits may range from allowing a user to see only certain columns in a table, to allowing them to see all the columns in a table, but not update them. PSQL makes no assumptions about database authorization based on operating system rights and permissions. By default, all users accessing a database through PSQL have complete read-write access to the data. You must enable and define database security to limit this access and protect the database from unauthorized update or access through PSQL.

PSQL security statements allow you to perform the following actions to limit access to your database:

Table 42 shows the rights you can grant to users and user groups.

You can assign certain types of rights over the whole database or for a particular database element. For example, when you assign the Update right to a user or user group, you can limit it to certain tables or to certain columns in tables. In contrast, when you assign the Create Table right to a user or user group, that user or user group has the Create Table right for the entire database. You cannot apply the Create Table right to a single table or column.

While the Create Table and Login rights apply to the entire database, all other rights apply to tables. In addition, you can apply Select and Update rights to individual columns in tables.

The following nine steps describe the general procedure for establishing security for a database.

You can use a SET SECURITY statement to enable security. In response, PSQL creates the master user, who has complete read-write access to the database. The password you specify with a SET SECURITY statement becomes the master password for the database.

The following example enables security for a database and specifies the password for the master user as Secure:

Passwords are case-sensitive.

When you enable security, PSQL creates the system tables X$User and X$Rights. Enabling security excludes all users except the master user from accessing the database until you explicitly create other users and grant them login rights.

After you enable security, your database has one user (Master) and one user group (PUBLIC). To provide other users access to the database, log in to the database as the master user and create users by name and password. You can also organize the users in user groups.

User names are case-sensitive in PSQL. Therefore, when you log in as the master user, you must specify the user name as Master.

To simplify security administration, you can organize users in user groups. You can create as many groups as you need, each with different rights and permissions, but each user in the database can belong to only one group. Once the user is in a group, that login inherits only the rights of that group plus the rights in the PUBLIC group, and any individual rights granted to that user are ignored. The rights of all users in a group are the same. To give a user unique rights, create a special group just for that user.

To create a user group, use a CREATE GROUP statement.

You can also create multiple user groups at once.

User group names are case-sensitive, cannot exceed 30 characters, and must be unique to the database. For more information about rules for naming user groups, see Advanced Operations Guide.

When you create a user for a database, PSQL enters the corresponding user name and password into the database’s security tables. To create a user, use a GRANT LOGIN TO statement. The following example creates the user Cathy and assigns Passwd as her password.

You can also assign a user to a user group when you create the user. For example, to assign the user Cathy to the Accounting group, use the following statement:

User names and passwords are case-sensitive. See Identifier Restrictions by Identifier Type in Advanced Operations Guide for permissible user name and password lengths and characters.

This topic explains how to grant rights to user groups and individual users.

All users automatically belong to the PUBLIC group, a special user group used to define the minimum set of rights for all users of a particular database. No user can have fewer rights than those assigned to the PUBLIC group. You cannot drop a user from the PUBLIC group, and you cannot revoke rights from a user if those rights are granted to the PUBLIC group.

By default, the PUBLIC group has no rights. To change the rights of the PUBLIC group, use a GRANT (access rights) statement. For example, the following statement allows all users of the sample database to query the Department, Course, and Class tables in the database:

After granting rights to the PUBLIC group, you can create other groups to define higher levels of access. You can also give individual users additional rights that differ from any other user or group, provided the user is not part of a group.

You can assign rights to a user group and add user names and passwords to the group. Doing so eliminates assigning each user’s rights individually. Also, security is easier to maintain if you assign security rights to groups, since you can change the rights of many users by granting new rights or revoking existing rights for an entire group at once.

To grant rights to a user group, use a GRANT (access rights) statement. For example, the following statement allows all users in the Accounting group to alter the Billing table definition in the sample database.

After you create a user, that user can log in to the database. However, the user cannot access data until you either place the user in a user group with rights or grant rights to the user.

To grant rights to a user, use a GRANT (access rights) statement. The following example allows the user John to insert rows into the billing table in the sample database.

To drop (delete) a user, use a REVOKE LOGIN statement.

This statement removes the user Bill from the data dictionary. After you drop a user, the user cannot access any tables in the database unless you disable security for the database.

You can also drop multiple users at once, as in the following example.

To drop a user group, follow these steps:

To revoke a user’s rights, use the REVOKE statement. The following example revokes the user Ron’s Select rights from the Billing table of the sample database.

To disable security for a database, follow these steps:

When you disable security for a database, PSQL removes the X$User and X$Rights system tables from the database and deletes the associated DDF files.

When you set up database security, PSQL creates the system tables X$User and X$Rights. Because the system tables are part of the database, you can query them if you have the appropriate rights.

For a reference to the contents of each system table, see System Tables in SQL Engine Reference.

The MicroKernel Engine and its automatic recovery functions handle the physical integrity of your database. PSQL provides logical data integrity using the transaction and record-locking capabilities of the MicroKernel Engine. PSQL, in conjunction with the MicroKernel Engine, provides the following types of concurrency controls:

Transaction processing lets you identify a set of logically related database modifications, either within a single table or across multiple tables, and require them to be completed as a unit. Transaction processing involves two important concepts:

The START TRANSACTION statement begins a transaction. When you have issued all the statements you want to complete during the transaction, issue a COMMIT WORK statement to end the transaction. The COMMIT WORK statement saves all your changes, making them permanent.

If an error occurs in one of the operations, you can roll back the transaction and then retry it again after correcting the error. For example, if you need to make related updates to several tables, but one of the updates is unsuccessful, you can roll back the updates you have already made so the data is not inconsistent.

PSQL automatically performs the rollback operation if two tasks are sharing a login session and the task that originated the session logs out before the second task completes its transition.

To begin a transaction, issue a START TRANSACTION statement in a stored procedure. After issuing all the statements you want to complete during the transaction, issue a COMMIT WORK statement to save all your changes and end the transaction.

For more information about the START TRANSACTION statement, see SQL Engine Reference.

In a SQL transaction, you can define additional markers called savepoints. Using savepoints, you can undo changes after a savepoint in a transaction and continue with additional changes before requesting the final commit or abort of the entire transaction.

To begin a transaction, use the START TRANSACTION statement. The transaction remains active until you issue a ROLLBACK or COMMIT WORK statement.

To establish a savepoint, use the SAVEPOINT statement.

To rollback to a savepoint, use the ROLLBACK TO SAVEPOINT statement.

The savepoint name must specify a currently active savepoint in the current SQL transaction. Any changes made after establishing this savepoint are cancelled.

To delete a savepoint, use the RELEASE SAVEPOINT statement.

You can only use this statement if a SQL transaction is active.

If you issue a COMMIT WORK statement, all savepoints defined by the current SQL transaction are destroyed, and your transaction is committed.

Savepoints provide a way to nest your transactions, thereby allowing the application to preserve the previous work in the transaction while it waits for a sequence of statements to complete successfully. As an example, you can use a WHILE loop for this purpose. You can set a savepoint before beginning a sequence of statements that may fail on the first attempt. Before your transaction can proceed, this sub-transaction must complete successfully. If it fails, the sub-transaction rolls back to the savepoint, where it can start again. When the sub-transaction succeeds, the rest of the transaction can continue.

A SQL transaction must be active when you issue a SAVEPOINT statement.

Work that is rolled back within a savepoint cannot be committed even if the outer transaction(s) completes successfully. However, work that is completed within a savepoint must be committed by the outermost transaction before it is physically committed to the database.

For example, in the sample database you might start a transaction to register a student for several classes. You may successfully enroll the student in the first two classes, but this may fail on the third class because it is full or it conflicts with another class for which the student has enrolled. Even though you failed to enroll the student in this class, you don’t want to undo the student’s enrollment for the previous two classes.

The following stored procedure enrolls a student into a class by first establishing a savepoint, SP1, then inserting a record into the Enrolls table. It then determines the current enrollment for the class and compares this to the maximum size for the class. If the comparison fails, it rolls back to SP1; if it succeeds, it releases savepoint SP1.

For more information about the syntax of any of these statements, see the topics for these statements in SQL Engine Reference.

Transactions do not affect the following operations:

If you attempt any of these operations within a transaction and PSQL completes the statement, then you cannot roll back the results.

You cannot alter or drop a table (in other words, change its dictionary definition) during a transaction if you have previously referred to that table during the transaction. For example, if you start a transaction, insert a record into the Student table, and then try to alter the Student table, the ALTER statement fails. You must commit the work from this transaction, and then alter the table.

An isolation level determines the scope of a transaction locking unit by allowing you to define the extent to which a transaction is isolated from other users, who may also be in a transaction. When you use isolation levels, PSQL automatically locks pages or tables according to the isolation level you specify. These automatic locks, which PSQL controls internally, are called implicit locks, or transaction locks. Locks that an application specifies explicitly are called explicit locks, formerly record locks.

PSQL offers two isolation levels for your transactions :

You set the isolation level using the ODBC API SQLSetConnectOption.

When you use the exclusive isolation level, the locking unit is an entire data file. Once you access a file or files within an exclusive transaction, those files are locked from any similar access by any other user in a transaction. This type of locking is most effective when few applications attempt to access the same tables at the same time, or when large parts of the file must be locked in the course of a transaction.

PSQL releases the lock on the file or files when you end the transaction. When you access a table during an exclusive transaction, the following conditions take effect:

When you access tables through a joined view using the exclusive isolation level, PSQL locks all the accessed data files in the view.

The MicroKernel Engine maintains data files as a set of data pages and index pages. When you use the cursor stability isolation level, the locking unit is a data page or index page instead of a data file. When you read records within a cursor stability transaction, PSQL locks the data pages that contain those records for possible update, but allows concurrent access to a table by multiple tasks within transactions. These read locks are released only when you read another set of records. PSQL supports set level cursor stability since it allows an application to fetch multiple records at a time.

In addition, any data modifications you make to the data or index pages cause those records to remain locked for the duration of the transaction, even if you issue subsequent reads. Other users in a transaction cannot access these locked records until you commit or roll back your work. However, other applications can lock other pages from the same files within their own transactions.

When you access a file during a cursor stability transaction, PSQL locks data and index pages as follows:

Cursor stability ensures that the data you read remains stable, while still allowing other users access to other data pages within the same data files. Within the cursor stability isolation level, you can generally achieve greater concurrency for all tasks by limiting the number of rows you read at one time, thereby locking fewer data pages at a time. This allows other network users access to more pages of the data file, since you do not have them locked.

However, if your application is scanning or updating large numbers of rows, you increase the possibility of completely locking other users out of the affected tables. Therefore, it is best to use cursor stability for reading, writing, and committing small transactions.

Cursor stability does not lock records within a subquery. Cursor stability does not guarantee that the conditions under which a row is returned do not change, only that the actual row returned does not change.

Whenever you access data within a transaction, PSQL locks the accessed pages or files for that application. No other application can write to the locked data pages or files until the locks are released.

Using the cursor stability isolation level, when you access tables through a joined view, PSQL locks all the accessed pages for all the tables in the view. Using the exclusive isolation level, when you access tables through a joined view, PSQL locks all the accessed tables in the view.

PSQL performs no-wait transactions. If you try to access a record that another task has locked within a transaction, PSQL informs you that the page or table is locked or that a deadlock has been detected. In either case, roll back your transaction and begin again. PSQL allows multiple cursors in the same application to access the same data file.

The following steps illustrate how two applications interact while accessing the same tables within a transaction. The steps are numbered to indicate the order in which they occur.

Since a transaction temporarily locks records, pages, or tables against updates by other applications, an application should not pause for operator input during a transaction. This is because no other application can update the records, pages, or tables accessed in the transaction until the operator responds and the transaction is terminated.

A deadlock condition occurs when two applications are retrying operations on tables, data pages, index pages, or records that the other one has already locked. To minimize the occurrence of deadlock situations, have your application commit its transactions frequently. Do not attempt to retry the operation from your application; PSQL attempts a reasonable number of retries before returning an error.

When you use the exclusive isolation level, PSQL locks the entire data file against updates by other applications; thus, it is possible for a deadlock to occur if your applications do not access data files in the same order, as shown in the following table.

When you use the cursor stability isolation level, other applications can read and update records or pages in the file you are accessing (records or pages that your application has not locked).

If your application performs single record fetch and update sequences that are not logically connected, you can use PSQL’s passive method of concurrency. Using this method, you can fetch and update (or delete) records without performing transactions or record locks. These operations are referred to as optimistic updates and deletes.

By default, if your task does not use transactions or explicit record locks to complete update and delete operations, your task cannot overwrite another task’s changes. The feature that ensures this data integrity is passive control, sometimes referred to as optimistic concurrency control. With passive control, your task does not perform any type of locking. If another task modifies a record after you originally fetched it, you must fetch the record again before you can perform an update or delete operation.

Under passive control, if another application updates or deletes a record between the time you fetch it and the time you issue an update or remove operation, your application receives a conflict status. This indicates that another application has modified the data since you originally fetched it. When you receive a conflict status, you must fetch the record again before you can perform the update or remove operation.

Passive control allows an application that was designed for a single-user system to run on a network without implementing lock calls. However, passive control is effective only when an application is operating in a lightly used network environment or on files in which the data is fairly static. In a heavily used environment or on files that contain volatile data, passive control may be ineffective.

The principle of atomicity states that if a given statement does not execute to completion, then it should not leave partial or ambiguous effects in the database. For example, if a statement fails after it has inserted three out of five records but does not undo that insert, then the database is not in a consistent state when you retry the operation. If the statement is atomic and it fails to complete execution, then all changes are rolled back, so that the database is in a consistent state. In this example, if all five records are not successfully inserted, then none of them are inserted.

The atomicity rule is especially significant for statements that modify multiple records or tables. It also makes retrying failed operations simpler, because any previous attempt is guaranteed not to have left any partial effects.

PSQL enforces atomicity in two ways:

Because triggers are always initiated by an external data change statement (INSERT, DELETE, or UPDATE), and all data change statements are defined to be atomic, the following statement are not allowed in triggers or in any procedures invoked by triggers:

In other words, triggers follow the same rules as ATOMIC compound statements.

No user-initiated COMMIT WORK, ROLLBACK WORK, RELEASE SAVEPOINT, or ROLLBACK TO SAVEPOINT statement can cause a system-begun transaction (for purposes of atomicity) to end.