ALTER TABLE
Function
Modifies tables, including modifying table definitions, renaming tables, renaming specified columns in tables, renaming table constraints, setting table schemas, enabling or disabling row-level security policies, and adding or updating multiple columns.
Precautions
- This section describes only the new syntax of Dolphin. The original syntax of openGauss is not deleted or modified.
- If a statement contains multiple subcommands, the DROP INDEX and RENAME INDEX commands are executed first. The two commands have the same priority.
Syntax
ALTER TABLE modifies the definition of a table.
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) } action [, ... ];
The action can be one of the following clauses:
column_clause | {DISABLE | ENABLE} KEYS | DROP INDEX index_name [ RESTRICT | CASCADE ] | DROP PRIMARY KEY [ RESTRICT | CASCADE ] | DROP FOREIGN KEY foreign_key_name [ RESTRICT | CASCADE ] | RENAME INDEX index_name to new_index_name | ADD table_indexclause | MODIFY column_name column_type ON UPDATE CURRENT_TIMESTAMP
Recreate a table.
ALTER TABLE table_name FORCE;
Rename a table. The renaming does not affect stored data.
ALTER TABLE [ IF EXISTS ] table_name RENAME { TO | AS } new_table_name;
Add the ON UPDATE attribute to the timestamp column of the table.
ALTER TABLE table_name MODIFY column_name column_type ON UPDATE CURRENT_TIMESTAMP;
Delete the ON UPDATE attribute from the timestamp column of the table.
ALTER TABLE table_name MODIFY column_name column_type;
ADD table_indexclause
Add an index to the table.
{INDEX | KEY} [index_name] [index_type] (key_part,...)[index_option]...
Values of index_type are as follows:
USING {BTREE | HASH | GIN | GIST | PSORT | UBTREE}
Values of key_part are as follows:
{col_name[(length)] | (expr)} [ASC | DESC]
The index_option parameter is as follows:
index_option:{ COMMENT 'string' | index_type }
The sequence and quantity of COMMENT and index_type can be random, but only the last value of the same column takes effect.
Parameter Description
{DISABLE | ENABLE} KEYS
Disables or enables all non-unique indexes of a table.
DROP INDEX index_name [ RESTRICT | CASCADE ]
Deletes the index of a table.
DROP PRIMARY KEY [ RESTRICT | CASCADE ]
Deletes the foreign key of a table.
DROP FOREIGN KEY foreign_key_name [ RESTRICT | CASCADE ]
Deletes the foreign key of a table.
RENAME INDEX index_name to new_index_name
Renames an index of a table.
NOTE:
For details about the involved parameters, see ALTER TABLE.
Examples
— Create tables, foreign keys, and non-unique indexes.
openGauss=# CREATE TABLE alter_table_tbl1 (a INT PRIMARY KEY, b INT);
openGauss=# CREATE TABLE alter_table_tbl2 (c INT PRIMARY KEY, d INT);
openGauss=# ALTER TABLE alter_table_tbl2 ADD CONSTRAINT alter_table_tbl_fk FOREIGN KEY (d) REFERENCES alter_table_tbl1 (a);
openGauss=# CREATE INDEX alter_table_tbl_b_ind ON alter_table_tbl1(b);
— Disable and enable non-unique indexes.
openGauss=# ALTER TABLE alter_table_tbl1 DISABLE KEYS;
openGauss=# ALTER TABLE alter_table_tbl1 ENABLE KEYS;
— Delete the index.
openGauss=# ALTER TABLE alter_table_tbl1 DROP KEY alter_table_tbl_b_ind;
— Deletes a primary key.
openGauss=# ALTER TABLE alter_table_tbl2 DROP PRIMARY KEY;
— Delete a foreign key.
openGauss=# ALTER TABLE alter_table_tbl2 DROP FOREIGN KEY alter_table_tbl_fk;
— Recreate a table.
openGauss=# ALTER TABLE alter_table_tbl1 FORCE;
— Rename the index.
openGauss=# CREATE INDEX alter_table_tbl_b_ind ON alter_table_tbl1(b);
openGauss=# ALTER TABLE alter_table_tbl1 RENAME INDEX alter_table_tbl_b_ind TO new_alter_table_tbl_b_ind;
— Delete a table.
openGauss=# DROP TABLE alter_table_tbl1, alter_table_tbl2;