Sdílet prostřednictvím

  • Článek

Creates a new Microsoft SQL Server Compact 3.5 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 } ] 
   }

Arguments

  • table_name
    The name of the new table. Table names must comply with the rules for identifiers. The table_name must be unique within the database. A table_name can contain a maximum of 128 characters.

  • column_name
    The name of a column in the table. Column names must comply with the rules for identifiers and must be unique in the table.

    Column names in SQL Server Compact 3.5 databases must not start with "__sys". For example, __sysobjects is a restricted column name.

  • data_type
    Specifies the column data type. For information about data types, see Data Types and RDA.

  • 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 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 3.5 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 3.5 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.

    SQL Server Compact 3.5 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 3.5 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 3.5 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.

    • 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.

Example

The following examples show how to:

  1. Create a two-column table with an identity column as the PRIMARY KEY.

  2. Create a one-column table with a PRIMARY KEY constraint

  3. 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))