改变表

改变表 - 更改表的定义

句法

ALTER TABLE [schema_name.]table_name alter_table_command [ ,alter_table_command ]...

Where alter_table_command can be one or more of the following:

ADD [ COLUMN ] column_definition
ADD [ COLUMN ] ( column_definition [ , column_definition ]... )
ADD [ CONSTRAINT [ constraint_name ] ] table_constraint
ADD PARTITION partition_name VALUES LESS THAN ( literal | MAXVALUE ) [ STORE IN storage_group ] }...
ADD PARTITION partition_name VALUES IN ( literal [ ,literal]... ) [ STORE IN storage_group ] }...

ALTER [ COLUMN ] column_name
    { [  DATA ] TYPE data_type |
      COLLATION | COLLATE [ = ] }{DEFAULT |8859-1U} collation_name |
       DEFAULT default_expr |
      column_constraint }...

CHANGE [ COLUMN ] column_name new_column_name data_type column_qualifier

DISABLE INDEX index_name

DROP [ COLUMN ] column_name
DROP [ COLUMN ] ( column_name [ ,column_name ]... )
DROP CONSTRAINT [ constraint_name ]

ENABLE INDEX index_name

MODIFY [ COLUMN ] column_definition
MODIFY PARTITION BY RANGE (column_name)
    [STORE IN default_storage_group]
    PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE)
    [STORE IN storage_group]

REBUILD { INDEXES | INDEX index_name }

RENAME [ COLUMN ] column_name TO new_column_name

RENAME [ INDEX ] index_name TO new_index_name

RENAME TO [ schema_name. ]new_table_name

Where column_definition is:

column_name data_type column_qualifier

Where column_qualifier can be one or more of the following:

column_constraint
{ COLLATION | COLLATE [ = ] }{DEFAULT |8859-1U} collation_name
DEFAULT expression
GENERATED (ALWAYS | BY DEFAULT) AS IDENTITY [(generator_name)]

column_constraint is one or more of the following:

NULL | NOT NULL
PRIMARY KEY
REFERENCES reference_table [ ( reference_column [, ... ] ) ]
CHECK ( boolean_expression )
UNIQUE

Where table_constraint can be one of the following:

CHECK ( boolean_expression )
PRIMARY KEY ( column_name [, ...] ) [ index_qualifiers ]
FOREIGN KEY ( column_name [, ...] ) REFERENCES reference_table [ ( reference_column [, ... ] ) ]
UNIQUE ( column_name [, ...] )
KEY indexName ( column_name[, ...]) [ index_qualifiers ]
UNIQUE KEY indexName ( column_name[, ...]) [ index_qualifiers ]

描述

Use 改变表 to change the definition of an existing table.

The 改变表 command is not supported for temporary tables.

添加或删除一个或多个列

要将列或列添加到表中,请使用:

ADD [ COLUMN ] column_definition
ADD [ COLUMN ] ( column_definition[ , column_definition ]... )

这将为指定表添加新列或列。看 column_definition above for syntax. ADD COLUMN requires the same syntax as when defining a column in the 创建表 command (See 创建表)。

To add a new column with a FOREIGN KEY reference to a UNIQUE constraint of the referencing table, first use the ADD COLUMN option of the 改变表 command to add the new column. Then, use the ADD CONSTRAINT option of the 改变表 command to add the FOREIGN KEY reference to the UNIQUE constraint of the referencing table.

从表使用中删除列或列:

DROP [ COLUMN ] column_name
DROP [ COLUMN ] ( column_name [ , column_name ]... )

这将删除表中的指定列或列。

您无法删除索引中定义的列。
您无法删除分区列,即表分区中使用的列。
您无法删除表约束中引用的列。您必须先删除表约束。

添加或删除表约束

表约束在表级别指定。表约束可以是检查约束,唯一性约束,主键或外键(参考约束)。看 table_constraint 以上对于NuodB支持的表约束语法。要为表添加命名或未命名的约束,请使用:

