How to modify the table structure in oracle

In Oracle, you can use the "ALTER TABLE" statement to modify the table structure. This statement can change the original table by adding or deleting columns, changing the original column type, renaming columns or tables, etc. Structure, syntax "ALTER TABLE table name [modify options]". ALTER TABLE table name [Modify options]

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

In Oracle, you can use the "ALTER TABLE" statement to modify the table structure.

ALTER TABLEstatement can change the structure of the original table, such as adding or deleting columns, changing the original column type, renaming columns or tables, etc.

ALTER TABLE statement syntax:

ALTER TABLE 表名 [修改选项]

In the above statement,

• First, specify the name of the table to be modified.

• Secondly, indicate the modification operation you want to perform after the table name.

ALTER TABLEstatement can be used:

• Add one or more columns
• Modify column definitions
• Delete one or more columns
• Rename columns
• Rename table

Let’s look at some examples to understand how each operation works .

Oracle ALTER TABLE Example

We will use thepersonstable created in the previous tutorial for demonstration.

-- 12c语法 CREATE TABLE persons( person_id NUMBER GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2(50) NOT NULL, last_name VARCHAR2(50) NOT NULL, PRIMARY KEY(person_id) );

1. Example of adding a column

To add a new column to the table, use the following syntax:

ALTER TABLE table_name ADD column_name type constraint;

For example, the following statement Add a new column namedbirthdateto thepersonstable:

ALTER TABLE persons ADD birthdate DATE NOT NULL;

If you look at thepersonstable, you will seebirthdateThe date column is added to the end of the column list:

DESC persons; Name Null Type ---------- -------- ------------ PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) BIRTHDATE NOT NULL DATE

To add multiple columns to the table at the same time, enclose the new columns in brackets as follows:

ALTER TABLE table_name ADD ( column_name type constraint, column_name type constraint, ... );

Look at the following example:

ALTER TABLE persons ADD ( phone VARCHAR(20), email VARCHAR(100) );

In this example, the statement adds two names namedphoneandemailto theuserstable. new column.

DESC persons Name Null Type ---------- -------- ------------- PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) BIRTHDATE NOT NULL DATE PHONE VARCHAR2(20) EMAIL VARCHAR2(100)

2. Example of modifying the properties of a column

To modify the properties of a column, use the following syntax:

ALTER TABLE table_name MODIFY column_name type constraint;

For example, the following statement changes thebirthdatecolumn to an empty column:

ALTER TABLE persons MODIFY birthdate DATE NULL;

We verify thepersonstable structure again:

DESC persons Name Null Type ---------- -------- ------------- PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) BIRTHDATE DATE PHONE VARCHAR2(20) EMAIL VARCHAR2(100)

As shown As you can see, thebirthdatedate becomes powerless.

To modify multiple columns, use the following syntax:

ALTER TABLE table_name MODIFY ( column_1 type constraint, column_1 type constraint, ...);

For example, the following statement changes thephoneandemailcolumns toNOT NULLcolumn, and extend the length of theemailcolumn to255characters:

ALTER TABLE persons MODIFY( phone VARCHAR2(20) NOT NULL, email VARCHAR2(255) NOT NULL );

Verify thepersonstable structure again:

DESC persons; Name Null Type ---------- -------- ------------- PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) BIRTHDATE DATE PHONE NOT NULL VARCHAR2(20) EMAIL NOT NULL VARCHAR2(255)

3. Example of deleting an existing column

To delete an existing column from a table, use the following syntax:

ALTER TABLE table_name DROP COLUMN column_name;

This statement removes a column from the table structure and deletes the data stored in that column. The following example deletes thebirthdatedate type column from thepersonstable:

ALTER TABLE persons DROP COLUMN birthdate;

Look at thepersonstable structure again and you will findbirthdatecolumn has been deleted:

DESC persons; Name Null Type ---------- -------- ------------- PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50) PHONE NOT NULL VARCHAR2(20) EMAIL NOT NULL VARCHAR2(255)

To delete multiple columns at the same time, use the following syntax:

ALTER TABLE table_name DROP (column_1,column_2,...);

For example, the following statement deletesphone## from the personal table # andemailcolumns:

ALTER TABLE persons DROP ( email, phone );

Let’s take a look at the

personstable:

DESC persons; Name Null Type ---------- -------- ------------ PERSON_ID NOT NULL NUMBER FIRST_NAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50)

emailandphoneThe column was deleted as expected.

4. Example of renaming a column

Starting with version 9i, Oracle added a clause for renaming a column as follows:

ALTER TABLE table_name RENAME COLUMN column_name TO new_name;

For example, The following statement renames the

first_namecolumn to theforenamecolumn:

ALTER TABLE persons RENAME COLUMN first_name TO forename;

The following statement checks the result:

DESC persons; Name Null Type --------- -------- ------------ PERSON_ID NOT NULL NUMBER FORENAME NOT NULL VARCHAR2(50) LAST_NAME NOT NULL VARCHAR2(50)

5. Rename the table Example

To rename a table to a new

nametable name, use the following syntax:

ALTER TABLE table_name RENAME TO new_table_name;

For example, the following statement will

userstable is renamed topeopletable:

ALTER TABLE persons RENAME TO people;

Recommended tutorial: "

Oracle Tutorial"

The above is the detailed content of How to modify the table structure in oracle. For more information, please follow other related articles on the PHP Chinese website!