Filter
Top Products
CHAPTER 7 Ensuring Data Integrity
279
By default, string comparisons are case insensitive unless the database is
explicitly created as a case-sensitive database, using the
CASE RESPECT
option.
Example 3
You can specify that a date or number falls in a particular range. For example,
you may want to require that the
start_date column of an employee table must
be between the date the organization was formed and the current date, as in the
following:
ALTER TABLE employee
MODIFY start_date
CHECK ( start_date BETWEEN ’1983/06/27’
AND CURRENT DATE ) UNENFORCED
You can use several date formats: the YYYY/MM/DD format used in this
example has the virtue of always being recognized regardless of the current
option settings.
Column CHECK conditions from user-defined data types
You can attach unenforced CHECK conditions to user-defined data types.
Columns defined on those data types inherit the CHECK conditions. A
CHECK condition explicitly specified for the column overrides that from the
user-defined data type.
When defining a CHECK condition on a user-defined data type, any variable
prefixed with the @ sign is replaced by the name of the column when the
CHECK condition is evaluated. For example, the following user-defined data
type accepts only positive integers:
CREATE DATATYPE posint INT
CHECK ( @col > 0 ) UNENFORCED
Any variable name prefixed with @ could be used instead of @col. Any
column defined using the
posint data type accepts only positive integers unless
it has a CHECK condition explicitly specified.
An ALTER TABLE statement with the DELETE CHECK clause deletes all
CHECK conditions from the table definition, including those inherited from
user-defined data types.
For information on user-defined data types, see “User-defined data types” in
the Adaptive Server IQ Reference.