ADD [ CONSTRAINT [ constraint_name ] ] table_constraint

This syntax will allow you to add a CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. An optional constraint_name may be specified. If a constraint_name is not specified, the default name given to the constraint will be generated. The constraint, including its name, is stored in various system tables, depending on the type of constraint, as follows:

命名约束是有用的两个原因:

  1. 不符合约束时产生的错误消息将包括约束的显式名称。

  2. You can easily drop the constraint using 改变表 DROP CONSTRAINT constraint_name。看 Dropping a Table Constraint, below.

When adding a unique, primary key, or foreign key table constraint, the CONSTRAINT keyword is only necessary if you want to name the table constraint. Otherwise, the syntax can be simplified to ADD UNIQUE…​, ADD PRIMARY KEY…​ or ADD FOREIGN KEY…​.

For a CHECK table constraint, a constraint_name is optional, but the CONSTRAINT keyword is always required.

When adding any constraint, if the data in the column or columns does not satisfy the constraint, an error message will be issued and the constraint will not be added. The exception to this is the FOREIGN KEY constraint where referential integrity is not enforced by NuoDB.

When adding a primary key using either syntax, if the column or columns are not already marked NOT NULL, then 改变表 attempts to set them to NOT NULL. If NULL values exist, an error message is issued and the primary key is not added.

A UNIQUE constraint will allow one or more of the columns defined by the constraint to contain a NULL value. Unless the UNIQUE constraint hasn’使用选项nulls不明确的选项创建t,将空值视为不等于另一个空值;这意味着可以存在多行,其中由约束定义的列值具有相等,而不是空值,只要由约束定义的一个或多个列值具有空值(参见下面的示例4)。如果仅由一个列定义唯一约束,则可以存在多个行,其中列值等于null。

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。看 the parameters section below for rules on specifying the reference_tablereference_column for the FOREIGN KEY constraint.

除了主键外,在表上删除约束,使用:

DROP CONSTRAINT constraint_name

You cannot drop a constraint, such as a UNIQUE constraint, on a table if it is being referenced by another table as a FOREIGN KEY. You must first alter the referencing table and drop the foreign key reference to the UNIQUE constraint.

Dropping a UNIQUE INDEX, using the 下降索引 command will drop the UNIQUE constraint associated with that UNIQUE INDEX.

目前无法删除表的主键。您必须删除主键的索引(参见 下降索引)。 You can only drop a primary key index if it is not being referenced by another table as a FOREIGN KEY.

通过添加或删除列限定符更改列

One or more specific column qualifiers can be added or removed for an existing column using ALTER COLUMN.

Using ALTER COLUMN you can do one or more of the following:

  • 更改列的类型

  • 将归类添加到现有列

  • 向现有列添加默认表达式。注意,更改现有列以添加默认值不会更新具有该默认值的现有行。

  • Add a NULL or NOT NULL constraint to an existing column

  • 制作现有列主键。所有现有的行都必须不为null。

You can also drop a default expression from a column using DROP DEFAULT.

这个子命令有五种变体:

ALTER [ COLUMN ] column_name
    { [  DATA ] TYPE data_type |
      COLLATION | COLLATE [ = ] }{DEFAULT |8859-1U} collation_name |
       DEFAULT default_expr |
      column_constraint }...

每个语句可以指定此子命令的多个变体,由空格分隔。

You may not use ALTER COLUMN to add a CHECK, UNIQUE, or referential FOREIGN KEY constraint to a column.
对于已在索引中定义的列无法更改官原类型。推荐的方法是:
1.下降索引
2. Alter the column using the 改变表 command
3.重新创建指数。

重新定义一列

You can redefine a column using the MODIFY subcommand or rename and redefine a column using the CHANGE subcommand.

MODIFY [ COLUMN ] column_definition

