=====================================================================
Using Constraints in 4.5
=====================================================================
PRODUCT: R:BASE VERSION: 4.5
=====================================================================
AREA: INTEGRITY CATEGORY: DB DESIGN, STRUCTURE, INTEGRITY
=====================================================================
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.
Unique
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
constraints.
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
key.
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.
LIST CONSTRAINTS
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
constraints.
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.
Messages
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.