Syntax for adding fields: alter table tablename add (column datatype [default value][null/not null],….);

Syntax for modifying fields: alter table tablename modify (column datatype [default value][null/not null],….);

Syntax for deleting fields: alter table tablename drop (column);

If you add, modify, or delete multiple columns, separate them with commas open.

Example of using alter table to add, delete and modify a column.

Create table structure:
create table test1
(id varchar2(20) not null);

Add a field:

alter table test1
add (name varchar2(30) default ‘無名氏' not null);

Use a SQL statement Add three fields at the same time:

alter table test1
add (name varchar2(30) default ‘無名氏' not null,
age integer default 22 not null,
has_money number(9,2)
);

Modify a field

alter table test1
modify (name varchar2(16) default ‘unknown');

Another: The more formal way of writing is:

-- Add/modify columns 
alter table TABLE_NAME rename column FIELD_NAME to NEW_FIELD_NAME;

Delete a field

alter table test1
drop column name;

Need to pay attention The problem is that if there are already values ??in a column, an error will occur if you want to modify the column width to be smaller than these values.

For example, if we inserted a value before

insert into test1
values ('1′,'我們很愛你');

create table student (
studentid int primary key not null,
studentname varchar(8),
age int);

create table student (
studentid int primary key not null,
studentname varchar(8),
age int);

(2) Naming

create table students (
studentid int ,
studentname varchar(8),
age int,
constraint yy primary key(studentid));

2. Delete the existing primary key constraints in the table
(1) No naming
Available SELECT * from user_cons_columns;
Look up the primary key name in the table and get the primary key name in the student table SYS_C002715
alter table student drop constraint SYS_C002715;
(2) with naming
alter table students drop constraint yy;

3. Add primary key constraints to the table
alter table student add constraint pk_student primary key(studentid);

For more articles related to oracle deleting table fields and oracle table adding fields, please pay attention to PHP Chinese website!