Oracle 与LightDB alter table 语法对比(部分)
本文对oracle(21)和lightdb(22.3)中常用的一些alter table 语法进行比较, 包括修改字段属性, 修改约束,修改表为分区表等。
1. 修改字段(包括添加删除)
1.1 修改字段属性
对常用的修改字段属性语法进行比较
功能 | oracle | lightdb | mysql | |
---|---|---|---|---|
修改默认值 | modify column default def_val | modify column default def_val alter column set default def_val | ||
修改为null | modify column null | modify column null alter column drop not null | ||
修改为 not null | modify column not null | modify column not null alter column set not null | ||
修改字段类型 | modify column Typename | modify column Typename alter [COLUMN] type Typename | ||
修改字段IDENTITY | modify GENERATED … AS IDENTITY OptParenthesizedSeqOptListmodify modify DROP IDENTITY | ALTER [COLUMN] ADD GENERATED … AS IDENTITY OptParenthesizedSeqOptList modify GENERATED … AS IDENTITY OptParenthesizedSeqOptList ALTER TABLE ALTER [COLUMN] DROP IDENTITY modify DROP IDENTITY | ||
修改字段类型 | modify Typename | ALTER_or_MODIFY opt_column ColId opt_set_data TYPE_P Typename opt_collate_clause alter_using modify Typename alter_using | ||
新增约束 | modify column inline_constraint | modify column inline_constraint | ||
此字段在插入null 时转换为默认值 | modify ColId default xxx | 不支持 | ||
其他 | encrypt/decrypt LOB_storage_clause alter_XMLSchema_clause modify_virtcol_properties modify_col_visibility modify_col_substitutable | 不支持 | ||
rename 字段 | RENAME COLUMN column_name TO new_column_name 必须有COLUMN关键字 | RENAME [ COLUMN ] column_name TO new_column_name |
oracle inline_constraint:
1.2. 添加字段
oracle 与lightdb 的常用的添加字段的用法相同 ,都可以通过如下方式添加字段:
ADD column_name column_type [ COLLATE collation ] [default xxx] [not null/null] [unique/primary key] [check ( expression )]
oracle 支持在一条sql 添加多个字段, lightdb 目前不支持:
add (column_def, column_def, ...)
具体各自的语法如下:
oracle语法
column_definition:
inline_constraint:
更具体的可以参考oracle官方文档1
lightdb 语法
ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
column_constraint:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
更具体的可以参考lightdb官方文档2
1.3. 删除字段
oracle 与lightdb 的常用的删除字段的用法相同 ,都可以通过如下方式删除字段:
drop COLUMN column_name
oracle 支持在一条sql 添加多个字段, lightdb 目前不支持, 却oracle 删除字段如果不带COLUMN关键字,需要把字段名括起来:
drop (column_name, column_name, ...)
具体各自的语法如下:
oracle 删除字段语法
lightdb 删除字段语法
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
2. 修改约束(包括添加删除)
下面对修改约束的基本用法进行比较, 各自的具体用法请参考具体语法
2.1. 添加约束
添加约束的常用语法也相同,支持如下:
ADD table_constraint
table_constraint:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
2.2. 删除约束
都支持如下基本用法:
DROP CONSTRAINT constraint_name [cascade]
DROP PRIMARY KEY [cascade]
oracle 还支持如下方式删除唯一索引, lightdb 暂不支持:
DROP UNIQUE(COLUMN_NAME, COLUMN_NAME, ...)
2.3. 修改约束状态
oracle 通过MODIFY关键字修改约束状态,lightdb 通过alter 关键字修改,且lightdb不支持修改通过PRIAMRY KEY/UNIQUE(COLUMN,…) 修改主键,唯一约束的状态。
2.4. rename 约束
相同, 语法如下:
RENAME CONSTRAINT constraint_name TO new_constraint_name
2.5 具体语法
oracle 修改约束语法
constraint_clauses:
out_of_line_constraint:
drop_constraint_clause:
lightdb 修改约束语法
ADD table_constraint [ NOT VALID ]
ADD table_constraint_using_index
ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
RENAME CONSTRAINT constraint_name TO new_constraint_name
VALIDATE CONSTRAINT constraint_name
table_constraint:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
table_constraint_using_index:
[ CONSTRAINT constraint_name ]
{ UNIQUE | PRIMARY KEY } USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
3. oracle外部表
lightdb 不支持类似oracle的外部表
4. 修改为分区表
lightdb 暂不支持
oracle 修改为分区表语法
5. 修改表属性
都支持常用的如下用法rename name 和修改collation
rename to new_name
DEFAULT COLLATION collation_name
oracle 修改表属性语法
alter_table_properties:
lightdb 修改表属性语法
RENAME TO new_name
SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
SET TABLESPACE new_tablespace [ NOWAIT ] -- 修改表空间
ALTER TABLE [ IF EXISTS ] name
ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
DETACH PARTITION partition_name
DISABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE TRIGGER [ trigger_name | ALL | USER ]
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name
DISABLE RULE rewrite_rule_name
ENABLE RULE rewrite_rule_name
ENABLE REPLICA RULE rewrite_rule_name
ENABLE ALWAYS RULE rewrite_rule_name
DISABLE ROW LEVEL SECURITY
ENABLE ROW LEVEL SECURITY
FORCE ROW LEVEL SECURITY
NO FORCE ROW LEVEL SECURITY
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
SET TABLESPACE new_tablespace
SET { LOGGED | UNLOGGED }
SET ( storage_parameter [= value] [, ... ] )
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }