Oracle Add method to modify Delete field/primary key

Oracle Modify Field Name

ALTER TABLE XGJ Rename column old_name to new_name;
Modify a field type

ALTER TABLE tablename Modify (column datatype [default value][null/not null],....);
Example

Assuming table xgj, there is a field name, data type char (20).

CREATE TABLE Xgj (
ID Number (9),
Name Char (20)
)
1, the field is empty, no matter what type of field to change, you can directly execute:

Sql> select * from Xgj;

ID NAME
---------- --------------------

Sql> ALTER TABLE XGJ Modify (name Varchar2 (20));

Table Altered

Sql>
2, the field has data, if compatible, change to VARCHAR2 (20) can be directly executed:

--Change the type of name to char (20) at creation time, immediately following the first case operation
Sql> ALTER TABLE XGJ Modify (name char (20));

Table Altered
--Inserting data
sql> INSERT INTO XGJ (id,name) VALUES (1, ' Xiaogongjiang ');

1 row inserted

Sql> select * from Xgj;

ID NAME
---------- --------------------
1 Xiaogongjiang

Sql> ALTER TABLE XGJ Modify (name Varchar2 (20));

Table Altered

sql> desc XGJ;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID Number (9) Y
NAME VARCHAR2 Y

Sql> ALTER TABLE XGJ Modify (name VARCHAR2 (40));
Table Altered

Sql> ALTER TABLE XGJ Modify (name char (20));
Table Altered
3, the field has data, when the modified type and the original type is incompatible, when the execution will pop up: "ORA-01439: To change the data type, the column to be modified must be empty"

Chestnuts:

--Build the table
CREATE TABLE Xgj (col1 number, col2 number);
--Inserting data
Insert into XGJ (col1,col2) values (1,2);
--Submit
commit;
--Modify col1 changed from number to VARCHAR2 type (incompatible type)
ALTER TABLE XGJ Modify (col1 varchar2 (20))

Solution:

Modify the original field name col1 to col1 _tmp
ALTER TABLE XGJ Rename column col1 to col1_tmp;
Add a field with the same name as the original field col1
ALTER TABLE XGJ add col1 varchar2 (20);
Update the original field COL1_TMP data to the added field col1
Update XGJ set Col1=trim (col1_tmp);
After the update, delete the original field col1_tmp
ALTER TABLE XGJ drop column col1_tmp;
Summarize:

1, when the field does not have data or to modify the new type and the original type compatible, you can directly modify modification.

2, when the field has data and to modify the new type and the original type is incompatible, to indirectly create a new field to transfer.

Add Field

ALTER TABLE tablename Add (column datatype [default value][null/not null],....);
Add multiple fields at the same time with one SQL statement:

ALTER TABLE XGJ

Add (name varchar2) default ' anonymous ' NOT NULL,

Age integer default is null,

Salary Number (9,2)

);
Delete a field

ALTER TABLE tablename drop (column);
To create a table with a primary key

CREATE TABLE Student (
StudentID int PRIMARY key NOT NULL,
Studentname varchar (8),
age int);
1, create a table while creating a PRIMARY KEY constraint

(1) No naming

CREATE TABLE Student (
StudentID int PRIMARY key NOT NULL,
Studentname varchar (8),
age int);
(2) having a 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 naming

Available with SELECT * from User_cons_columns;

Find the primary key name in the table student the primary key in the table is sys_c002715

ALTER TABLE student drop constraint sys_c002715;
(2) having a 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);