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:
ALTER TABLE Test1
Add (name VARCHAR2 () default ' anonymous ' not NULL);
Add three fields using one SQL statement at a time:
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
ALTER TABLE Test1
Modify (name VARCHAR2 (+) Default ' unknown ');
Another: The more formal wording 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;
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
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 >>
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
CREATE TABLE Student (
StudentID int PRIMARY key NOT NULL,
Studentname varchar (8),
age int);
(2) have a name
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);
CREATE TABLE Test1 (
ID VARCHAR2 (+) primary key,
Name VARCHAR2 (8) is not NULL,
Age number,
)
To add a table comment:
COMMENT on table test1 is ' personal information ';
To add a field comment:
Comment on column test1.id is ' ID ';
Comment on column test1.nameis ' name ';
Comment on column test1.age is ' age ';
Oracle adds modified delete field and add comment