ORACLE: ALTER TABLE

This section describes how to use the alter table statement to modify a table. The alter table statement can execute the following tasks:

1. Add, modify, or delete Columns

2. add or delete Constraints

3. enable or disable Constraints

1. Add a column (alter table table_name add column name type)

(1) Add a column named modified_by to order_status2, whose type is integer.

SQL>alter table order_status2 add modified_by integer;

Add a column named initially_created to the order_status2 table. The column type is date and the default value is sysdate.

⑵、SQL>alter table order_status2 add initially_created date default sysdate not null;

Ii. alter table table_name Modify column name ....)

1. Modify the length of a column. The condition is that the length of the column type can be modified, for example, Char or varchar2.

2. Modify the precision of the numeric Column

3. Modify the column data type

4. Modify the column Default Value

The following are examples:

(1) modify the Column Length

Increase the length of the Status column in order_status2 from 10 to 20 (type: varchar2)

SQL>alter table order_status2 modify status varchar2(20);

Note: The column length can be reduced only when no row or all columns in the table are null.

(2) precision of modifying numeric Columns

Change the precision of the ID column in order_status2 from 40 to 20 (type: Number)

SQL>alter table order_status2 modify id number(20);

Note: The precision of numeric columns can be reduced only when no rows or all columns in the table are null.

(3) modify the Data Type of a column

Change the Data Type of the Status column from varchar2 to Char in the order_status2 table.

SQL>alter table order_status2 modify status char(20);

(4) modify the column Default Value

Change the default value of the last_modified column in The order_status2 table to the sysdate-1

SQL>alter table order_status2 modify last_modified default sysdate-1;

⑸ Delete column

Delete the initially_creaded column in The order_status2 table.

SQL>alter table order_status2 drop column initially_created;

3. Add constraints (check, not null, primary key, foreign key, unique, check option, read only, etc)

(1) Add check Constraints

Add a check constraint to the Status column in order_status2.

SQL>alter table order_status2 add constraint order_status2_status_chk check (status in (‘PLACED’,’PENDING’,’SHIPPED’));

Add an ID constraint. The id value must be greater than 0;

SQL>alter table order_status2 add constraint order_status2_id_chk check (id>0);

(2) Add a not null Constraint

Add a not null constraint to the Status column of the order_status2 table.

SQL>alter table order_status2 modify status constraint order_status2_status_nn not null;

Add a not null constraint to the modified_by Column

SQL>alter table order_status2 modify modified_by constraint order_status2_modified_by_nn not null;

SQL>alter table order_status2 modify last_modified not null;

(3) Add a foreign key constraint

Use alter table to first Delete the modified_by column from order_status2, and then add a foreign key constraint that references the employees. employee_id column;

SQL>alter table order_status2 drop column modified_by;

SQL>alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employees(employee_id);

A On Delete cascade clause with the foreign key constraint can be used to specify that when a row of records is deleted in the parent table, all matched rows in the child table will also be deleted.

SQL>alter table order_status2 drop column modified_by;

SQL>alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employee(employee_id) on delete cascade;

That is, when a row of records is deleted in the employee table, all matched rows in the order_status2 table will also be deleted.

SQL>alter table order_status2 add constraint order_status2_modified_by_fk modified_by references employee(employee_id) on delete set null;

That is, when a row of records is deleted in the employee table, all matched rows in the order_status2 table will also be set to null.

(4) Add a unique constraint

Add a unique constraint to the Status column of the order_status2 table.

SQL>alter table order_status2 add constraint order_status2_status_uq unique(status);

Constraint Deletion

Use the drop constraint clause of alter table to delete constraints.

SQL>alter table order_status2 drop constraint order_status2_status_uq;

Forbidden Constraint

Add and disable a unique constraint

SQL>alter table order_status2 add constraint order_status2_status_uq unique(status) disable;

Disable an existing Constraint

SQL>alter table order_status2 disable constraint order_status2_status_nn;

Constraint enabling

The following describes how to enable the order_status2_status_uq constraint:

SQL>alter table order_status2 enble constraint order_status2_status_uq;

By specifying enable novalidate, you can select to apply only a constraint to the new data.

SQL>alter table order_status2 enable novalidate constraint order_status2_status_uq;

Latency Constraints

Deferred constraint is a constraint that is enforced when a transaction is committed.

Initially immediate: Check this constraint every time you add data to, modify, or delete data in the table (this is the same as the default behavior of the constraint)

Initially deferred: constraints are checked only when a transaction is committed (that is, the commit command is executed ).

SQL>alter table order_status2

add constraint order_status2_status_uq unique(status)

deferrable initially deferred|immediate;

Obtain related constraint information

Query user_constraints to obtain the constraints.

Use all_constraints to obtain information about all the access constraints.

SQL>select constraint_name,constraint_type,status,deferrable,deferred

         From user_constraints

         Where table_name=upper(‘order_status2’);

Obtain constraint information about Columns

You can query user_cons_columns to obtain the constraint information about columns.

Use all_con_columns to obtain the constraint information of all accessible columns.

SQL>column column_name format a15

SQL>select constraint_name,column_name

        From user_cons_columns

        Where table_name=upper(‘order_status2’);

The following is a joint query of user_constraints and user_cons_columns.

SQL>select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status

        From user_constraints uc,user_cons_columns ucc

        Where uc.table_name=ucc.table_name

        And uc.constraint_name=ucc.constraint_name

        And ucc.table_name=upper(‘order_status2’);

⑼ Rename a table

SQL>rename order_status2 to order_state;

Comment

Add a comment to the order_status2 table.

SQL>comment on table order_status2 is ‘order_status2 stores the of an order’;

Add comments to the order_status2.last_modified Column

SQL>comment on column order_status2.last_modified is ‘last_modified stores the date and time the order was modified last’;

Use the user_tab_comments view to obtain Table comments.

SQL>select * from user_tab_comments where table_name=’ORDER_STATUS2’;

Use the user_col_comments view to obtain comments about columns.

SQL>select * from user_col_comments where table_name=’ORDER_STATUS2’;

Partition truncation table

SQL> truncate table order_status2)

SQL>drop table order_status2;