The MODIFY subcommand redefines an existing column. It is the same as CHANGE COLUMN except CHANGE also allows you to rename the existing column. The keyword COLUMN is optional and can be omitted. MODIFY COLUMN requires the same syntax as ADD COLUMN, or when defining a column in the 创建表 command (See 创建表)。 Therefore, the data type must be specified — regardless of whether those parameters are being modified or not — along with one or more of the optional column_qualifier s in the column_definition.

You may not use MODIFY COLUMN to add a CHECK, UNIQUE, or referential FOREIGN KEY constraint to a column.

You can rename and redefine a column using the CHANGE subcommand.

CHANGE [ COLUMN ] column_name new_column_name data_type column_qualifier

The CHANGE subcommand changes the name other modifiers of an existing column in the table. It is the same as MODIFY COLUMN except it also allows you to rename the column. The keyword COLUMN is optional and can be omitted. CHANGE COLUMN uses the same syntax as ADD COLUMN or when defining a column in the 创建表 statement (See 创建表)。 Therefore, the data type of the column must be specified — regardless of whether those parameters are being modified or not — along with one or more of the optional column_qualifier s.

You may not use CHANGE COLUMN to add a CHECK, UNIQUE, or referential FOREIGN KEY constraint to a column.

重命名一列

The RENAME COLUMN command modifies the name of an existing column in the table:

RENAME [ COLUMN ] column_name TO new_column_name

The keyword COLUMN can be omitted. There is no effect on the stored data.

重命名桌子

The RENAME command renames an existing table.

RENAME TO [ schema_name. ]new_table_name

If provided, schema_name must be the same for table_namenew_table_name. Tables cannot be moved between schemas.

启用或禁用索引

It is possible to temporarily disable an index such that it will not be chosen by the optimizer for use in a query plan. The index will still be updated when inserts are done on its corresponding column or columns. When an index is disabled it is disabled for all existing and new connections to the database and will remain disabled until you use ENABLE INDEX. To disable an index:

DISABLE INDEX index_name

要启用先前已禁用的索引:

ENABLE INDEX index_name

重建索引

The REBUILD sub command will drop and create the index using the current index definition. 如果在索引创建时间指定了非默认分辨率,则将在重建时使用该分辨率。 要更改索引,例如添加或删除列,请使用 下降索引创建索引.

在正常操作期间,您不必手动运行REBUILD子命令,因为NOODB已经在后台重新组织和重新平衡索引。
Rebuilding indexes can sometimes be necessary when nuochk verifies an archive and identifies that an index needs to be rebuilt.

另请参阅 运行并发索引构建 on the 创建索引 page that describes behavior when many index builds run concurrently.

要重建为正在更改的表定义的每个索引,请使用:

REBUILD INDEXES

这还包括重建主键索引。

To rebuild only the index specified by index_name for the table being altered, use:

REBUILD INDEX [.var]__index_name__

重命名索引

要使用以下内容修改表中现有索引的名称:

RENAME [ INDEX ] index_name TO new_index_name

对存储数据没有影响。此操作对正在进行的交易没有影响。

添加或删除表分区

您可以从已存在的分区表中添加或删除表分区。 您还可以在不移动数据的情况下向未分区填充的表添加分区。

ADD PARTITION partition_name VALUES LESS THAN ( value | MAXVALUE ) [ STORE IN storage_group ] }...
ADD PARTITION partition_name VALUES IN ( value [ ,value]... ) [ STORE IN storage_group ] }...
DROP PARTITION partition_name

There is no PARTITION BY clause for 改变表.

The type of partition (RANGE or LIST) must match the existing partitioning type for the table, that is, the type specified by PARTITION BY when the table was created.

在已为表定义的现有分区覆盖的范围内添加分区,将导致运行时错误。

表分区和存储组简介 想要查询更多的信息。

将未分区表转换为分区表

可以使用以下方式分区未分区表:

