oracle delete table field and oracle table add field

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′,'我们很爱你');

and then modified the column: alter table test1
modify (name varchar2(8));
will You get the following error:
ERROR at line 2:
ORA-01441: Unable to reduce column length because some values ​​are too large

Advanced usage:

Rename table
ALTER TABLE table_name RENAME TO new_table_name;

Modify the name of the column

Syntax:
ALTER TABLE table_name RENAME COLUMN supplier_name to sname;

Example:
alter table s_dept rename column age to age1;

Attachment: Create a table with a primary key>>

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

1. Create a primary key constraint while creating the table
(1) No naming

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!