Frequently used change statements by Oracle

Tables table

Measured frequently: my_test_table

--Create tablecreate table my_test_table (  A VARCHAR2 (),  B number (Ten) default 1,  C DATE) tablespace bosswg_ CFG  pctfree  initrans 1  maxtrans 255  Storage  (    initial 64K    minextents 1    Maxextents Unlimited  );--add comments to the table comment on table my_test_table is  ' TEST ';--add comments to The columns comment on column my_test_table. A is  ' aaaa '; Comment on column my_test_table. B is  ' bbbb '; Comment on column my_test_table. C is  ' CCCC ';

0. Add a field

ALTER TABLE my_test_table add D VARCHAR2 (50);
--ADD comments to the columns
Comment on column my_test_table. D
is ' dddddd ';

1. Change the name of the table field

ALTER TABLE my_test_table Rename column A to D; Change the A field name to D

2. change table field type

ALTER TABLE my_test_table Modify B varchar2 (10); Change the number (10) Type of the B field to VARCHAR2 (10)

3. Change the default value of the table field

ALTER TABLE my_test_table modify B default 2; Change the default value of the B field to 1 to 2

4. Change the non-empty properties of a table field

ALTER TABLE my_test_table modify B NOT null; Change the B field to non-empty

5. Change the Notes of the table field

Comment on column my_test_table. b//Change the memo value of the B field
is ' bbbb2 ';

6. Primary KEY operation

ALTER TABLE my_test_table
Add constraint pk_my_test_table primary key (A); Join the primary key

ALTER TABLE my_test_table
Drop constraint pk_my_test_table cascade; Delete primary key

Suppose you want to change the primary key. That is, you must first delete the primary key and then join the primary key.

7.check constrained operation

ALTER TABLE my_test_table
Add constraint ckc_my_test_table_b//join constraint
Check (B in (+/-));

ALTER TABLE my_test_table
Drop constraint Ckc_my_test_table_b; Delete Constraint

Suppose you want to change the constraint. You must first delete the constraint and then join the constraint.

8. Foreign KEY operation

ALTER TABLE my_test_table
Add constraint fk_my_test_dtable_a foreign key (A)//Join FOREIGN key
References My_test_table2 (A) on DELETE cascade;

ALTER TABLE my_test_table
Drop constraint fk_my_test_dtable_a; Delete foreign key

Suppose you want to change the foreign key. You must first delete the foreign key and then join the foreign key

Second, sequence seq

Test sequence: My_test_seq

Create sequence My_test_seqminvalue 1maxvalue 9999999999start with 1increment by 1cache 20;

1. Change the MinValue and MaxValue values:

Alter sequence MY_TEST_SEQ
MinValue 20
MaxValue 99999999999;

2. Change the NextValue value:

Alter sequence MY_TEST_SEQ increment by 8 nocache; The original NextValue value is 2. Change to NextValue value of 10
Select My_test_seq.nextval from dual;
Alter sequence MY_TEST_SEQ increment by 1 cache 20; This sentence must have. Otherwise the cache and growth rate will be reset

Alter sequence MY_TEST_SEQ increment by-8 nocache; From the original NextValue value of 10, change to NextValue value of 2
Select My_test_seq.nextval from dual;
Alter sequence MY_TEST_SEQ increment by 1 cache 20; This sentence must have. Otherwise the cache and growth rate will be reset

Frequently used change statements by Oracle