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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.