ALTER TABLE

Synopsis

Use the ALTER TABLE statement to change the definition of an existing table.

Syntax

• Grammar
• Diagram

alter_table ::= ALTER TABLE [ ONLY ] name [ * ] alter_table_action 
                [ , ... ]
alter_table_action ::= ADD [ COLUMN ] column_name data_type
                       | RENAME TO table_name
                       | DROP [ COLUMN ] column_name 
                         [ RESTRICT | CASCADE ]
                       | ADD alter_table_constraint
                       | DROP CONSTRAINT constraint_name 
                         [ RESTRICT | CASCADE ]
                       | RENAME [ COLUMN ] column_name TO column_name
                       | DISABLE ROW LEVEL SECURITY
                       | ENABLE ROW LEVEL SECURITY
                       | FORCE ROW LEVEL SECURITY
                       | NO FORCE ROW LEVEL SECURITY
alter_table_constraint ::= [ CONSTRAINT constraint_name ] 
                           { CHECK ( expression )
                             | UNIQUE ( column_names ) 
                               index_parameters
                             | FOREIGN KEY ( column_names ) 
                               references_clause }

alter_table

alter_table_action

alter_table_constraint

Semantics

alter_table

ALTER TABLE [ ONLY ] name [ * ] alter_table_action [ , … ]

Alter the specified table and dependencies.

• ONLY — Limit the change to the specified table.

alter_table_action

Specify one of the following actions.

ADD [ COLUMN ] column_name data_type

Add the specified column with the specified data type.

RENAME TO table_name

Rename the table to the specified table name.

DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ]

Drop the named column from the table.

• RESTRICT — Remove only the specified

ADD alter_table_constraint

Add the specified constraint to the table. For descriptions of valid table_constraint values, see CREATE TABLE.

DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]

Drop the named constraint from the table.

• RESTRICT — Remove only the specified constraint.
• CASCADE — Remove the specified constraint and any dependencies.

RENAME [ COLUMN ] column_name TO column_name

Rename a column to the specified name.

ENABLE / DISABLE ROW LEVEL SECURITY

This enables or disables row level security for the table.If enabled and no policies exist for the table, then a default-deny policy is applied.If disabled, then existing policies for the table will not be applied and will be ignored.See CREATE POLICY for details on how to create row level security policies.

FORCE / NO FORCE ROW LEVEL SECURITY

This controls the application of row security policies for the table when the user is the table owner.If enabled, row level security policies will be applied when the user is the table owner.If disabled (the default) then row level security will not be applied when the user is the table owner.See CREATE POLICY for details on how to create row level security policies.

alter_table_constraint

Specify a table constraint.

CONSTRAINT constraint_name

Specify the name of the constraint.

CHECK ( expression ) | FOREIGN KEY ( column_names ) reference_clause

See also

• CREATE TABLE