Data Storage, Searching, and Manipulation - Design and Use of Relational Databases in Chemistry - page 62

from being entered, each with a different compound _ id . The next chap-
ter shows how using uniqueness constraints and SMILES can ensure that
a compound occurs only once in a table.
The foreign key constraint is used routinely in a schema (or sche-
mas) with many related tables. Usually one table is defined to hold the
primary unique key, for example, compound _ id . Other tables are then
defined using compound _ id as a foreign key. This requires that every
compound _ id in the foreign key table have a corresponding entry for that
compound _ id in the primary key table. This use of primary and foreign
keys forms the foundation on which the table relations and joins among
relational tables are defined. The foreign key constraint can also ensure
that data in the primary table cannot be accidentally deleted, since this
would leave the rows in the foreign key table(s) orphaned with no corre-
sponding primary key. The foreign key constraint does not require that the
values be unique. There may be many rows with the same compound _ id
in a table where compound _ id is defined as a foreign key. For example,
consider a table ic50 with compound _ id as a foreign key. There may be
many measures of ic50 for the same compound _ id all stored in the same
table ic50 . There may also be many other tables of experimental and theo-
retical data using the same compound _ id to store various data.
It is also possible to define other constraints on data in tables. For
example, molecular weight might be stored as a numeric value. While
the standard SQL numeric constraint applies, it might be desirable to also
constrain molecular weight values to be positive values. This can be read-
ily done after the column is defined using the following SQL.
Alter Table compound Add Constraint mw_check Check (mw > 0);
More elaborate constraints are also possible. For example, the chemical
abstracts (CAS) number is composed of three integers. In order for a CAS
number to be valid, the first integer must have no more than six digits,
the second must have two digits, and the third integer is a checksum.
For example, the CAS number for caffeine is 58-08-2. The values 58 and
08 are arbitrarily assigned by Chemical Abstracts, and the 2 is the proper
checksum computed as (8*1 + 0*2 + 8*3 + 5*4) % 10, where % represents
the modulo function. The following function will validate a CAS number
based on input of the three integers.
Create Function valid_cas(integer, integer, integer) Returns boolean
As 'Select $1 < 1000000 And $2 < 100 And $3 < 10 And
$3 = (
($2 % 10 * 1) +
($2 / 10 * 2) +
($1 % 10 / 1 * 3 ) +
($1 % 100 / 10 * 4 ) +