ORACLE 管理 约束与字符集

约束的高级属性

约束和索引的关系:

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;

--删除数据库: