Oracle主键、外键、索引

增加主键

alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);

指定表空间

alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN) using index  tablespace TABLE_SPACE_NAME;

增加外键

alter table TABLE_NAME add constraint FK_NAME  foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;

使主键或外键失效、生效

alter table TABLE_NAME  disable(enable) constraint KEY_NAME;

查看各种约束

select constraint_name,table_name,constraint_type,status from user_constraints;
select constraint_name, constraint_type,search_condition, r_constraint_name  from user_constraints where table_name = upper('&table_name')
select c.constraint_name,c.constraint_type,cc.column_name  
     from user_constraints c,user_cons_columns cc
     where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
     and c.owner = cc.owner and c.constraint_name = cc.constraint_name
     order by cc.position;

删除主键或外键

alter table TABLE_NAME  drop constraint KEY_NAME;

建外键

--单字段时：
create table 表名 (col1  char(8), cno   char(4)  REFERENCE course);
--多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段)
--连带删除选项 (on delete cascade
--当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除
REFERENCE 表名（） on delete cascade;

删除带约束的表

drop table 表名 cascade  constraints;

索引管理

<1>.creating function-based indexes 
sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);
<2>.create a B-tree index 
sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace 
sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer] 
sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0 
sql> maxextents 50);
<3>.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows
<4>.creating reverse key indexes 
sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k 
sql> next 200k pctincrease 0 maxextents 50) tablespace indx;
<5>.create bitmap index 
sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k 
sql> pctincrease 0 maxextents 50) tablespace indx;
<6>.change storage parameter of index 
sql> alter index xay_id storage (next 400k maxextents 100);
7.allocating index space 
sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');
<8>.alter index xay_id deallocate unused;
<9>、查看索引
     SQL>select index_name,index_type,table_name from user_indexes order by table_name;
<10>、查看索引被索引的字段
     SQL>select * from user_ind_columns where index_name=upper('&index_name');

创建序列

   select * from user_sequences;
    create  sequence SEQ_NAME  start with 1000 
         maxvalue  1000 increment by 1;
    alter sequence  SEQ_NAME minvalue 50 maxvalue 100;