Oracle adds and modifies deletion Fields

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

Alter table tablename modify (column datatype [default value] [null/not null],...);

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

Multiple columns are separated by commas.

Use alter table to add, delete, and modify a column.

Create a table structure:
Create Table test1
(ID varchar2 (20) not null );

Add a field:

Alter table test1
Add (name varchar2 (30) default 'Anonymous 'not null );

Use an SQL statement to add three fields at the same time:

Alter table test1
Add (name varchar2 (30) default 'Anonymous '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 ');

In addition, the more formal syntax 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;

It should be noted that if a value already exists in a column, an error will occur if you want to modify it to a column width smaller than these values.

For example, if we insert a value
Insert into test1
Values ('1', 'We love you ');

Then I modified the column: ALTER TABLE test1.
Modify (name varchar2 (8 ));
The following error is returned:
Error is located in row 2nd:
ORA-01441: cannot reduce column length because some values are too large

Bytes ---------------------------------------------------------------------------------------------------------------

Advanced usage:

Rename a table
ALTER TABLETable_nameRenameNew_table_name;

Modify column name

Syntax:
Alter table table_name rename column supplier_name to sname;

Example:
Alter table s_dept rename column age to age1;

Appendix: 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 when creating a table
(1) No Name
Create Table student (
Studentid int primary key not null,
Studentname varchar (8 ),
Age INT );
(2) named
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 Name
Select * From user_cons_columns;
Find the primary key name in the student table. The primary key name is sys_c002715.
Alter table student drop constraint sys_c002715;
(2) named
Alter table students drop constraint YY;

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