Interactions Between Btrieve and Relational Constraints

---

While Pervasive.SQL is designed to support concurrent access to the same data through both the SQL/ODBC interface and the Btrieve interface, 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 6-2 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 interface, 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 below 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 Pervasive.SQL 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 6-3 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.

"Integrity Enforced" is not directly related to "Bound Database." 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 Pervasive.SQL Database GUI Reference .