创建表

创建表 “创建一个新表。

句法

创建一个表:

CREATE TABLE [ IF NOT EXISTS ] [schema_name.]table_name
 ( column_name data_type [ column_constraints ] [ , ... ]
    [, table_constraints ]
 )

创建一个 临时表:

CREATE [ LOCAL ] { TEMPORARY | TEMP } TABLE [ IF NOT EXISTS ] table_name
 ( column_name data_type [ column_constraints ] [, ... ]
    [, table_constraints ]
 ) [ ON 犯罪 { PRESERVE ROWS | 删除 ROWS | DROP } ]

使用分区创建表:

CREATE TABLE [ IF NOT EXISTS ] [schema_name.]table_name
 ( column_name { data_type | domain_name } [ column_constraints ] [ , ... ]
    [, table_constraints ]
 )
 {
  STORE IN storage_group |
  ( { PARTITION BY RANGE (column_name) [ STORE IN storage_group ]
      [ PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE)
      [ STORE IN storage_group ] ]... } |
    { PARTITION BY LIST (column_name) [STORE IN storage_group ]
      [ PARTITION partition_name VALUES IN ( { literal [,literal]...} | DEFAULT )
      [ STORE IN storage_group ] ]... }
  )
 }

Where column_constraints can be one or more of the following:

  GENERATED (ALWAYS | BY DEFAULT) AS IDENTITY [(generator_name)]
  NOT NULL | NULL
  CHECK ( expression )
  DEFAULT default_expr
{ COLLATE | COLLATION } collation_name
  PRIMARY KEY
  REFERENCES reference_table [ ( reference_column [ ,...] ) ]
  UNIQUE

Where table_constraints is:

[ CONSTRAINT [ constraint_name ] ] table_constraint [ , table_constraint ]...

and table_constraint is:

