Interactions Between Btrieve and Relational Constraints
While PSQL is designed to support concurrent access to the same data through both the Relational Engine and the MicroKernel Engine, some features of the relational (SQL) database architecture may interfere with Btrieve access to the data. For example, features that are designed to limit relational access, such as Referential Integrity, may also limit Btrieve access in the interest of preserving data integrity.
You should fully understand Integrity Enforcement, Bound Databases, ODBC/SQL Security, Triggers, Referential Integrity and Owner Names before implementing these features on a database that is used by a transactional (Btrieve) application. In most cases you can get “Best of Both Worlds” access to your database, but since Security, Referential Integrity, and Triggers can put constraints on access or operations, some Btrieve operations may be restricted or prevented depending on the implementation.
In some cases using Integrity Enforced, Bound Databases, Security, Triggers, or Referential Integrity may cause Btrieve access to the data and/or file to be restricted or prevented when Btrieve access would violate the bounds and restrictions placed on that data. Triggers and RI mainly limit the ability to manipulate data via the Btrieve API.
Security and owner names can limit access and/or the ability to manipulate that data without the proper account, rights, and password. There are many possible combinations of these features, so only the most common ones are listed here.
Table 21
Interactions Between Relational Restrictions and Btrieve Access
CONDITIONS
RESULTS
DDFs Exist?
Integrity Enforced?
Bound Database?
Relational Security Used?
Triggers Used?
RI Used?
Btrieve Access allowed?
SQL/ODBC Access Allowed?
No
-
-
-
-
-
Yes
No
Yes
No
No
No
No
-
Yes
Yes
Yes
No
No (1)
No
Yes (2)
-
Yes (2)
Yes
Yes
Yes
No
No
No
No
Yes
Yes
Yes
Yes
No
Yes
No
No
Yes (3)
Yes (3)
Yes
Yes
No (1)
Yes
No
Yes
Yes (4)
Yes (3) (4)
Yes
Yes
No (1)
Yes
Yes (2)
No
Yes (1)
Yes (3)
Yes
Yes
Yes
No
No
No
Yes
Yes
Yes
Yes
Yes
Yes
No
No
Yes (3)
Yes (3)
Yes
Yes
Yes (1)
Yes
No
Yes
Yes (2) (4)
Yes (3) (4)
Yes
Yes
Yes (1)
Yes
Yes (2)
No
Yes (2) (3)
Yes (3)
(1) Regardless of the Bound Database setting for a database, the database engine automatically stamps a data file as bound if it has a trigger, a foreign key, or a primary key that is referenced by a foreign key. For more information on the meaning of a bound database or file, see Bound Databases.
(2) Adding triggers on a table prevents access to that file from the Btrieve API, for any operations that would cause the trigger to execute. Because triggers do not react to database operations coming through the Btrieve interface, this lock-out behavior preserves the consistency of the data. See Bound Database versus Integrity Enforced for more information.
(3) When a database or file is secured, access is allowed as long as the user has permissions (that is, a relational user name and password or a valid Btrieve owner name) to that file. Files that are in a secure database but do not have Btrieve owner names set are accessible to Btrieve users. When relational security is first set up for a file that already has a Btrieve owner name, the Master user must grant relational permissions to users using the file’s Btrieve owner name. See PSQL Security for more information.
(4) If a table contains referential integrity constraints, and Integrity Enforced is turned on for the given database, both Btrieve and SQL operations that would violate the constraints are disallowed. This mechanism preserves the integrity of the data regardless of the method of access.
Bound Database versus Integrity Enforced
If you do not specify the attribute Integrity Enforced for a named database, the database engine does not enforce any referential integrity, triggers, or security rules. If you specify the attribute Integrity Enforced for a named database, the MicroKernel enforces the database’s defined security, RI, and triggers, regardless of the method you use to access the data. The MicroKernel enforces these rules as follows:
•
Btrieve users are not subject to relational security. If you have owner names on the files, they remain in effect. If you do not have owner names on the files, any Btrieve user can access the data regardless of relational security constraints. Btrieve operations are subject to all the RI and other constraints defined in the database as well as the Trigger restrictions listed below.
•
If constraints exist on a given file, Btrieve access is permitted as follows:
Table 22
Constraints on Btrieve Access - Integrity Enforced
Constraint on File
Level of Access Permitted Using Btrieve
RI constraints defined
User can access the data and perform any operations within RI constraints.
INSERT triggers defined
Read-only, update, and delete operations permitted.
UPDATE triggers defined
Read-only, insert, and delete operations permitted.
DELETE triggers defined
Read-only, update, and insert operations permitted.
If more than one constraint exists on the bound file, the access level follows the most restrictive constraint or combination of constraints. For example, if a file has both INSERT and UPDATE triggers defined, then Btrieve users have only read-only and delete access.
The Integrity Enforced setting is not directly related to the Bound Database setting. A database can be bound without enforced integrity, or a database can have integrity enforced without being bound.
Bound Databases
If you specify the attribute Bound for a named database, the DDFs and data files in that database cannot be associated with any other database. Also, a bound data file cannot be associated with more than one table definition in the database. When you add new tables or DDFs to a bound database, the database engine automatically binds the new objects. This behavior prevents conflicts that could cause unpredictable behavior or data integrity corruption. For example, if you used the same data file for two different table definitions in the same database, you could define RI rules on one table but not on the other. In this case, inserting a row into the table without the RI rules would violate the RI rules on the other table. Binding the data files and DDFs prevents such conflicts.
DDFs and data files can be individually bound. The database engine automatically marks a data file as bound if it has a trigger, has a foreign key, or has a primary key that is referenced by a foreign key. These files cannot be shared with another database or associated with more than one table definition.
Whether a data file is bound has no direct affect on Btrieve access to that data file. However, files that are bound often have other constraints that may limit Btrieve access.
See Also
For information on how to manipulate the Integrity Enforced and Bound Database settings for a given database, see New Database GUI Reference.