Sybase 12.4.2 manual Column Check conditions from user-defined data types, Option, 279

	CHAPTER 7 Ensuring Data Integrity
	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.

279