CREATE TABLE (SQL Server Compact)
Creates a new Microsoft SQL Server Compact table.
CREATE TABLE table_name
( { < column_definition > | < table_constraint > } [ ,...n ]
)
< column_definition > ::=
{ column_name data_type }
[ { DEFAULT constant_expression
| [ IDENTITY [ ( seed , increment ) ]
]
} ]
[ ROWGUIDCOL ]
[ < column_constraint > [ ...n ] ]
< column_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| REFERENCES ref_table [ ( ref_column ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
}
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
{ ( column [ ,...n ] ) }
]
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
}
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.
-
FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table. FOREIGN KEY constraints cannot reference unique indexes.
-
-
Additional Constraint Information
-
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.
The following examples show how to:
-
Create a two-column table with an identity column as the PRIMARY KEY.
-
Create a one-column table with a PRIMARY KEY constraint
-
Create a table with one of its columns referencing a column in another table
CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY, CompanyName nvarchar (50))
CREATE TABLE MyCustomers2 (CustID int CONSTRAINT pkCustId PRIMARY KEY)
CREATE TABLE MyOrders (OrderID int, CustID int REFERENCES MyCustomers(CustID))
Note