Oracle事例

所需积分/C币:48 2007-08-26 10:35:53 21KB
36
收藏 收藏
举报

1.增加主键 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; 2.增加外键 alter table TABLE_NAME add constraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME; 3.使主键或外键失效、生效 alter table TABLE_NAME disable(enable) constraint KEY_NAME; 4、查看各种约束 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; 5、删除主键或外键 alter table TABLE_NAME drop constraint KEY_NAME; 6、建外键 单字段时:create table 表名 (col1 char(8), cno char(4) REFERENCE course); 多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段) 连带删除选项 (on delete cascade 当指定时,如果父表中的记录被删除,则依赖于父表的记录也被删除 REFERENCE 表名() on delete cascade; 7、删除带约束的表 Drop table 表名 cascade constraints; 8:索引管理 .creating function-based indexes sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped); .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); .pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows .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; .create bitmap index sql> create bitmap index

...展开详情
立即下载 低至0.43元/次 身份认证VIP会员低至7折
一个资源只可评论一次,评论内容不能少于5个字
您会向同学/朋友/同事推荐我们的CSDN下载吗?
谢谢参与!您的真实评价是我们改进的动力~
  • 分享小兵

关注 私信
上传资源赚钱or赚积分
最新推荐
Oracle事例 48积分/C币 立即下载
1/0