CHECK ( boolean_expression )
PRIMARY KEY ( column_name [, ...] ) [ index_qualifiers ]
FOREIGN KEY ( column_name [, ...] ) REFERENCES reference_table [ ( reference_column [, ... ] ) ]
UNIQUE ( column_name [, ...] [ WITH ( [ RESOLUTION n, [ MAX KEY SIZE n ], [ NULLS [NOT] DISTINCT ] ) ]
KEY indexName ( column_name|expression] ...) [ index_qualifiers ]
UNIQUE KEY indexName ( column_name[, ...]) [ index_qualifiers [ WITH ( [ RESOLUTION n, [ MAX KEY SIZE n ], [ NULLS [NOT] DISTINCT ] ) ]

描述

创建表 在当前数据库中创建一个新的最初空表。该表将由发出命令的用户拥有。

If a schema_name is specified (for example, 创建表 myschema.mytable …​)然后在指定的架构中创建该表。 Otherwise, the table is created in the current schema, assigned by the 用 SCHEMA command. Tables cannot be created in the SYSTEM schema. 此架构保留用于数据库元数据。 Temporary tables exist in a special schema, so a schema_name cannot be specified when creating a temporary table.

持久性表的名称必须与当前或指定架构中的任何其他表,序列,索引或视图的名称不同。 可以使用与现有持久性表的相同名称创建临时表。 但是,除非使用其架构限定名称引用,否则临时表存在于当前会话时,持久性表不可见。 这是由于在没有架构名称的情况下对该表的任何引用引用,请参阅临时表。

可选的约束条款指定新的或更新的行必须满足插入或更新操作以取得成功的约束(测试)。 约束是一个SQL对象,有助于以各种方式在表中定义一组有效值。

有两种方法可以定义约束:列约束和表约束。 列约束被定义为列定义的一部分,只影响一个列。 表约束定义未绑定到特定列,它可以包含多个列。

表不能有超过32000列。 (在实践中,由于记录长度约束,有效限制较低)。

The 创建表 command may also create indexes. See also, section 运行并发索引构建 on the 创建索引 page that describes behavior when many index builds run concurrently.

参数

TEMPORARY | TEMP

如果指定,则将该表创建为临时表。 Temporary tables are automatically dropped at the end of a session, or, optionally, at the end of the current transaction (see ON COMMIT below). 在临时表上创建的任何索引也是自动临时的。 Multiple connections to a database can create temporary tables with the same name. Optionally, LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference, as all temporary tables are, by default, LOCAL.

IF NOT EXISTS

如果已存在具有相同名称的表,则不会抛出错误。 请注意,无法保证现有表是类似于创建的表。

schema_name

将创建表的模式的名称。 The default is the current schema as defined by the command. If schema_name is provided, and the schema does not exist, it will be created.

You cannot specify schema_name for a TEMPORARY table.

table_name

要创建的表的名称。

column_name

要在新表中创建的列的名称。

data_type

列的数据类型。数据类型指定列的长度和比例因子。 有关更多信息,请参阅 SQL数据类型.

GENERATED (ALWAYS | BY DEFAULT) AS IDENTITY [(generator_name)]

在INSERT上,基于发电机序列生成生成的列的值。 如果未指定Generator_name,则隐式创建序列(仿效使用隐式生成的名称执行创建序列语句)。 如果指定了Generator_name,但创建列时不存在此名称的序列,则返回错误。 如果未指定创建列时未指定Generator_name,则当列丢弃列时,将删除隐式生成的序列。 表可以在大多数身份列中具有。

始终和默认值之间的差异如下:

  • GENERATED ALWAYS AS IDENTITY - 此列的值会自动生成,无法手动插入/更新。

  • GENERATED BY DEFAULT AS IDENTITY - 插入新记录时,如果未提供一个,则将为此列生成值。 可以手动更新这些列中的值。 NOODB将设置列等于序列的下一个值。

You can use GENERATED ALWAYS AS IDENTITY to improve performance for inserts and updates. 当您指定此子句时,数据库本身负责为列生成唯一值。 这使得对列的索引操作更快地完成。 In other words, strictly from the performance point of view, it is typically faster to perform inserts against a table whose primary key is GENERATED ALWAYS AS IDENTITY than one whose keys are provided by the client.

例8..

创建表后,您无法更改表并定义要自动生成的现有列。 您只能更改表并添加一个新列并将该列定义为自动生成的。
NOT NULL

The column is not allowed to contain NULL values.

NULL

The column is allowed to contain NULL values. This is the default.

DEFAULT default_expr

The DEFAULT clause assigns a default value for the column. The value expression,default_expr, cannot include a variable, sub-select or cross-references to other columns in the current table. he default_expr can be a scalar user defined function. This type of function returns a single value.

默认表达式的数据类型必须与列的数据类型兼容。 例如,数值的默认表达式必须评估为数字,而时间戳的默认表达式必须评估为时间戳。

数据类型检查默认值表达式在Create Table和Alter Table的编译时强制执行。

The default_expr is assigned as the column value in any operation that does not assign a value for the column.

CHECK (expression)

CHECK 子句指定必须通过新的或更新行满足的完整性约束或测试,以允许插入或更新操作成功。 CHECK 如果将其评估为true或null的值,则满足约束。 每个约束必须是产生布尔结果的表达式。在列定义中出现的条件应该引用该列’s value only, while a condition appearing as a table constraint may reference multiple columns. CHECK expressions cannot contain sub-selects nor refer to variables other than columns of the current row.

CHECK table constraints can be optionally qualified with the keyword CONSTRAINT followed by a name. 当表约束命名时,不符合约束时产生的错误消息将包括约束的名称,例如, violation of constraint "\constraint_name.`“。

例6..

{COLLATE|COLLATION}[.var]{DEFAULT|case_insensitive|"8859-1U"} collation_name

此子句允许定义列的排序规则。看 例5.. Supported collation types include: DEFAULT, case_insensitive and "8859-1U". Both case_insensitive and "8859-1U" collation types are synonyms for each other. Therefore, COLLATE case_insensitive will produce the same results as COLLATE "8859-1U". The DEFAULT collation is case sensitive.

PRIMARY KEY(column_name[,…​])

The PRIMARY KEY constraint specifies that a column or group of columns of the new table will be part of the table’s primary key. 主键的角色是提供一个列或一组列,可以用来唯一地识别表中的任何行。 Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL.

Only one PRIMARY KEY can be created for a table, whether as a column constraint or a table constraint.

The PRIMARY KEY constraint should name a set of columns that is different from other sets of columns named by any unique constraint, such as a unique index, defined for the same table.

A PRIMARY KEY table constraint can be optionally qualified by both the keyword CONSTRAINT along with a constraint_name.. In this case, the primary key index is named constraint_name. in SYSTEM.INDEXES (see 索引系统表描述)。

index_qualifiers

One or more modifiers for an index used in the KEY, UNIQUE KEY, and PRIMARY KEY table constraints, where index_qualifiers is:

( index_qualifier [ , index_qualifier ]…​ )

and index_qualifier is one of:

RESOLUTION n

设置索引直方图的分辨率。有关更多信息,请参阅 创建索引.

MAX KEY SIZE literal

默认最大键大小为1024字节。 Nuodb建议索引密钥大小小于或等于默认最大值,因为具有较大的索引键可能会降低性能。 However, if your application requires a larger maximum index key size then you can change this using 改变表. 没有系统属性,可以设置最大索引密钥大小。只有在创建特定索引时,您只能增加最大索引密钥大小。 To change the maximum size allowed for an index key, specify WITH(MAX KEY SIZE n) and replace n with a value from 100 bytes to 3072 bytes. 如果您尝试指定少于100字节或超过3072字节的最大键大小,则汇总异常。

FOREIGN KEY ( column_name , …​ ] REFERENCES reference_table [ ( reference_column , …​ ] ) ]

The FOREIGN KEY constraint defines a relationship between one or more columns in the new table, named as column_name, with referencing column(s), named as reference_column, in the referenced table, named as reference_table. There are rules for specifying this reference:

  • The reference_column must be referencing either columns in a primary key or a unique constraint that is defined on the reference_table.

  • All columns in the primary key or unique constraint of the reference_table must be referenced in the reference_column column list. However, they do not have to appear in the same order as they appear in the primary key or unique constraint on the reference_table.

  • If no reference_column is specified, the default is to reference the PRIMARY KEY column(s) defined for the table specified as the reference_table.

    This FOREIGN KEY constraint, by default, will prevent the table specified by reference_table from being dropped, as long as it remains referenced as a FOREIGN KEY constraint on this new table, or any other table.

    A FOREIGN KEY table constraint can be created with or without specifying a constraint_name.. If a constraint_name. is specified, it must be qualified by the keyword CONSTRAINT, which is not required if no constraint_name. is specified. If no constraint_name. is specified, a unique constraint_name. is generated (see Foresskeys系统表描述)。

    The FOREIGN KEY constraint_name. is stored in the FOREIGNKEYNAME column of the SYSTEM.FOREIGNKEYS table. This is the name that must be referred to if the foreign key constraint is to be dropped using the 改变表 command.

    NuoDB supports the syntax required to define FOREIGN KEY constraints but does not enforce any kind of referential integrity. Only a semantic check is available that prevents the dropping of a table referenced from another table. If required, referential integrity must be maintained by the application.
UNIQUE

The UNIQUE constraint specifies that a column or group of columns of the new table can contain only unique (non-duplicate) values. Columns defined in a UNIQUE constraint do not have to be defined as NOT NULL (NULLS [NOT] DISTINCT. For more information on NULLS [NOT] DISTINCT, see 创建索引)。

Creating a UNIQUE constraint will create a UNIQUE INDEX on the same column(s) defined by the UNIQUE constraint.

A UNIQUE table constraint can be optionally qualified by the keyword CONSTRAINT and a constraint_name.. Y OU还可以指定直方图的分辨率,索引密钥的最大长度以及返回空值的处理。

KEY indexName ( column_name[, …​]) [ WITH (RESOLUTION n )

To add a named index to a table at the same time that you create the table, specify the KEY table constraint. 指定索引的名称和索引的列。您还可以指定直方图的分辨率。

A KEY table constraint can be optionally qualified by the keyword CONSTRAINT and a constraint_name.. In this case, the index is named constraint_name.indexName after the keyword KEY is ignored.

有关索引创建的信息,请参阅 创建索引.

ON COMMIT

The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:

PRESERVE ROWS

交易结束时没有特别行动。这是默认行为。

删除 ROWS

临时表中的所有行将在每个事务结束时删除。

DROP

临时表将在当前事务结束时丢弃。

storage_group

The name of a storage group. When using the storage_group parameter with a STORE IN clause, a default value is provided and stored in the PARTITIONEDTABLES table. This default value may be used thereafter by 改变表…​ADD PARTITION statements. 有关创建存储组的信息,请参阅 管理存储组。还要看看 表分区.

partition_name

由此定义的分区的名称 地图分区 statement. 分区名称必须是唯一的。也可以看看 表分区

value

This is used in the partitioning syntax for VALUES LESS THAN literal and VALUES IN (literal [,literal]…​). See 表分区.

DEFAULT

The keyword DEFAULT is used by the syntax VALUES IN (DEFAULT) to specify a default partition for all data that does not meet previous VALUES IN partitioning criterion. See 表分区.

MAXVALUE

The keyword MAXVALUE is used by the syntax VALUES LESS THAN(MAXVALUE) to specify a default partition for the table. See 表分区.

表分区

创建表 supports an optional PARTITION BY clause which allows control over where data in the table is stored. 如果没有表分区,则表中的行存储在每个存储管理器(SM)上存储。 这是一种非常强大的工具,可以通过减少磁盘和网络流量来提高性能,尤其是在区域分布式应用程序中。 The notion is to keep data local to the client accessing it. 创建表 supports two formats of PARTITION BY: BY RANGE and BY LIST.

In addition to associating a table with multiple storage groups, a table can be created and assigned to just one storage group by specifying the syntax STORE IN storage_group. In this case, we establish a partition for the table and all the data in the table is put into that partition regardless of any other associations established for that table (for example via the 地图分区 command or a subsequent PARTITION BY clause).

按范围分区

PARTITION BY RANGE specifies that each row is mapped to a partition based on the first VALUES LESS THAN subclause that evaluates to true, where VALUES LESS THAN clauses are sorted from lowest to highest.

PARTITION BY RANGE (column_name) [ STORE IN storage_group ]
( [ PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE) [ STORE IN storage_group ]... )

The first, optional, STORE IN storage_group clause designates a storage group for the table, if no storage group is specified after the VALUES LESS THAN clause for the partition. Subsequent, optional, STORE IN storage_group clauses, specified per VALUES LESS THAN clause, may specify a storage group to use for the given partition_name, if the 地图分区 command was not already used to associate the specific partition name with a storage group. The MAXVALUE keyword specifies a partition for values not evaluating to true for any of the VALUES LESS THAN clauses. If MAXVALUE is not specified, then values not evaluating to true for any of the VALUES LESS THAN clauses would cause an error. A partition_name may not be used in more than one VALUES LESS THAN clause. A literal may not be used in more than one VALUES LESS THAN clause.

Determining the storage group associated with the partitioning criterion for an insertion into a PARTITION BY RANGE table is done in the following order:

  1. If an optional storage group is specified after the VALUES LESS THAN clause, then that storage group is used.

  2. If a storage group was specified in the optional STORE IN clause after the PARTITION BY RANGE clause, use that storage group.

  3. If the partition name specified by PARTITION partition_name was previously associated with a storage group using 地图分区, then use that storage group.

  4. 找不到存储组,抛出异常。

Only one column can be used for partitioning. The type of the partitioning column can be any supported SQL type except BLOB or CLOB. If the table has a PRIMARY KEY and/or UNIQUE index, the partitioning column must be added as a column for the index to ensure uniqueness across multiple partitions. The exception to this is a partitioning column that is defined as GENERATED ALWAYS AS IDENTITY. This column would not be required to be added to the PRIMARY KEY and/or UNIQUE index.

有关更多信息,请参阅 使用表分区和存储组.

按列表分区

PARTITION BY LIST specifies that each row is mapped to a partition based on the first VALUES IN clause that contains the value stored in the partitioning column specified by column_name.

PARTITION BY LIST (column_name) [STORE IN storage_group ]
( [ PARTITION partition_name VALUES IN ( { literal [,literal]... } | DEFAULT ) [ STORE IN storage_group ] ]... )

Like PARTITION BY RANGE, The first, optional, STORE IN storage_group clause designates a storage group for the table, if no storage group is specified after the VALUES IN clause for the partition. Subsequent, optional, STORE IN storage_group clauses, specified per VALUES IN clause, may specify a storage group to use for the given partition_name, if the 地图分区 command was not already used to associate the specific partition name with a storage group name. If a value in the partitioning column specified by column_name does not match any of the VALUES IN lists, then an exception is thrown. The DEFAULT keyword specifies a partition for values not evaluating to true for any of the previous VALUES IN clauses. If DEFAULT is not specified, then values not evaluating to true for any of the VALUES IN clauses would cause an error. A partition_name may not be used in more than one VALUES IN clause. A literal may not be used in more than one VALUES IN clause.

Determining the storage group associated with the partitioning criterion for an insertion into a PARTITION BY LIST table is done in the following order:

  1. If an optional storage group is specified after the VALUES IN clause, then that storage group is used.

  2. If a storage group was specified in the optional STORE IN clause after the PARTITION BY LIST clause, use that storage group.

  3. If the partition specified by PARTITION partition_name was previously mapped to a storage group using 地图分区, then use that storage group.

  4. 找不到存储组,抛出异常。

Only one column can be used for partitioning. The type of the partitioning column can be any supported SQL type except BLOB or CLOB.

有关更多信息,请参阅 使用表分区和存储组.

例子

示例1:使用列约束创建表。
CREATE TABLE hockey_fans
(id        BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 name      STRING      NOT NULL,
 address   STRING      NULL,
 city      STRING      NOT NULL,
 zip_code  STRING      NOT NULL,
 teamid    VARCHAR(3)  DEFAULT 'BOS',
 gender    CHAR(1)     CHECK (gender IN ('M','F') ),
 phone     STRING
);

表演 TABLE hockey_fans;

    Tables named HOCKEY_FANS

    Found table HOCKEY_FANS in schema HOCKEY

        Fields:
            ID bigint
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            ZIP_CODE string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: gender IN ('M','F')
            PHONE string
        Primary Index: HOCKEY_FANS..PRIMARY_KEY on field: ID
示例2:创建本地临时表。
表演 TABLES
    No tables found in schema 用R

CREATE LOCAL TEMP TABLE local_table (Col1 STRING,
                                     Col2 INTEGER);

表演 TABLES
    Tables in schema 用R
        LOCAL_TABLE (temporary)
示例3:创建一个临时表,该表将在提交时删除行。
CREATE TEMPORARY TABLE tmp_table (col1 INTEGER, col2 STRING) ON 犯罪 删除 ROWS;

START TRANSACTION;
 INTO tmp_table VALUES (1, 'string1');
选择 * FROM tmp_table;
 COL1   COL2
 ----- -------
   1   string1
犯罪;
选择 * FROM tmp_table;
 COL1   COL2
 ----- -------
<< no records >>
示例4:在单个列上定义缩符类型。
DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12));
 INTO tst_table VALUES ('abc'),('ABC');
 INTO tst_table VALUES ('bcd'),('BCD');
选择 * FROM tst_table WHERE column1 = 'abc';
COLUMN1
--------
   abc

选择 * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   ABC
   BCD
   abc
   bcd


DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12) COLLATE "8859-1U");
 INTO tst_table VALUES ('abc'),('ABC');
 INTO tst_table VALUES ('bcd'),('BCD');
选择 * FROM tst_table WHERE column1 = 'abc';
 COLUMN1
 --------
   abc
   ABC

选择 * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   abc
   ABC
   bcd
   BCD
示例5:使用函数创建表作为默认列值。
 DELIMITER @
CREATE FUNCTION fnc_getdatabase()
  RETURNS STRING
AS
    RETURN (选择 'Database: '||database() FROM DUAL);
END_FUNCTION;
@
 DELIMITER ;

CREATE TABLE test_table
  (column1 STRING,
   database_name  STRING NOT NULL DEFAULT fnc_getdatabase());

 INTO test_table VALUES ('row1',DEFAULT);
 INTO test_table (column1) VALUES ('row2');
 INTO test_table VALUES ('row3','Database: TEST2');
选择 * FROM test_table;
 COLUMN1   DATABASE_NAME
 -------- ---------------
   row1   Database: test
   row2   Database: test
   row3   Database: TEST2
示例6:使用命名和未命名的CHECK约束。

创建一个 table, t1, with one column constraint and two table constraints.

CREATE TABLE t1 (
    f1 INT CHECK (f1 > 10),
    CONSTRAINT even CHECK (f1 % 2 = 0),
    CONSTRAINT CHECK (f1 < 100) );

In this scenario, we are setting a column constraint on f1 that checks that the value inserted is greater than 10. We also set one named table constraint, even, that checks if f1 mod 2 equals 0 and one unnamed table constraint that checks if f1 is less than 100. 违反这些限制的行为将报告如下:

 INTO t1 VALUES (5);
