Export (0) Print
Expand All

CREATE TABLE

SQL Server 2000

Creates a new table.

Syntax
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 } ] 
   }
Arguments
table_name
Is the name of the new table. Table names must conform to the rules for identifiers. table_name must be unique within the database. table_name can contain a maximum of 128 characters.
column_name
Is the name of a column in the table. Column names must conform to the rules for identifiers and must be unique in the table.
data_type
Specifies the column data type. For information about data types, see Data Types.
DEFAULT
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, such as a character string, or a date function 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, Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE) provides a unique, incremental value for the column. Identity columns are commonly 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
Is the value used for the first row that is loaded into the table.
increment
Is 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
Is 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
Is the name of a constraint. constraint_name is optional and must be unique within a database. If constraint_name is not specified, SQL Server CE generates a constraint name.
NULL | NOT NULL
Are keywords that specify whether null values are allowed in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL.
PRIMARY KEY
Is a constraint that enforces entity integrity for a given column or columns using a unique index. Only one PRIMARY KEY constraint can be created per table.
UNIQUE
Is a constraint that provides entity integrity for a given column or columns using a unique index. Columns in a UNIQUE constraint must also be NOT NULL. A table can have multiple UNIQUE constraints.

Note  SQL Server CE can use indexes to enforce PRIMARY KEY and UNIQUE constraints. You should not rely on this behavior nor try to manipulate any indexes that are created as part of a constraint.

FOREIGN KEY...REFERENCES
Is 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
Is the name of the table referenced by the FOREIGN KEY constraint.
( ref_column [ ,...n ] )
Is a column, or list of columns, from the table referenced by the FOREIGN KEY constraint.
ON DELETE {CASCADE | NO ACTION}
Specifies what action takes place 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 CE raises an error and the delete action on the referenced row in the parent table is rolled back.
For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: the Orders.CustomerID foreign key references the Customers.CustomerID primary key.
If a DELETE statement is executed on a row in the Customers table and an ON DELETE CASCADE action is specified for Orders.CustomerID, SQL Server CE checks for one or more dependent rows in the Orders table. If any exists, the dependent rows in the Orders table are deleted, as well as the row referenced in the Customers table.
Alternately, if NO ACTION is specified, SQL Server CE raises an error and rolls back the delete action on the row in the Customers table when there is at least one row in the Orders table that references it.
ON UPDATE {CASCADE | NO ACTION}
Specifies what action takes place 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 CE raises an error and the update action on the referenced row in the parent table is rolled back.
For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: the Orders.CustomerID foreign key references the Customers.CustomerID primary key.
If an UPDATE statement is executed on a row in the Customers table and an ON UPDATE CASCADE action is specified for Orders.CustomerID, SQL Server CE checks for one or more dependent rows in the Orders table. If any exists, the dependent rows in the Orders table are updated, as well as the row referenced in the Customers table.
Alternately, if NO ACTION is specified, SQL Server CE raises an error and rolls back the update action on the referenced row in the Customers table when there is at least one row in the Orders table that references it.
column
Is a column or list of columns, in parentheses, used in table constraints to indicate the columns used in the constraint definition.
Remarks
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.
    • All columns defined as a UNIQUE constraint must be defined as NOT NULL. If nullability is not specified, all columns defined as UNIQUE constraints have their nullability set to NOT NULL.
  • 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 (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, which 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 terminated.
DEFAULT Definitions

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

Nullability Rules Within a Table Definition

The nullability of a column determines whether or not that column can allow 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 usually implies that the value is either unknown or not applicable.

Examples

The following example creates a two-column table with an identity column as the PRIMARY KEY.

CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY, CompanyName nvarchar (50))

The following example creates a one-column table with a PRIMARY KEY constraint.

CREATE TABLE MyCustomers2 (CustID int CONSTRAINT pkCustId PRIMARY KEY)

The following example creates a table with one of its columns referencing a column in another table.

CREATE TABLE MyOrders (OrderID int, CustID int REFERENCES MyCustomers(CustID))

Show:
© 2014 Microsoft