ALTER TABLE [table] MODIFY PARTITION BY RANGE (column_name)
    [STORE IN default_storage_group]
    PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE)
    [STORE IN storage_group]

生成的分区表将具有单个分区,该分区包含来自原始表的所有数据,包括现有索引。

Only range-style partitioning is supported. The range must be set high enough to include all records currently in the table. This means the specified range must be greater than the maximum value of partitioning column (column_range) in all records of the table.

如果在表上创建了任何唯一索引,则必须保证索引密钥在分区上是唯一的。这意味着索引必须包括分区列或包括自动化列。

The partition must remain in the same storage group as the table currently resides. This will be the UNPARTITIONED storage group for a regular table.

It is possible to specify STORE IN default_storage_group and/or STORE IN storage_group as long as the partition will remain in the same storage group as it is currently in. The default storage group is used when adding additional partitions so that there is no need to always specify the storage group.

推进自动生成的列的序列

您可以通过发出以下命令,提前自动生成的列序列以匹配表中的最高值:

ALTER TABLE CHANGE COLUMN column_name UPDATE SEQUENCE RANGE

参数

schema_name

Name of the schema in which the table to be altered exists. The default is the current schema as defined by the command.

table_name

现有表的名称(可选地模式限定)

column_name

现有列的名称

new_column_name

现有列的新名称

data_type

列的数据类型。数据类型指定列的长度和比例因子。更多细节。看 SQL数据类型.

NULL | NOT NULL

A column qualifier that constrains the column to either allow or disallow NULL values.

reference_table

This is the name of an existing table being referenced by a FOREIGN KEY constraint.

reference_column

This is the name of one or more columns defined in the reference_table being referenced by a FOREIGN KEY constraint. The reference_column column list must be a list of columns that are defined as a PRIMARY KEY or UNIQUE constraint of the reference_table. If no reference_column column list is specified, the default is to reference column(s) defined by the PRIMARY KEY for the table specified as the reference_table. This reference_column column list must included all columns an defined by the PRIMARY KEY or UNIQUE constraint of the reference_table, but not necessarily in that same order.

boolean_expression

A SQL expression used in a CHECK constraint. The CHECK constraint is satisfied if boolean_expression resolves to true or unknown. A boolean_expression appearing within a CHECK constraint in a column definition should reference that column’s value only, while a boolean_expression appearing as a table constraint may reference multiple columns. The boolean_expression cannot contain sub-selects nor refer to variables other than columns of the current row.

UNIQUE

A column qualifier that specifies a UNIQUE constraint on the column or columns specified. A UNIQUE constraint is satisfied if each value inserted into the column is unique. Adding a UNIQUE constraint will create a UNIQUE INDEX on the column(s) defined by the UNIQUE constraint.

collation_name

A string representing the name of a collation used in a COLLATION or COLLATE qualifier. See Example 2. Supported collation types include: DEFAULT, case_insensitive"8859-1U". Both case_insensitive"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.

default_expr

A SQL expression used in a DEFAULT column qualifier.

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

列限定符,使用序列生成要插入给定列的值。 See 创建表 有关生成列的详细信息和用法。

Altering an existing column to add the IDENTITY qualifier will not update existing rows.
constraint_name

表约束的可选名称。如果没有提供,将生成唯一的约束名称。

new_table_name

要重命名的现有表的新名称(可选地模式限定)。如果提供的话,架构必须与现有表相同。表格不能在模式之间移动。

partition_name

The name of a partition as defined by the 地图分区 command. See 地图分区, 创建表使用表分区和存储组 想要查询更多的信息。

VALUES LESS THAN ( value | MAXVALUE )

This is used in the partitioning syntax for VALUES LESS THAN valueVALUES IN (value [,value]…​)。看 创建表使用表分区和存储组 想要查询更多的信息。

