<< Click to Display Table of Contents >> Navigation:  Application development > Creating tables > Integrity constraints

When creating a table by using the BDE function DbiCreateTable, you can use integrity constraints to ensure that references in the key fields of secondary tables (in the same database) or foreign tables (in another database) are maintained to key fields in a primary table. For example, if several tables have keys referencing the primary key Customer ID in the Customer table, then this dependency must be checked so that referenced customer IDs cannot be deleted, thereby orphaning records in secondary or foreign tables.

Primary key and foreign key integrity constraints are implemented wherever supported by SQL servers such as:

•Sybase system 10

•Microsoft SQL server 6.0

•InterBase 5

•Oracle 6.0 (syntax only, not enforced)

•Oracle 8

•DB2 2.1.1

•Informix 7.11

•Informix 9

Primary key support

1.Decide which fields or set of fields will act as the primary key for the table to be created. For a dBASE table, choose the index that will act as the primary key for the table to be created.

2.Put this information in an IDXDesc structure with these columns.

3.Set IDXDesc.bPrimary = TRUE;

4.Attach the IDXDesc structure to a CRTBLDesc.pidxDesc pointer.

Primary key columns must be NOT NULL, which means you should have VCHKDesc for each column with VCHKDesc.bRequired = TRUE. The exception is Paradox which can have one blank record.

There can be only one primary key per table.

A table with primary key constraint (table level) is created and an unique index (ascending) on these columns is also created. For dBASE, any index can be used, whether ascending or descending. For remote databases, this index can neither be added nor dropped by using CREATE INDEX or DROP INDEX. The index will be created when table is created and will go away when the table is dropped. (In Local SQL, you can drop the primary index by using this statement: "DropIndex TABLENAME.PRIMARY".)

Foreign key support

1.Decide which table (the other Table) is going to be referenced by the table (this table) being created. It could be the same table if supported by the server.

2.Decide which columns of this table reference the other tables columns

3.Decide what should be the referential action for Delete. If cascading is required, set RINTDesc. eDelOp = rintCASCADE. (This is supported by ORACLE 7.x and Sybase System 10.)

4.Put this information in the RINTDesc structure.

5.Attach the RINTDesc to CRTBLDesc.printDesc pointer

There can be more than one referential (foreign key) integrity constraint.

Schema retrieval/integrity constraints

Primary key: Any of index retrieval functions and check if pIdxDesc->bPrimary = TRUE.

Foreign Key: Use BDE function DbiGetRINTDesc.