Oracle Remove table fields and Oracle table add Field

This article mainly describes the Oracle table add field, delete table fields to modify the use of table, please refer to the use of it

Syntax for adding a field: ALTER TABLE tablename Add (column datatype [default value][null/not null],....);

Modify the syntax of the field: ALTER TABLE tablename modify (column datatype [default value][null/not null],....);

Syntax for deleting a field: ALTER TABLE tablename drop (column);

Add, modify, and delete multiple columns, separated by commas.

Use ALTER TABLE to add, delete, and modify an example of a column.

To create a table structure:
CREATE TABLE Test1
(ID varchar2 () not NULL);

Add a field:

Copy CodeThe code is as follows:
ALTER TABLE Test1
Add (name VARCHAR2 () default ' anonymous ' not NULL);

Add three fields using one SQL statement at a time:

Copy CodeThe code is as follows:
ALTER TABLE Test1
Add (name VARCHAR2 () default ' anonymous ' NOT NULL,

The age integer default is not NULL,

Has_money Number (9,2)

);

Modify a field

Copy CodeThe code is as follows:
ALTER TABLE Test1
Modify (name VARCHAR2 (+) Default ' unknown ');

Another: The more formal wording is:
Copy CodeThe code is as follows:
--Add/modify columns
ALTER TABLE table_name Rename column field_name to New_field_name;

Delete a field
Copy CodeThe code is as follows:
ALTER TABLE Test1
Drop column name;

Note that if a value already exists in a column, there will be an error if you want to modify the column widths that are smaller than the values.

For example, if we insert a value in front

Copy CodeThe code is as follows:
INSERT INTO Test1
VALUES (' 1′, ' We love you very much ');

The column was then modified: ALTER TABLE TEST1
Modify (name VARCHAR2 (8));
You will get the following error:
ERROR on line 2nd:
ORA-01441: cannot reduce column length because some values are too large

Advanced usage:

Renaming a table
ALTER TABLE table_name RENAME to new_table_name;

Modify the name of a column

Grammar:
ALTER TABLE table_name RENAME COLUMN supplier_name to sname;

Example:
ALTER TABLE s_dept Rename column age to Age1;

Attached: Creating a table with a primary key >>

Copy CodeThe code is as follows:
CREATE TABLE Student (
StudentID int PRIMARY key NOT NULL,
Studentname varchar (8),
age int);

1. Create a PRIMARY KEY constraint while creating a table
(1) No naming

Copy CodeThe code is as follows:
CREATE TABLE Student (
StudentID int PRIMARY key NOT NULL,
Studentname varchar (8),
age int);

(2) have a name

Copy CodeThe code is as follows:
CREATE TABLE Students (
StudentID int,
Studentname varchar (8),
Age int,
Constraint yy primary KEY (StudentID));

2. Delete the existing PRIMARY KEY constraint in the table
(1) No naming
Available as SELECT * from User_cons_columns;
Find primary key name in table student table with primary key named sys_c002715
ALTER TABLE student drop constraint sys_c002715;
(2) have a name
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);

Oracle Remove table fields and Oracle table add Field