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 TABLE statement 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 TABLE statement 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 the persons table 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 named birthdate to the persons table:

ALTER TABLE persons 
ADD birthdate DATE NOT NULL;

If you look at the persons table, you will see birthdate The 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 named phone and email to the users table. 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 the birthdate column to an empty column:

ALTER TABLE persons MODIFY birthdate DATE NULL;

We verify the persons table 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, the birthdate date 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 the phone and email columns to NOT NULL column, and extend the length of the email column to 255 characters:

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

Verify the persons table 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 the birthdate date type column from the persons table:

ALTER TABLE persons
DROP
  COLUMN birthdate;

Look at the persons table structure again and you will find birthdate column 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 deletes phone## from the personal table # and email columns:

ALTER TABLE persons
DROP
  ( email, phone );

Let’s take a look at the

persons table:

DESC persons;

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

email and phone The 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_name column to the forename column:

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

name table name, use the following syntax:

ALTER TABLE table_name
RENAME TO new_table_name;

For example, the following statement will

users table is renamed to people table:

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!