The keyword MAXVALUE is used by the syntax VALUES LESS THAN(MAXVALUE) to specify a default partition for the table. See 创建表使用表分区和存储组 想要查询更多的信息。

VALUES IN ( value [ ,value ]…​ )

A literal value, whose type should be the same as the corresponding column. This is used in the partitioning syntax for VALUES LESS THAN valueVALUES IN (literal [,literal]…​)。看 创建表使用表分区和存储组 想要查询更多的信息。

The keyword MAXVALUE is used by the syntax VALUES LESS THAN(MAXVALUE) to specify a default partition for the table. See 创建表使用表分区和存储组 想要查询更多的信息。

STORE IN storage_group

存储组的名称。有关创建存储组的信息,请参阅 管理存储组。看 创建表使用表分区和存储组 想要查询更多的信息。

index_qualifiers

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

( index_qualifier [ , index_qualifier ]…​ )

index_qualifier is one of:

RESOLUTION n

The query optimizer uses statistics to plan and optimize queries. Index histograms are one of the most important parts of index statistics. This option allows you to specify the RESOLUTION of the histogram, that is, the number of "buckets" used in the histogram. The default number of buckets created is 256. Raising this number will make more precise statistics available to the query optimizer, which will then allow the optimizer to make better decisions, potentially leading to an improvement in query performance. The downside of increasing the number of buckets is that the index will consume more memory and processing when the index is created or updated. Valid RESOLUTION values are in the range: 10 to 1000 (default 256).

提高查询性能 有关如何使用此选项的详细信息。

提高查询性能 有关使用此选项的更多信息。

MAX KEY SIZE literal

The default maximum key size is 1024 bytes. NuoDB recommends that index key size be less than or equal to the default maximum because having larger index keys might decrease performance. However, if your application requires a larger maximum index key size then you can change this using 改变表. There is no system property for setting a maximum index key size. You can increase the maximum index key size only when you create a particular index. To change the maximum size allowed for an index key, specify WITH(MAX KEY SIZE n) 和 replace n with a value from 100 bytes to 3072 bytes. NuoDB throws an exception if you try to specify a maximum key size that is less than 100 bytes or more than 3072 bytes.

index_name

Identifier to distinguish an index. This is used by ENABLEDISABLE INDEX, REBUILD INDEX, and in identifying keys.

例子

Example 1: Multiple 改变表 examples

创建一个新表。
更改表以添加新列。
更改表以添加表约束主键。
This change sets the column to now be NOT NULL.
更改表以删除现有列并添加新列。
更改表以修改现有列
更改表以重命名现有列

CREATE TABLE hockey_fans (id       INTEGER     GENERATED ALWAYS AS IDENTITY,
name      STRING     NOT NULL,
address   STRING     NULL,
city      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 用R
        Fields:
            ID integer
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string

ALTER TABLE hockey_fans ADD COLUMN age INTEGER;
ALTER TABLE hockey_fans ADD CONSTRAINT pk_hockey_fans PRIMARY KEY (id);
表演 table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema 用R
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            AGE integer
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans DROP age , ADD state CHAR(2);
表演 table hockey_fans;``
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema 用R
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            STATE char(2)
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans ALTER state  DATA TYPE STRING;
表演 table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema 用R
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE string
            STATE string
        Primary Index: PK_HOCKEY_FANS on field: ID

ALTER TABLE hockey_fans RENAME phone TO phone_number;
表演 table hockey_fans;
    Tables named HOCKEY_FANS
    Found table HOCKEY_FANS in schema 用R
        Fields:
            ID integer
                Nullable: No
                Generator: HOCKEY_FANS$IDENTITY_SEQUENCE    Generated Always
            NAME string
                Nullable: No
            ADDRESS string
            CITY string
                Nullable: No
            TEAMID varchar(3)
                Default: 'BOS'
            GENDER char(1)
                Constraint: GENDER in ('M','F')
            PHONE_NUMBER string
            STATE string
        Primary Index: PK_HOCKEY_FANS on field: ID
示例2:更改列上的排序规则
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

ALTER TABLE tst_table ALTER column1 COLLATE 8859-1U;
选择 * FROM tst_table WHERE column1 = 'abc';
 COLUMN1
 --------
   abc
   ABC

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

/* Cannot alter the collation on an indexed column */用 TEST;
DROP TABLE IF EXISTS tsttable;
CREATE TABLE tsttable (column1 INTEGER, column2 string);
CREATE INDEX idx_tsttable_column2 ON tsttable(column2);
ALTER TABLE tsttable ALTER COLUMN column2 collate 8859-1U;
/* ERROR MSG: cannot modify collation on column column2 in table test.tsttable: column is used in index idx_tsttable_column2 */
示例3:ALTER TABLE添加NOT NULL列并提供默认值
用 TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER PRIMARY KEY, field2 STRING);
 INTO test_table (field1, field2) VALUES (1,'row1');

