Delete the fields of Oracle database and change the primary key setting

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.

Creating 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 of 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 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

Syntax: 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 (1) Without naming the CREATE TABLE student (StudentID int primary key NOT NULL, Studentname varchar (8), and age int); (2) There is a named CREATE TABLE students (StudentID int, studentname varchar (8), age int, constraint yy primary key (StudentID));

2. Delete the existing PRIMARY KEY constraint (1) in the table without naming the available SELECT * from User_cons_columns; Find the primary key name in the table in the student table with the primary key named sys_c002715 ALTER TABLE student drop constraint sys_c002715; (2) There is a named ALTER TABLE students drop constraint yy;

3. Add the primary KEY constraint to the table ALTER TABLE student add constraint Pk_student primary key (StudentID);

Delete The fields of the Oracle database and change the primary key setting deletion