Oracle's frequently used modification statements

Tables table

Test table: 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 ';

1. modify table field name

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

2. modifying table field types

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

3. Modify table field default values

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

4. Modify 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. Modify the Notes of a table field

Comment on column my_test_table. b//Modify 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); Add primary Key

ALTER TABLE my_test_table
Drop constraint pk_my_test_table cascade; Delete primary key

If you want to modify the primary key, you must first delete the primary key and then add the primary key.

7.check constrained operation

ALTER TABLE my_test_table
Add constraint Ckc_my_test_table_b//Add Constraint
Check (B in (+/-));

ALTER TABLE my_test_table
Drop constraint Ckc_my_test_table_b; Delete Constraint

If you want to modify a constraint, you must first delete the constraint and then add the constraint.

8. Foreign KEY operation

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

ALTER TABLE my_test_table
Drop constraint fk_my_test_dtable_a; Delete foreign key

If you want to modify the foreign key, you must first delete the foreign key, and then add the foreign key

Second, sequence seq

Test sequence: My_test_seq

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

1. Modify the MinValue and MaxValue values:

Alter sequence MY_TEST_SEQ
MinValue 20
MaxValue 99999999999;

2. Modify the NextValue value:

Alter sequence MY_TEST_SEQ increment by 8 nocache; By the original NextValue value of 2, modified to NextValue value of 10
Select My_test_seq.nextval from dual;
Alter sequence MY_TEST_SEQ increment by 1 cache 20; This must be true, otherwise the cache and growth rate will be reset

Alter sequence MY_TEST_SEQ increment by-8 nocache; By the original NextValue value of 10, modified to NextValue value of 2
Select My_test_seq.nextval from dual;
Alter sequence MY_TEST_SEQ increment by 1 cache 20; This must be true, otherwise the cache and growth rate will be reset