ALTER TABLE test_table ADD COLUMN field3 STRING;
选择 * FROM test_table;
 FIELD1  FIELD2  FIELD3
 ------- ------- -------
    1     row1   <null>
表演 TABLE test_table;
    Tables named TEST_TABLE
    Found table TEST_TABLE in schema 用R
        Fields:
            FIELD1 integer
                Nullable: No
            FIELD2 string
            FIELD3 string
        Primary Index: TEST_TABLE..PRIMARY_KEY on field: FIELD1

/* cannot alter an existing column to NOT NULL */
ALTER TABLE test_table ALTER COLUMN field3 NOT NULL;
/* ERROR: NOT NULL constraint failed. Column field3 contains NULL values*/

ALTER TABLE test_table ALTER COLUMN field3 NOT NULL DEFAULT 'field3 not null';
选择 * FROM test_table;
 FIELD1  FIELD2      FIELD3
 ------- ------- ---------------
    1     row1   field3 not null
/* cannot alter table adding a new NOT NULL column without having to specify a default
 * value */
ALTER TABLE test_table ADD COLUMN field4 STRING NOT NULL;
/* ERROR:  field FIELD4 doesn't allow NULL values, but a default value for the
 * existing rows hasn't been specified */

ALTER TABLE test_table ADD COLUMN field4 STRING NOT NULL DEFAULT 'userid';
选择 * FROM test_table;
 FIELD1  FIELD2      FIELD3      FIELD4
 ------- ------- --------------- -------
    1     row1   field3 not null userid
示例4:更改表添加主键或唯一约束

First, we attempt add a PRIMARY KEY constraint on columns with NULL values.
这返回错误。
The 更新 statement sets these NULL values to some unique, NOT NULL value.
Adding the PRIMARY KEY also changes the columns defined by the PRIMARY KEY to be NOT NULL.

用 TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER, field2 INTEGER, field3 STRING );
 INTO test_table (field1, field2, field3) VALUES (NULL,1,'row1'),(NULL,1,'row2');
表演 TABLE test_table;
    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string

ALTER TABLE test_table ADD PRIMARY KEY (field1,field2);
Null key values are not allowed in primary key index TEST_TABLE..PRIMARY_KEY

UPDATE test_table  field1 = substr(field3,-1,1) where field1 IS NULL;
选择 * FROM test_table;
 FIELD1  FIELD2  FIELD3
 ------- ------- -------

    1       1     row1
    2       1     row2

ALTER TABLE test_table ADD PRIMARY KEY (field1,field2);
表演 TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
                Nullable: No
            FIELD2 integer
                Nullable: No
            FIELD3 string
        Primary Index: TEST_TABLE..PRIMARY_KEY on fields: FIELD1, FIELD2

