Specifies the value provided for the column when a value is not explicitly supplied during an insert action. DEFAULT definitions can be applied to any column, except those defined by the IDENTITY property. DEFAULT definitions are removed when the table is dropped. A constant value can be used as a default.

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, SQL Server Compact provides a unique, incremental value for the column. Identity columns are generally used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned only to int columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

seed

The value used for the first row that is loaded into the table.

increment

The incremental value added to the identity value of the previous row that is loaded.

ROWGUIDCOL

Indicates that the new column is a row global unique identifier column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

ROWGUIDCOL automatically generates values for new rows inserted into the table.

CONSTRAINT

An optional keyword indicating the beginning of a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint definition. Constraints are special properties that enforce data integrity and create special types of indexes for the table and its columns.

constraint_name

The name of a constraint. The constraint_name is optional and must be unique within a database. If a constraint_name is not specified, SQL Server Compact generates a constraint name.

NULL | NOT NULL

Keywords that specify whether null values are permitted in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL.

PRIMARY KEY

A constraint that enforces entity integrity for a particular column or columns using a unique index. Only one PRIMARY KEY constraint can be created per table.

UNIQUE

A constraint that provides entity integrity for a particular column or columns using a unique index. Columns in a UNIQUE constraint can be NULL, but only one NULL value is allowed per column. A table can have multiple UNIQUE constraints.

Note

SQL Server Compact can use indexes to enforce PRIMARY KEY and UNIQUE constraints. We recommend that you do not rely on this behavior nor try to modify any indexes that are created as part of a constraint.

FOREIGN KEY...REFERENCES

A constraint that provides referential integrity for the data in the column. FOREIGN KEY constraints require that each value in the column exists in the specified column in the referenced table.

ref_table

The name of the table referenced by the FOREIGN KEY constraint.

( ref_column [ ,...n ] )

A column, or list of columns, from the table referenced by the FOREIGN KEY constraint.

ON DELETE {CASCADE | NO ACTION}

Specifies what action happens to a row in the table that is created when 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 when the corresponding referenced row is deleted from the parent table. If NO ACTION is specified, SQL Server Compact returns an error and the delete action on the referenced row in the parent table is rolled back.

ON UPDATE {CASCADE | NO ACTION}

Specifies what action happens to a row in the table that is created when 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 when the corresponding referenced row is updated in the parent table. If NO ACTION is specified, SQL Server Compact returns an error and the update action on the referenced row in the parent table is rolled back.

column

A column or list of columns, in parentheses, used in table constraints to indicate the columns used in the constraint definition.

Column Definitions

You must specify at least one column definition when you create a table.

Constraints

PRIMARY KEY Constraints

A table can contain only one PRIMARY KEY constraint.

Each PRIMARY KEY generates an index.

All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

UNIQUE Constraints

Each UNIQUE constraint generates an index.

FOREIGN KEY Constraints

When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.

FOREIGN KEY constraints can reference another column in the same table, referred to as a self-reference. However, FOREIGN KEY constraints cannot be used to create a self-referencing or circular FOREIGN KEY constraint.

The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column. This must have the same data type as the column on which the constraint is defined.

The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column also must be the same as the corresponding column in the column list.

An index created for a constraint cannot be dropped with the DROP INDEX statement. The constraint must be dropped with the ALTER TABLE DROP CONSTRAINT statement.

Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If the CONSTRAINT keyword and constraint_name is not supplied, a system-generated name is assigned to the constraint.

When a constraint is violated in an INSERT, UPDATE, or DELETE statement, the statement is stopped.

DEFAULT Definitions

A column can have only one DEFAULT definition. This can contain constant values or constant functions.

Nullability Rules Within a Table Definition

The nullability of a column determines whether that column could permit a null value (NULL) as the data in that column. NULL is not zero or blank. It means no entry was made or an explicit NULL was supplied, and it typically implies that the value is either unknown or not applicable.