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

7.10 SMILES as an SQL Data Type
The standard SQL data type Text has been used to store SMILES. This is
appropriate because every SMILES is a valid text string. But not every text
string is a valid SMILES. Without additional information about SMILES,
the RDBMS cannot enforce any rules about which text strings ought to be
in a column intended to contain SMILES.
7.10.1 Domains
The SQL domain allows one to define which values are to be allowed in a
particular column of a table. A domain is created by stating the underly-
ing built-in SQL data type used to store the domain data type. In addition,
a check constraint function may be used to allow or forbid certain values.
This can be used to great advantage for SMILES and canonical SMILES.
Using a domain improves the ability of the RDBMS to maintain the integ-
rity of the data contained in its tables.
The following SQL defines a domain data type smiles.
Create Domain smiles As Text Check (valid(Value));
The use of the keyword Value is required. Value refers to the value of the
data element, here the SMILES. Once this domain is created, it can be used
as a data type in the creation of a table. For example:
Create Table atable (id Integer, smi smiles, mw Numeric);
When a value is inserted into this table, the valid function will be called
by the RDBMS. If the function returns true, then the value will be allowed
into the column smi. Otherwise, an SQL error will be reported and the
value will not be allowed.
Using a domain like this, the smiles data type behaves much like a
standard data type. When one attempts to insert an invalid number into
a numeric column, an SQL error is reported and the value is not inserted.
This fundamental behavior of an RDBMS is readily extended to SMILES
using a domain .
The check constraint used in the creation of a domain is similar to the
check constraint used in the creation of a table. For example, it would be
possible to simply
Create Table atable ( id Integer, smi Text Check(valid(smi)) );
This would ensure that the column smi could contain only a valid SMILES.
If this is the only table in which a SMILES column is used, this approach