限制的進階屬性
限制和索引的關系:
create table t (a int constraint c unique);
insert into t values(1);
alter table t disable constraint c;
alter table t enable novalidate constraint c;
排它(查找違反限制條件的行):
alter table emp1 add constraint ck_sal_1 check(sal>1000);
@?\rdbms\admin\utlexcpt
alter table emp1 add constraint ck_sal_1 check(sal>1000) EXCEPTIONS into EXCEPTIONS;
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
限制的四種狀态
ENABLED ENABLED DISABLED DISABLED --對新來資料
VALIDATE NOVALIDATE VALIDATE NOVALIDATE --對現有資料
create table e as select * from emp;
create table d as select * from dept;
ENABLED + VALIDATE:
alter table e add constraint uk_e_ename unique (ename);
select STATUS,VALIDATED,DEFERRABLE,DEFERRED
from user_constraints
where constraint_name='UK_E_ENAME';
STATUS VALIDATED DEFERRABLE DEFERRED
---------- -------------------- -------------------- ---------------------------
ENABLED VALIDATED NOT DEFERRABLE IMMEDIATE
alter table e modify constraint uk_e_ename disable;
ENABLED + NOVALIDATE:
alter table e add constraint ck_e_sal check(sal>1000 and sal is not null) novalidate;
DISABLED + VALIDATE:不影響子表資料的前提下重建父表!
alter table e add constraint ck_e_sal check(sal>700 and sal is not null) disable validate;
where constraint_name='CK_E_SAL';
限制的兩種工作模式:
立即型:DML時限制就工作(預設風格)!
延遲型:commit時限制才工作!
alter table e add constraint ck_e_sal check(sal>700 and sal is not null) initially DEFERRED;
主鍵&唯一鍵限制隐式建立索引的類型:
限制如果是DEFERRABLE的那麼建立唯一鍵索引
限制如果是NOT DEFERRABLE的那麼建立非唯一鍵索引
(隻針對主鍵和唯一鍵限制)
yes deferrable yes
有索引嗎?---------------- 限制可延遲嗎?-----索引是唯一鍵嗎?----限制不能啟用
| | |
|no |not deferrable | no
| deferrable | |
限制可延遲嗎?-----建立非唯一鍵索引 使用現有索引 使用現有索引
|
|not deferrable
建立唯一鍵索引
select INDEX_NAME,UNIQUENESS from user_indexes where table_name='E';
INDEX_NAME UNIQUENESS
------------------------------ ---------------------------
UK_E_ENAME UNIQUE
alter table e DROP constraint uk_e_ename;
alter table e add constraint uk_e_ename unique (ename) DEFERRABLE;
UK_E_ENAME NONUNIQUE
練習
觀察下面操作:怎樣重新啟動限制 c ?
修改資料庫字元集
[/root]#echo $LANG
zh_CN.UTF-8
[/root]#su - oracle
[oracle@uplooking ~]$ echo $LANG
[oracle@uplooking ~]$ export NLS_LANG='SIMPLIFIED CHINESE'_CHINA.AL32UTF8
[oracle@uplooking ~]$ echo $NLS_LANG
SIMPLIFIED CHINESE_CHINA.AL32UTF8
[oracle@uplooking ~]$ sqlplus / as sysdba
--inittestutf8.ora--
compatible=10.2.0.1.0
db_name=testutf8
shared_pool_size=200m
control_files='/u01/app/oracle/oradata/testutf8/control01.ctl'
undo_management=auto
undo_tablespace=undotbs1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create database testutf8
maxinstances 8
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxloghistory 1
datafile '/u01/app/oracle/oradata/testutf8/system01.dbf' size 300m autoextend on next 10m extent management local
sysaux datafile '/u01/app/oracle/oradata/testutf8/sysaux01.dbf' size 120m autoextend on next 10m
default temporary tablespace temp tempfile '/u01/app/oracle/oradata/testutf8/temp01.dbf' size 50m
undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/testutf8/undotbs01.dbf' size 40m
character set utf8
national character set utf8
logfile
group 1 '/u01/app/oracle/oradata/testutf8/redo01.log' size 50m,
group 2 '/u01/app/oracle/oradata/testutf8/redo02.log' size 50m;
select * from PROPS$;
字元集轉換:
shutdown immediate
startup mount
--alter session set sql_trace=true;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set internal_convert al32utf8;
alter database national character set internal_convert al16utf16;
--删除資料庫: