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