The "Key/Indexes" option allows users to create table constraints; such as Primary Keys, Foreign Keys and Unique Keys. Users can also create Indexes, which provide faster data retrieval for the database.
New Key - allows you to create a new key/index
Delete Key - deletes the currently selected key/index. If you try to delete a key which is referenced by another table, you will receive an error message about releasing the dependencies first.
Save - commits any changes made since the last save
Save with "NOCHECK" - commits any changes made since the last save, and does not update references to views, tables, and columns in forms, reports, labels, access rights, and rules. In this case, the user assumes the responsibility to update any references to views, tables, and columns in forms, reports, labels, access rights, and rules.
Help - opens the Data Designer Help
Save and Close - commits any changes made since the last save and closes the Data Designer module
Save and Close with "NOCHECK" - commits any changes made since the last save, closes the Data Designer module, and does not update references to views, tables, and columns in forms, reports, labels, access rights, and rules. In this case, the user assumes the responsibility to update any references to views, tables, and columns in forms, reports, labels, access rights, and rules.
Close - closes the Data Designer. If unsaved changes have been made, you will be prompted to save them before closing.
|
|
When implementing keys or indexes, you must know the differences between the options available to you.
Keys
To control the data that enters your database, you can apply constraints. By applying a constraint to a column, you can prevent irreconcilable and empty data from being entered. Constraints cannot be turned off and are always enforced; you must delete the constraint if you do not want it. R:BASE uses the following constraints:
•Primary Key - A column or set of columns that uniquely identify a row; in other words, each value in a primary key column is unique. A primary-key constraint prevents duplicate (non-unique) and null values from being entered. Even if you do not specifically define a constraint, all tables (in a well-designed database) should have a primary key. You can define one primary key per table.
•Unique Key - A column or set of columns that uniquely identify a row; in other words, each value in a unique key column is unique. A unique-key constraint prevents duplicate (non-unique) and null values from being entered. The only difference between a unique key and a primary key is that you can define multiple unique keys per table.
•Foreign Key - A column or set of columns that match values in a particular primary key or unique key defined in a different table. A value cannot be inserted or changed in a foreign key if it does not already exist in the primary key. Not all tables have foreign keys.
Indexes
The R:BASE index is similar to an index in a book; both indexes allow you to find information faster. Instead of searching through a book page by page, you can look up the topic in the index and find the exact page number of the topic. Similarly, you can apply indexes to columns so that R:BASE finds data faster.
When you apply an index to a column, R:BASE records the location of every value in that column. Then, when you look for or sort information in the column, R:BASE uses the index to find the rows you need quickly. For example, you want to list the bonuses that employee 102 earned; if the Empid column in the SalesBonus table is indexed, R:BASE finds and searches that column faster. Indexes are most useful when you have tables with many rows.
You can apply the following types of indexes:
•Unique index - Ensures that the values entered in the indexed column are unique. A unique index may be case sensitive.
•NOT NULL index - The index is populated with references to rows where the column value is not NULL. NULL values in the column are not added to the index. Not NULL indexes are ideal for columns which contain a considerable amount of NULL values, and where the not-NULL data is ideal for index retrievals. If a WHERE clause includes "ColName IS NULL" then the index is not used.
•Full or partial text index - For columns with NOTE or TEXT data types. R:BASE preserves each character in the indexed column (a full text index). Or, you can specify the number of characters to preserve, and R:BASE hashes (converts characters to a 4-byte integer) the remaining characters (a partial index).
•Multi-column index - A combination of up to 8 columns in one index. For example, if you consistently search three columns when working with a certain database, you can define a separate index for each column. Or, you can define one index for all three columns--R:BASE searches a multi-column index faster than three separate indexes.
Duplicate Factor - the computed duplicate factor used by R:BASE during retrieval to guess the fastest way to find the result set. This number is the average number of times each value appears in the column. A number of 1.0 means that values are never duplicated, or always unique. Zero means that the value is unknown. The higher the number, the less efficient the index. See also: Index Efficiency
Adjacency Factor is the computed adjacency factor used by R:BASE during retrieval to guess the fastest way to find the result set. It is the estimate of the probability that two rows with similar index values will be physically located together in data file (.RX2). A higher number means more efficient retrieval when reading rows in index order. Zero means that the value is unknown.
The duplicate and adjacency factor values are computed when indexes are first created and when rebuilt or reloaded, during a PACK or RELOAD.
Notes:
•Primary, unique, and foreign key columns are indexed automatically. So, if you create a key for any columns, do not add an index.
•To keep the .RX3 file from becoming too large, use a partial index--specify enough characters to guarantee the values are unique. If the preserved values are not unique, R:BASE must unhash the values before it can identify the rows, which slows performance. If you do not specify the number of characters to preserve, R:BASE preserves all of them, unless there are more than 196 characters defined; then, R:BASE preserves the first 32 and hashes the rest.
•For multi-column indexes, you can add up to 8 columns, but for primary key, foreign key, or unique key definitions, you can only select one column.
