Using Constraints in 4.5
     PRODUCT: R:BASE            VERSION: 4.5
     One of the new features in 4.5 is constraint support. Constraints 
     provide automatic data integrity and referential integrity features. 
     In previous versions of R:BASE, data integrity and referential 
     integrity had to be enforced through rules and programming. In 4.5, 
     they are a part of the database structure through constraints and are 
     automatically enforced. The constraints that can be defined are 
     primary key, foreign key, not null and unique.
     Primary key/Foreign key
     *  primary key
     A primary key is the column, or set of columns, that uniquely 
     identifies a row in a table. In other words, it is a set of values 
     that distinguishes one row from another. This could be something like 
     an employee id column in an employee table or it could be a 
     combination of the customer's id and the customer's phone number. 
     Every table will have a column or set of columns that identify a row, 
     but not every table will have a defined primary key. A primary key 
     cannot be defined if any one of the columns included in the desired 
     key already have null or duplicate values. Defining a primary key 
     automatically enforces not null, unique constraints on the column. A 
     primary key definition can be used instead of a rule to prevent 
     duplicates, and will be faster. R:BASE automatically builds an index 
     on the specified column(s) when a primary key is defined.
     *  foreign key
     Like a primary key, a foreign key is a column or a group of columns. 
     A foreign key matches a defined primary key, i.e. the values in a 
     foreign key reference values in the primary key. A primary key can 
     exist without a foreign key, but a foreign key cannot exist without a 
     primary key. A foreign key is always defined to reference a primary 
     key. A foreign key automatically checks that the values in the foreign 
     key exist in the referenced primary key. A foreign key replaces a 
     verify a value rule. An index is automatically built when a foreign 
     key constraint is defined. 
     Primary and foreign keys must match in terms of the specified columns. 
     If you have a multi column primary key, you can not have single column 
     foreign keys reference it. If you have a multi column foreign key, it 
     cannot reference a single column primary key. When a primary key is 
     defined as more than one column, those columns are treated as a whole. 
     Primary and foreign keys must match exactly. Thus if your primary key 
     is defined as full-text, then the corresponding foreign keys referencing 
     it must also be full-text.
     Primary and foreign keys automatically preserve referential integrity. 
     You cannot delete a row from a table with a defined primary key if 
     there are referenced foreign keys, thus you can never have detail 
     records without a matching master record. You can delete a row from a 
     table with a foreign key. You cannot add a row to table with a foreign 
     key defined unless the value entered matches a value in the referenced 
     primary key. You cannot update a primary key value if there are 
     references, thus you are ensured that linking columns always match. 
     You can update primary key values, if there are not matching values in 
     the referenced foreign key. 
     Not Null
     Placing a not null constraint on a column requires that the data in 
     the column must contain a value, it cannot be null. This prevents 
     users from adding a "blank" value to a table. A not null constraint 
     cannot be added if the column already contains null values. The null 
     values must first be edited to actual data values, then the not null 
     constraint can be added. A not null constraint can replace a "Require 
     a value" rule. R:BASE does not build an index for a not null 
     constraint, but since it stores the not null as part of the column 
     definition, it is able to check the constraint faster than it could 
     check the rule.
     A unique constraint requires that the data values in the column be 
     unique, i.e. the column cannot contain duplicate values. By 
     definition, the column must also be defined as NOT NULL. A unique 
     constraint can replace a "Require a unique value" rule. A unique 
     constraint also automatically builds an index. The unique constraint 
     can only be defined through the R> prompt using either the CREATE 
     TABLE or ALTER TABLE command. For example,
     ALTER TABLE tblname ALTER COLUMN colname datatype NOT NULL UNIQUE
     Benefits of Constraints
     Constraints provide automatic, database wide data integrity and 
     referential integrity. The not null and unique constraints restrict 
     data entry. No matter what command is used to enter data, the 
     constraint verifys that the specified column has a value and the value 
     is unique. The primary, foreign key constraints provide both data 
     integerity and referential integrity. The primary key column is 
     automatically not null and unique. Deletions to a table with a defined 
     primary key are automatically restricted if a referenced foreign key 
     is defined. A value cannot be entered into a table with a foreign key 
     unless that value exists in the referenced primary key table. In 
     previous versions of R:BASE rules could be used to force these 
     Using rules to enforce these same data constraints required several 
     different rules. You would need a rule to prevent duplicates (replaced 
     by the primary key or unique constraint), verify a value rules 
     (foreign key), and require a value rules (not null). In addition, you 
     would need to define delete rules to prevent rows being deleted from 
     the primary key table if there were matching rows in the foreign key 
     table. If you wanted to prevent users from changing a primary key 
     value that exists in the foreign key table, you would need to define 
     an additional verify a value rule. Constraints are quicker and easier 
     than rules. Unlike rules, constraints cannot be turned off, they are 
     always checked for. Constraints also provide much faster performance 
     than rules, but can use more disk space since constraints are enforced 
     by using indexes. Many rules, however, also required the rule column 
     to be indexed for performance. It is recommended where possible to use 
     constraints over rules for faster performance and ease of use. Rules 
     are still needed to check other conditions, such as checking for 
     specific values or a range of values.
     Defining Constraints
     Constraints are defined using the Info Create/modify menu or through 
     the CREATE TABLE or ALTER TABLE commands. Primary key, foreign key and 
     not null constraints can be defined through the menus by just 
     selecting the desired tables and columns from a menu. Unique 
     constraints can only be defined from the R> prompt. Defining a primary 
     key or unique constraint from the R> prompt using CREATE TABLE or 
     ALTER TABLE commands requires the column be explicitly defined as NOT 
     NULL as well as the unique or primary key designation. However, 
     defining a primary key from the menus does not require the explicit 
     NOT NULL, the menus do it for you automatically. For example,
     CREATE TABLE tblname (colname datatype NOT NULL PRIMARY KEY, ...)
     Before defining primary and foreign key constraints, decide which 
     column or columns and which tables are best suited for the primary 
     key. The column or columns selected should be the ones that uniquely 
     identify the row from other rows in the table, and the table should be 
     the one where that value is first entered into the database and is 
     unique. Linking columns are generally good candidates for primary, 
     foreign key constraints. For example, in the Concomp sample database, 
     the empid column is used to link data in the Employee table with data 
     in the Transmaster table and data in the Salesbonus table. Since a 
     record is first entered into the Employee table, and when data is 
     entered into the Transmaster or Salesbonus tables a matching record 
     must already exist in the Employee table, the empid column in Employee 
     becomes the primary key. The empid columns in Transmaster and 
     Salesbonus become foreign keys referencing the Employee table primary 
     What benefit is gained from designating empid as a primary key in the 
     Employee table and foreign keys in the Transmaster and Salesbonus 
     tables? By this designation you are protecting your data from 
     inadvertent changes. The empid value in Employee cannot be changed 
     when there is a matching row in the Transmaster or Salesbonus table. 
     Rows cannot be deleted from either table. When adding data to 
     Transmaster or Salesbonus you automatically require a matching value 
     in Employee without having to define a rule.
     Listing Constraints
     LIST CONSTRAINTS from either the menu or the R> prompt shows primary 
     key, foreign key and unique constraints. The constraint ID, the type 
     of constraint, the table name, and table references if the key was a 
     foreign key are displayed. Not null constraints are not displayed.
     Id     Type                          Table Name         References
     ------ ----------------------------- ------------------ ---------------
     #23    UNIQUE                        product
     #26    PRIMARY KEY REFERENCED        employee
     #28    FOREIGN KEY                   transmaster        employee
     #27    FOREIGN KEY                   salesbonus         employee
     To see not null constraint information, you use the LIST TABLE command 
     at the R> prompt. That also shows primary and foreign key and unique 
     LIST TABLE employee
     Table: employee
     Descr: Employee information
     No. Column Name        Attributes
     --- ------------------ -----------------------------------------------
       1 empid              Type   : INTEGER
                            Consrnt: PRIMARY KEY REFERENCED
                            Comment: Employee identification number
       2 emptitle           Type   : TEXT 30
                            Comment: Employee job title
       3 empfname           Type   : TEXT 10
                            Comment: Employee first name
     LIST TABLE salesbonus
     Table: salesbonus
     Descr: Sales bonus information
     No. Column Name        Attributes
     --- ----------- ------------------------------------------------------
       1 empid              Type   : INTEGER
                            Consrnt: FOREIGN KEY REFERENCES employee
                            Comment: Employee identification number
       2 transdate          Type   : DATE     NOT NULL
                            Comment: Transaction date
       3 netamount          Type   : CURRENCY
                            Comment: Net amount of transaction
       4 bonuspct           Type   : REAL
     LIST TABLE product
     Table: product
     Descr: Model information, including list price
     No. Column Name        Attributes
     --- ----------- ------------------------------------------------------
       1 model              Type   : TEXT 6 NOT NULL
                            Consrnt: UNIQUE
                            Comment: Product model number
       2 prodname           Type   : TEXT 35
                            Comment: Product name
     Removing Constraints
     A primary key constraint that is referenced by a foreign key cannot be 
     removed until the foreign key constraint has first been deleted. If a 
     column was first defined as not null, and then as a primary key, the 
     not null constraint on the column cannot be removed until the primary 
     key constraint has been removed.  Removing a primary key constraint 
     does not remove the NOT NULL part of the constraint.  That must be 
     removed seperately.
     When primary key, foreign key and not null constraints are defined, 
     custom violation messages can be entered. The messages cannot be added 
     or modified after the constraint is defined. The constraint must be 
     deleted and re-defined to add or modify custom messages.