violation of constraint "F1"``
 INTO t1 VALUES (6);``
violation of constraint "F1"``
 INTO t1 VALUES (11);``
violation of constraint "EVEN"``
 INTO t1 VALUES (100);``
violation of constraint "T1$constraint0"
示例7:使用表分区。

创建一个 table, test_table1, with table partitioning by range on column x and a partition p1 previously mapped to storage group sg1.

MAP PARTITION p1 STORE IN sg1;
CREATE TABLE test_table1 (x INTEGER, y STRING) PARTITION BY RANGE (x)
    (PARTITION p1 VALUES LESS THAN (10));

创建一个 table, test_table2, with table partitioning by list on column x and a storage group sg2 that has not previously been mapped to a partition.

CREATE TABLE test_table2 (x INTEGER, y STRING) PARTITION BY LIST (x)
    (PARTITION p2 VALUES IN (1,3) STORE IN sg2);

创建一个 table, test_table3, with multiple table partitioning clauses. This example assumes that partition p1 is mapped to a storage group and partition p2 is mapped to a storage group, but partition p3 is not. Here we tell it to store values less than MAXVALUE in storage group sg3.

CREATE TABLE test_table3 (x INTEGER, y STRING)
    PARTITION BY RANGE (x) (PARTITION p1 VALUES LESS THAN (10)
        PARTITION p2 VALUES LESS THAN (20)
        PARTITION p3 VALUES LESS THAN (MAXVALUE) STORE IN sg3);

有关创建存储组和将表分区映射到它们的端到端示例,请参阅 使用存储组和表分区的示例.

示例8:创建生成的列。
CREATE SEQUENCE auto_seq_1;
CREATE TABLE test_seq_tab1 (column1 INTEGER GENERATED ALWAYS AS IDENTITY (auto_seq_1),
                            column2 STRING);