The Constraints Syntax

This section has been provided to help you better understanding the CONSTRAINTS section in the CREATE TABLE or ALTER TABLE statements and explains all the constraint options. The constraints as documented for CREATE TABLE or ALTER TABLE are as follows:

• ROWGUIDCOL   This argument indicates that the new column can hold globally unique identifiers. It is a constraint because only one such uniqueidentifier column (set as such) per table can be designated as the ROWGUIDCOL column.

  The ROWGUIDCOL property, however, does not enforce uniqueness of the values stored in the column. It also does not automatically generate values for new rows inserted into the table. To generate unique values for each column, either use the NEWID function on INSERT statements or use the NEWID function as the default for the column.

• CONSTRAINT   This is an optional keyword indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint definition.

• NULL | NOT NULL   These are keywords that determine if null values are allowed in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL, which is a constraint.

• PRIMARY KEY   This is a constraint that enforces entity integrity for a given column or columns through a unique index. Only one PRIMARY KEY constraint can be created per table.

• UNIQUE   This is a constraint that provides entity integrity for a given column or columns through a unique index. A table can have multiple UNIQUE constraints.

• CLUSTERED | NONCLUSTERED   These are keywords to indicate that a clustered or nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.

  You can specify CLUSTERED for only one constraint in a CREATE TABLE statement. If you specify CLUSTERED for a UNIQUE constraint and also specify a PRIMARY KEY constraint, the PRIMARY KEY defaults to NONCLUSTERED.

• FOREIGN KEY…REFERENCES   These are constraints that provide referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exist in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table.

• ON DELETE (CASCADE | NO ACTION}   These arguments specify what action takes place in a row in the table created, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

  If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back (see Chapter 16, where this constraint is further discussed).

  On the other hand, if NO ACTION is specified, SQL Server raises an error and rolls back the delete action on the Customers row if there is at least one row in the Orders table that references it.

• ON UPDATE {CASCADE | NO ACTION}   This argument specifies what action takes place in a row in the table created, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

  If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.

  If NO ACTION is specified, SQL Server raises an error and rolls back the update action on the Customers row if there is at least one row in the Orders table that references it.

• CHECK   This is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

• NOT FOR REPLICATION   This is not really a constraint but an “anti-constraint” that is important for integrity consideration. The argument is used to prevent the CHECK constraint from being enforced during the distribution process used by replication. When tables are subscribers to a replication publication, do not update the subscription table directly; instead, update the publishing table and let replication distribute the data back to the subscribing table.

A CHECK constraint can be defined on the subscription table to prevent users from modifying it. Unless the NOT FOR REPLICATION clause is added, however, the CHECK constraint also prevents the replication process from distributing modifications from the publishing table to the subscribing table. The NOT FOR REPLICATION clause means the constraint is enforced on user modifications but not on the replication process.

The NOT FOR REPLICATION CHECK constraint is applied to both the before and after images of an updated record to prevent records from being added to or deleted from the replicated range. All deletes and inserts are checked; if they fall within the replicated range, they are rejected.

When this constraint is used with an identity column, SQL Server allows the table not to have its identity column values reseeded when a replication user updates the identity column. See also Chapter 8 for more specifics regarding replication between SQL Server instances.