Protecting the consistency of all data in a single column prevents data-entry errors. SQL Server supports several features for enforcing single-column integrity.
Data types determine the kind of information that can be stored in the columns of database tables. A rich set of system-supplied data types is provided—including binary large object (BLOB) data types to support storage of multimedia objects in the database. SQL Server also supports international languages with alternate sort orders and alternate date, money, and number formats.
Developers can also define their own data types to supplement the system-supplied data types. For example, us_state_type can be defined as a two-character data type to store the legal abbreviations for each of the fifty states in the United States of America. The advantage of user-defined data types is that definitions of NULL values, defaults, and rules can be shared across many applications or tailored to specific applications. They also ensure consistency of data so tables can be joined together during transactions and queries.
Constraints conform to the ANSI standard for creating and altering tables. Constraints limit the possible values that users can enter into a column or columns. Multiple constraints can be associated with a single column (column-level constraint), and a constraint can be associated with multiple columns (table-level constraint). Constraints are defined when creating tables in SQL Enterprise Manager or with the CREATE TABLE statement. SQL Server provides CHECK, DEFAULT, FOREIGN KEY, PRIMARY KEY, and UNIQUE constraints.
SQL Server correctly distinguishes NULLs (non-entries) from any specific entry, including "0" for a number field or spaces for a character field.
Defaults allow developers to specify a value when no value is explicitly entered for a specific column. For example, if a user does not enter a value in the order_date column, SQL Server can automatically insert the current date.
Rules specify the domain of valid values for a specific column. They can specify a set of values, a range of values, or a format. For example, the us_state_code column can be restricted to the legal abbreviation of each of the fifty states in the United States of America.
The definition of a rule can contain any expression that is valid in an SQL WHERE clause, except that it cannot refer to column or table names. It can include arithmetic operators, relational operators, LIKE, BETWEEN, and so on. The same rule can be bound to more than one column or user-defined data type, and applications automatically use any rules that have been defined. Rules can be changed or dropped at any time.
Note CHECK constraints, which specify data values that are acceptable in a column, can often be more powerful than rules. You can apply CHECK constraints to multiple columns, and you can apply multiple CHECK constraints to a single column.