Using the same original column values, the following shows the behavior of adding a UNIQUE constraint.
The two rows appear to be duplicate rows, both having the same values of field1 equal to NULL value and field2 equal to 1.
However, since NULL values cannot be equal values, these two rows are unique. Therefore, the UNIQUE constraint is created.
The UNIQUE constraint allows NULL values. Therefore, the columns defined by the UNIQUE constraint are not changed to NOT NULL.

用 TEST;
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table ( field1 INTEGER, field2 INTEGER, field3 STRING );
 INTO test_table (field1, field2, field3) VALUES (NULL,1,'row1'),(NULL,1,'row2');
表演 TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string

ALTER TABLE test_table ADD unique (field1,field2);
表演 TABLE test_table;

    Tables named TEST_TABLE

    Found table TEST_TABLE in schema TEST

        Fields:
            FIELD1 integer
            FIELD2 integer
            FIELD3 string
        Unique Constraint Index: TEST_TABLE_UNIQUE_0 on fields: FIELD1, FIELD2
示例5:将未分区表转换为分区表
/* Create a regular table and then convert it to a partitioned table */
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(f1 int, f2 int, f3 string, PRIMARY KEY (f1, f2));
 INTO test_table values (1,100,'abc'), (2,200,'def'), (3,300,'ghi');

/* Unique indexes must include the partitioning column or an autogenerated
 * column */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f3)
    PARTITION p1 VALUES LESS THAN ('zzz') STORE IN UNPARTITIONED;
/* Error:  Partitioning column TEST_TABLE.F3 must be part of unique index
 * TEST_TABLE..PRIMARY_KEY, or TEST_TABLE..PRIMARY_KEY must include an
 * autogenerated column */

/* The range must exceed the maximum value in the partitioning column */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (200) STORE IN UNPARTITIONED;
/* Error:  partition range must exceed maximum value in the table */

SQL> 选择 MAX(f2) FROM test_table;
 MAX
 ----
 300

ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (301) STORE IN UNPARTITIONED;

/* The table is now partitioned */
选择 tablename, partitioningcolumn, defaultstoragegroup
    FROM system.partitionedtables WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONINGCOLUMN  DEFAULTSTORAGEGROUP
 ---------- ------------------- --------------------
 TEST_TABLE         F2                 <null>

选择 tablename, partitionname, storagegroup
    FROM system.partitionids WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONNAME  STORAGEGROUP
 ---------- -------------- -------------
 TEST_TABLE       P1       UNPARTITIONED


/* Convert a table that is located on a specific storage group */
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table(f1 int, f2 int, f3 string) STORE IN stg1;
 INTO test_table values (1,100,'abc'), (2,200,'def'), (3,300,'ghi');

/* The storage group cannot be changed */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2)
    PARTITION p1 VALUES LESS THAN (301) STORE IN UNPARTITIONED;
/* Error:  changing the storage group is disallowed, table TEST_TABLE is
 * located in storage group STG1 */

ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2) STORE IN stg2
    PARTITION p1 VALUES LESS THAN (301);
/* Error:  changing the storage group is disallowed, table TEST_TABLE is
 * located in storage group STG1 */

/* A different default storage group may be specified
   which will then be used by add partition */
ALTER TABLE test_table MODIFY PARTITION BY RANGE (f2) STORE IN stg2
    PARTITION p1 VALUES LESS THAN (301) STORE IN stg1;

ALTER TABLE test_table ADD PARTITION p2 VALUES LESS THAN (401);
选择 tablename, partitioningcolumn, defaultstoragegroup
    FROM system.partitionedtables WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONINGCOLUMN  DEFAULTSTORAGEGROUP
 ---------- ------------------- --------------------
 TEST_TABLE         F2                  STG2

选择 tablename, partitionname, storagegroup
    FROM system.partitionids WHERE tablename = 'TEST_TABLE';

 TABLENAME  PARTITIONNAME  STORAGEGROUP
 ---------- -------------- -------------
 TEST_TABLE       P2           STG2
 TEST_TABLE       P1           STG1