Computer Representations of Molecular Structures - Design and Use of Relational Databases in Chemistry - page 88

write a function to correct an invalid SMILES, so a trigger to do so would
not be effective. A domain is defined with a simple Boolean check con-
straint to allow or disallow a value. This is just what is need for a smiles
domain and smiles data type. On the other hand, canonical SMILES is just
a type of SMILES. It is very common that a user will attempt to insert a
valid SMILES into a canonical SMILES column. This should be forbidden,
but there is a function, cansmiles that can produce a valid canonical
SMILES from a valid SMILES. A trigger is a good solution to this common
situation. The check constraint on a cansmiles column doubly ensures
that only canonical SMILES is allowed into that column.
There is another reason to avoid using a cansmiles domain : There is
an interaction between the use of domains and triggers. A domain check
constraint takes priority over the trigger. In other words, if one attempts
to insert an invalid canonical SMILES into a column defined using the
cansmiles domain , the insert may fail, even though there is a trigger on
the table. This is because the check constraint of the domain forbids the
insert before the trigger is applied by the RDBMS. The recommended
solution to this is to define the cansmi column using the Text data type,
use a check constraint on that column and a trigger on the table con-
taining the cansmi column.
In summary, the domain check occurs first, then the trigger and
finally the column constraint check . So, whenever a trigger is used to
attempt to correct a value being inserted or updated, a domain check
constraint should not be used. Instead a column check constraint should
be used.
7.11 Summary
The use of a few new SQL functions can greatly enhance the way chemi-
cal structures are used in a relational database. These functions allow the
SMILES text string to store structures and canonical SMILES to create a
unique text representation of a specific chemical structure. SMARTS text
strings are used to search SMILES strings in a way comparable to how
regular expressions are used to search ordinary text strings. Functions
that convert to and from SMILES and common chemical structure file for-
mat expand the kinds of chemical data the database can handle. Finally,
the use of domains, triggers, and column check constraints can improve
the integrity of the data in a database.
References
1. Pauling, L. 1939. The nature of the chemical bond and the structure of molecules
and crystals, 3rd ed. Ithaca, NY: Cornell University Press.
2. Sainsbury, M. 1992. Aromaticity . New York, Oxford University Press, Inc.