Oracle alter operation

Because Oracle has a slightly different format for column addition, deletion and modification, it is hereby recorded in order to facilitate future viewing.

1. Add columns:

SQL > alter table T1 add y integer

Table altered

Executed in 0.046 seconds

Description: add column format to alter table table name add add column name data type

2. Delete columns

SQL > alter table T1 drop column y

Table altered

Executed in 0.015 seconds

Description: delete a single column in the format of alter table table name drop column column name

3. Modify the column

SQL > alter table T1 modify i number (10)

Description: modify the format of the column to alter table table name modify column name data type

Special note: if you need to change the column data type, you need to back up the column data before conversion and clear the column data, otherwise the change will fail!

4. Rename the column

SQL > alter table T1 rename column i to x

Table altered

Executed in 0.062 seconds

Note: the format of renamed column is alter table table name rename column column name to new column name;,

5. Delete multiple columns

SQL > alter table t drop (afort b)

Table altered

Executed in 1.56 seconds

Description: delete multiple columns at the same time in the format of alter table table name drop (column name 1, column name 2. Column name N)

At the same time, it should be noted that deleting multiple columns at the same time cannot delete all the columns in the table, and it doesn't make any sense if you delete the table.

6. Update the primary key

If there is a primary key before the table, it needs to be deleted

Alter table table name drop constraint primary key name

Add a new primary key

Alter table table name add constraint primary key name primary key (columns in the table)

Note: the primary key name is custom and may not be the same as the column name.

Finally, with a little disdain for oracle developers, the format of sql command statements is not at all standard and too casual!