．约束
作用
约束用于确保数据库数据的完整性在oracle数据库中可以使用约束触发器和应用代码(过程函数)种方法实现数据完整性这种方法中因为约束易于维护并且具有最好的性能所以实现数据完整性首选约束
分类
约束的种类有not nulluniqueprimary keyforeign keycheck
Not null确保字段值不能为空
Unique:确保字段值唯一性
Primary key最常用的约束（主键约束）主键约束的列值不仅不能重复也不能为NULL注意一张表最多只能有一个主键约束当定义主键约束后oracle自动建立一个以主键为关键字段的索引
Foreign key:定义了主从表之间的关系foreign要定义在从表上但主表必须具有主键约束或唯一约束当定义froeign key后外部键列的数据必须在主表的主键列中存在或者为NULL
Check::用于强制表行数据必须满足的条件如工资表工人工资必须在之间
约束状态
enable validate:是默认新旧数据同时满足约束规则
enable novalidate:旧数据可以不满足检新插入的数据要满足约束
disable validate:不允许在表上执行任何DML操作主要用在分区表对于主键和唯一约事会删除相应的唯一索引但约束状态任可用
disable novalidate数据可不满足约束规则对于主键和唯一约事会删除相应的唯一索引
约束常用语句
create table t(i numberv mubmer not null)
create table t(i numberv mubmer unique)
create table t(i number constraint pk_i primary keyv number)
create table t(c numberd numberconstraint fk_d foreign key(c)references t(v));
alter table t add constraint pk_i primary key (i)
alter table t modify i not null;
alter table t add constraint t_i unique(i)[(create index ind_name on t(i))];
alter table t add constraint t_i check(i in ());
alter table t disable novalidate constraint i
alter table t enable novalidate constraint check_i
alter table t drop constraint i;
alter table t drop primary key i;
#常用的数据字典
dba_constraints
dba_cons_columns
user_cons_columns
user_constraints
简单应用
检验当为一个表建立主键索时后这个字段是否满足约束非空唯一性而且自动建立一个索引并查看当把约束状态关闭再次插入相同的记录是否还能把把约束设为enable ividate状态
SQL> create table t(i number constraint pk_i primary keyv number);
SQL> insert into t values();
SQL> insert into t values();
SQL> commit;
SQL> select * from t;
I V
现在表中有两条记录然后给它插主键为空或相同的值
SQL> insert into t values();
ERROR at line :
ORA: cannot insert NULL into (YTI)
SQL> insert into t values();
ERROR at line :
ORA: unique constraint (YPK_I) violated
可以看到全部报错此时主键不能为空或重复
查看是否建立索引
SQL> select index_name from user_indexes;
INDEX_NAME
PK_I
把约束关闭再次做同样的操用
SQL> alter table t disable novalidate constraint pk_i;
Table altered
SQL> insert into t values();
row created
SQL> insert into t values();
row created
SQL> commit;
Commit complete
SQL> select * from t;
I V
SQL> select index_name from user_indexes;
no rows selected
可见当把约束关闭后就可以何意给表插数据了而具索引也自动删除了
现在激活约束
SQL> alter table t enable validate constraint pk_i;
alter table t enable validate constraint pk_i
ERROR at line :
ORA: cannot validate (SYSPK_I) primary key violated
因为表中主键有相同的值所以不能恢复到enable validate状态了
再次测试回复到enable novalidate
SQL> alter table t enable novalidate constraint pk_i;
alter table t enable validate constraint pk_i
ERROR at line :
ORA: cannot validate (SYSPK_I) primary key violated
也失败了
因为表中主键有了空值和相同的值所以恢复不到enable validate状态但enable novalidate不检查旧数据所以应该还能恢复到enable novalidate
要想恢复到enable novalidate必须建立主键索引(关闭约束时自动删除的那个索引)如下:
SQL> create index pk_i on t(i);
Index created
然后恢复到enable disvalidate以后再插数据不能为空主键也不能重复了
SQL> alter table t enable novalidate constraint pk_i;
Table altered
SQL> insert into t values();
insert into t values()
ERROR at line :
ORA: unique constraint (SYSPK_I) violated
修正约束数据
当给一个表作主键约束时因为已存数据不满足约束规则会提示错误信息些时必须对数据进行修正
要修正数据先找出不满足约束的数据
如下表有不满足约束的数据
SQL> select * from t;
I V
如果一个表数据量多可通过如下方法查找
SQL> alter table t drop constraint pk_i;
Table altered
SQL>conn y /
SQL> @$ORACLE_HOME/rdbms/admin/utlexcptsql
Table created
SQL> alter table t add constraint pk_i primary key (i) exceptions into exceptions;
select * from t where rowid in (select row_id from exceptions)
I V
找到了重复的记录
修正
SQL>update t set i= where v=;
SQL> select * from t;
I V
再建主键约束
alter table t add constraint pk_i primary key (i)
Table altered
成功了!!!
二:分区表管理
作用:将在张大表的数据分布到多个表分区段不同分区彼此独立从而提高了表的可用性和性能
种类:范围分区散列分区(使用HASH算法最常使用)列表分区范围/散列组合分区范围/列表组合分区
范围分区表
创建范围分区表
create table t(v numberb number)
partition by range(v) (
partition p values less than () tablespace test
partition p values less than () tablespace test);
增加与删除分区
#增加分区
alter table t add partition p values less than () tablespace test;
alter table t drop partition p
一个时间分区的例子
alter session set nls_data_lanage=AMERICAN;
alter session set nls_data_format=DDMONYYYY
create table t(v_date dateb number)
partition by range(v_date)(
partition p values less than (APR) tablespace test
partition p values less than (JUN) tablespace test);
散列分区表(最常用)
创建
create table t(
v numberb number)
partition by hash(v)
(partition p tablespace test
partition p tablespace test);
增加分区
alter table t add partition p tablespace test;
删除分区
alter table t drop coalesce partition;
列表分区
建列表分区
create table t(
v varchar()
b number
)partition by list(v)
(partition p values(ab) tablespace test
partition p values(cd) tablespace test);
#插入数据
SQL> insert into t values(a);
SQL> insert into t values(d);
#注意插入数据时第一个字段只能为abcd
SQL> insert into t values(f);
ERROR at line :
ORA: inserted partition key does not map to any partition
#查询
select * from t;
select * from t partition(p);
select * from t partition(p);
select * from t where v=XXX
增加分区
alter table t add partition p values() tablespace test;
删除分区
alter table t drop partition p
范围/散列组合分区
建立散列组合分区
create table t(
v numberb number)
partition by range(v)
subpartition by hash(b) subpartitions
store in (testtest)(
partition p values less than ()
partition p values less than ());
查询
select * from t;
select * from t partition(p);
select * from t where
增加主分区和子分区
alter table t add partition p values less than () tablespace test;
alter table t modify partition p add subpartition;
删除分区
alter table t coalesce partition;
alter table t modify partition p coalesce subpartition;
范围/列表组合分区
创建
create table t(
v numberb number)
partition by range(v)
subpartition by list(b)
(partition p values less than () tablespace test(
subpartition p_ values()
subpartition p_ values()
)
partition p values less than () tablespace test(
subpartition p_ values()
subpartition p_ values()
));
查询
select * from t
select * from t partition(p)
select * from t subpartition(p_)
select * from t where
select segment_namepartition_nametablespace_name
from user_segments where segment_name=T;
增加分区和子分区
alter table t add partition p values less than () tablespace test(
subpartition p_ values()
subpartition p_ values());
alter table t modify partition r
add subpartition r_ tablespace test values();
删除分区
alter table t modify partition p coalesce subpartition;
其它设置
交换分区数据
alter table t exchange partition p with table tt;
载断分区
alter table t truncate partition p;
修改分区名
alter table t rename partition p_ to p;
合并分区
alter table t merge partitions pp into partition p
重组分区
alter table t move partition p tablespace test
为列表分区和子分区加值
alter table t modify partition p add values();
alter table t modify subpartition p_ add values();
从列表分区和子分区中删除值
alter table t modify partition p drop values()
alter table t modify subpartition p_ drop values()
分区表常用的数据字典
分区表信息: dba_part_tables
显示分区: dba_tab_partitions
显示子分区: dba_tab_subpartitions
显示分区列: dba_part_key_columns
显示子分区列:dba_subpart_dey_columns
显示分区索引:dba_part_indexes
显示索引分区:dba_ind_partitions
