Search This Blog

Total Pageviews

Alter Table Examples.

How to add a constraint to table

ALTER TABLE table_name   ADD CONSTRAINT constraint_name FOREIGN KEY (col_1, col_2)    REFERENCES table_2 (cola,colb);

Removing a constraint

alter table table_name drop constraint constraint_name;

Adding a unique constraint

The following example adds a unique constraint on the columncolumn_name in the table whose name is table_name. The name of the constraint is constraint_name.
alter table table_name   add constraint constraint_name   unique (column_name) 

Disabling constraints

Integrity constraints can be disabled with the alter table command.
alter table table-name disable constraint-specification; alter table table-name disable constraint constraint-name; 

Adding a new Column

alter table foo_table add bar_column char(1); 

Modifying a column

Renaming a column name

alter table some_table rename column column_name to new_column_name; 

Changing a column's type

A column's type can be changed if the column is empty within the entire table, otherwise Oracle gives an ORA-01440: column to be modified must be empty to decrease precision or scale.
alter table some_table modify (column_name number); 

Changing null to not null or vice versa

alter table some_table modify (column_name not null); 
alter table some_table modify col_not_null number null; 
The parenthesis after the modify are optional.

alter table ... move

Alter table ... move partition

This is one of the few statements that can make use of thenologging option.

Alter table .. rename to ...

alter table old_table_name rename to new_table_name; 

Alter table ... split partition

This is one of the few statements that can make use of thenologging option.

Specifying tablespace for index

alter table tbl add constraint pk_tbl primary key (col_1, col_2) using index tablespace ts_idx

No comments:

java.lang.NoSuchFieldError: Class com.sun.tools.javac.tree.JCTree$JCImport

SpringBoot Error: java.lang.NoSuchFieldError: Class com.sun.tools.javac.tree.JCTree$JCImport does not have member field 'com.sun.tools.j...