oracle中6中约束：not null constraint，unique Constraint，prinary key
constraint，foreign key constraint ,ref constraint,check
constraint.
按照定义约束的位置可以氛围inline约束和out-of-line约束，inline约束是指和column一起定义的约束叫inline约束，和表的定义一起的叫out－of－line约束
inline－constraint语法图
out－of－line constraint语法图
inline-ref-constraint语法图
out－of－line－ref－constraint语法图
reference－clause语法图
constraint状态图
1.not null constraint
限制指定的列不能为null，该约束只能以inline约束的形式来定义。在视图中不能指定not
null约束，自定义对象的属性也不能用not null constraint要使用check not null。
自定义对应的属性：
create type person_name
as object(first_name
varchar2(30),last_name varchar2(20));
create table students(name
person_name,age number) check(name.first_name is not
null and name.last_name is not
null)
设置表的属性为not null constraint：alter
table test add test_1
number constraint
1_constraint not null;alter table
test add
test_1 number not null
设置为可为null则是alter table test modify(test_1 null);
2.unique constraint
该约束限制表中的某列不能出现重复的值，但是可以存在多个null，因为null！=null；在指定一个unique
constraint时oracle会默认的创建一个索引，如果相应的列已经有索引存在则不创建，但是在删除该unique
约束时，如果索引是之前就存在的而不是由于创建了unique约束暗地里创建的则索引不会删除，否则是会删掉的。
在创建表时定义unique constraint；
inline-constraint create table test(test_1 number unique，test_2 varchar2(20));
out－of－line
constraint create table test(test_1
number,tet_2 varchar2(20),constraint con-test unique test_1);
composite unique key out of line
create table test (test_1 number,tet_2 varchar2(20),test_3
varchar2(20),constraint con-test unique
(test_1,test_3));组合唯一约束，是按照一个组合的值(test_1,test_3)来判断是否唯一的，而不是单独的比如上表中的一个test_1或者test_3来判断的
多个属性要设置为unique约束应该：
create table test (test_1 number,tet_2 varchar2(20),test_3
varchar2(20),constraint con-test unique
(test_1)，unique(test_3));
表创建好后修改unique约束：
alter table test modify constraint name unique(test_1);
alter table test add constraint name unique(test_1);
删除约束：alter table table_name drop consraint
constraint_name;
unique constraint的限制：
1) LOB, LONG, LONG RAW,
VARRAY, NESTED TABLE,
OBJECT, REF, TIMESTAMP
WITH TIME ZONE, or
user-defined 类型不能设置unique
constraint，但是组合unique约束里面可以有一列是TIMESTAMP
WITH LOCAL TIME
ZONE
2)组合唯一约束的列不能超过32列
3)定义了unique约束的列或者组合不能再定义为primary约束列或者是组合；比如teat表里面的test_1定义了primary约束，那么不能对test_1列不能再定义unique约束，但是可以定义test_1,test_2的组合unique约束，这样定义以后test_2其实没有起到什么作用；
4)唯一约束不能在子视图中定义，只能在第一级的父视图中定义；
在定义unique
constraint时，报ora－02299：无法验证constraint
name－找到重复关键字，该错误是以内在你要定义唯一约束的表中，你要定义的列或者是组合列存在重复的数据记录导致的。
3.primary key
constraint
主键约束，主键约束的列或者组合列中的所有列不能有null值，且不能重复；设置了primary key的列或组合会创建索引，如果相应的列已经有索引存在则不创建，但是在删除该主键约束时，如果索引是之前就存在的而不是由于创建了主键约束暗地里创建的则索引不会删除，否则是会删掉的。
主键的限制：
1)一个表或者一个视图只能有一个主键
2)LOB, LONG,
LONG RAW, VARRAY,
NESTED TABLE, OBJECT,
REF, TIMESTAMP WITH
TIME ZONE, or user-defined 类型不能设置primary
key constraint，但是组合的主键约束里面可以有一列是TIMESTAMP
WITH LOCAL TIME
ZONE
3)主键的大小不能超过约一个数据库块的大小。这样应该是考虑到索引存储的性能，要是一个索引就要一个块多，占用的存储空间也大，搜索索引的效率就大大降低，没啥优势了。自己的理解不知道对否，望大大们指教
4)组合主键最多不能超过32列
5)定义了unique约束的列或者组合不能再定义为primary约束列或者是组合；比如teat表里面的test_1定义了primary约束，那么不能对test_1列不能再定义unique约束，但是可以定义test_1,test_2的组合unique约束，这样定义以后test_2其实没有起到什么作用；
6)主键约束不能在子视图中定义，只能在第一级的父视图中定义；
4.foreign key
constraint
外键约束又叫参照完整性约束。拥有foreign
key的表或视图叫子表或子视图，拥有referenced
key(参照键)的表或视图叫做父表或父视图，父表或者父视图和子表或子视图可以在同一个表中。一个表或者视图中可以有多个外键。
外键的限制：
1) 外键不能是LOB, LONG,
LONG RAW,
VARRAY, NESTED
TABLE,
BFILE,
REF, TIMESTAMP
WITH TIME ZONE, or
user-defined 类型的列，在组合外键中可以有一列的类型是TIMESTAMP
WITH LOCAL TIME
ZONE.
2)参照列必须是主键或者是unique列
3)组合外键不能超过32列
4)在拥有外键联系的父表和子表必须在一个数据库中(不同的schema也是可以的)，在分布式的数据库中外键关系的表在不同的节点上，要设置这种外键需要创建触发器
5)如果子对象或者是父对象是视图，则该表或者视图的所有约束限制就要遵循view
约束的限制
6)在ctas创建的表中不能定义外键，必须创建好表之后使用alter
table命令来创建外键(如alter table test add constraint fk_t foreign key(cid)
reference class(cid));
foreign key约束可以指定on
delete选项(view约束中不能使用该选项)，该选项是是指定数据库在移除参照列时做什么处理，如果没有指定该选项，则不能删除父表中参照列的值(如果该值在子表中存在的话)。
on delete
cascade：指定该选项则在删除父表对应的值时会将子表中有相应值的列删除
on delete set
null：指定该选项时在删除父表对应值时会将子表中相应的值设置为null。
**************************************************************************************************
在设置外键是没有指定参照列，则默认是使用参照表中的主键
**************************************************************************************************
SQL> create table
class(cid number primary key,building varchar2(10) not
null,se
ats number not null,description
varchar2(30));
表已创建。
SQL> create table
course(couid number, teacher varchar2(20),time date,cid
constr
aint fk_c references class);－－inline_constraint
create table course(couid number,
teacher varchar2(20),time date,cid，constr
aint fk_c foreign key(cid) references class);---out_of_line_constraint
表已创建。
SQL> SELECT
CONSTRAINT_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS WHERE
TABLE_NAME=
'COURSE';
CONSTRAINT_NAME COLUMN_NAM
------------------------------ ----------
FK_C CID
**************************************************************************************************
在外键关系中的父表和子表可以是同一个表**************************************************************************************************
SQL> create table
testsamefk(tid number primary key,test_1 varchar2(20),count
nu
mber,test_2 constraint fk_s references
testsamefk(tid));
表已创建。
**************************************************************************************************
外键的参照列必须是主键或者是有唯一约束的列**************************************************************************************************
SQL> create table
testsamefk(tid number primary key,test_1 varchar2(20),count
nu
mber,test_2 constraint fk_s references testsamefk(test_1));
create table testsamefk(tid number primary key,test_1
varchar2(20),count number,
test_2 constraint fk_s references
testsamefk(test_1))
*
第 1 行出现错误:
ORA-02270: 此列列表的唯一关键字或主键不匹配
SQL> create table
testsamefk(tid number primary key,test_1 varchar2(20),count
nu
mber unique,test_2 constraint fk_s references
testsamefk(count));
表已创建。
**************************************************************************************************
父表这子表在不同的schema中，拥有子表的用户需要有父表的references的权限,reference权限不能通过角色来授予(该方式是无效的)必须是直接授予。dba用户可以发出grant
reference on schema。table to user，一位dba其有grant any object
privilege权限，sys用户要想使用其他模式的表也必须显示的被授予referece权限
**************************************************************************************************
SQL> grant references
on class to test5;
授权成功。
QL> conn test5/123456
已连接。
SQL> create table test22(id number,cid constraint
fk_t references test1.class(ci
d));
表已创建。
SQL> select
constraint_name,column_name from user_cons_columns where
table_name=
'TEST22';
CONSTRAINT_NAME COLUMN_NAM
------------------------------ ----------
FK_T CID
SQL> revoke references on class from test5;
revoke references on class from test5
*
第 1 行出现错误:
ORA-01981: 必须指定 CASCADE CONSTRAINTS 以执行此撤销
SQL> revoke references on class from test5 cascade
constraints;
撤销成功。
SQL> select constraint_name,column_name from
user_cons_columns where table_name=
'TEST22';
未选定行－－－－外键也被撤销了
5.check constraint
检查约束是为了限制某列的值满足某个特定的条件，比如某列的字符只能是字符和数字。
检查约束的限制：
1)视图中不能使用检查约束，但是可以使用with check option属性，with check
option等价于检查约束
2)检查约束可以指定到表中的任意列，但是不能指定到其他表中的列
3)检查约束里面不能有以下结构：子查询和标量子查询表达式；调用具有不确定的函数，比如(CURRENT_DATE, CURRENT_TIMESTAMP,
DBTIMEZONE, LOCALTIMESTAMP,
SESSIONTIMEZONE, SYSDATE,
SYSTIMESTAMP, UID, USER, and
USERENV)；调用用户自定义的函数；取消ref列的关联性，比如使用 DEREF
函数；嵌套表列或属性；伪劣如CURRVAL, NEXTVAL,
LEVEL, or ROWNUM；未完全指定的时间常数；
SQL> create table testcheck(id number
check(id>10) disable,name varchar2(20) che
ck(name=upper(name)));
表已创建。
SQL> desc testcheck;
名称 是否为空? 类型
-----------------------------------------
-------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
SQL> insert into testcheck
values(9,'XU');－－－因为在创建表的时候id的check约束是disable数以是没有生效，所以9可以插入
已创建 1 行。
SQL> insert into testcheck
values(9,'li');
insert into testcheck values(9,'li')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (TEST1.SYS_C0011505)
SQL> commit;
提交完成。
SQL> SELECT CONSTRAINT_NAME,COLUMN_NAME FROM
USER_CONS_COLUMNS WHERE TABLE_NAME=
'TESTCHECK';
CONSTRAINT_NAME COLUMN_NAM
------------------------------ ----------
SYS_C0011504 ID
SYS_C0011505 NAME
SQL> ALTER TABLE TESTCHECK MODIFY CONSTRAINT
SYS_C0011504 ENABLE;
ALTER TABLE TESTCHECK MODIFY CONSTRAINT SYS_C0011504 ENABLE
*
第 1 行出现错误:
ORA-02293: 无法验证 (TEST1.SYS_C0011504) - 违反检查约束条件 －－因为在testcheck表中已经有id＝9存在所以违反约束条件
SQL> TRUNCATE TABLE TESTCHECK;
表被截断。
SQL> ALTER TABLE TESTCHECK MODIFY CONSTRAINT
SYS_C0011504 ENABLE;
表已更改。
SQL> insert into testcheck
values(9,'XU');－－9<10不满足条件insert into
testcheck values(9,'XU')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (TEST1.SYS_C0011504)
SQL> insert into testcheck
values(11,'XU');
已创建 1 行。
6.ref constraint
ref constraint是描述列的类型和它所引用的对象之间的关系；
CREATE TYPE cust_address_typ_new AS OBJECT
(
street_address VARCHAR2(40)
,
postal_code VARCHAR2(10)
,
city VARCHAR2(30)
,
state_province VARCHAR2(10)
,
country_id CHAR(2)
);
/
CREATE TABLE address_table OF cust_address_typ_new;
CREATE TABLE customer_addresses (
add_id NUMBER,
address REF
cust_address_typ_new
SCOPE IS address_table);
－－－－－表示address的值只能在表address_table中，scope指定了address的值的范围SQL>
insert into address_table
values('11','311202','hanghzou','zhejiang','1');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into customer_addresses select 1,ref(c)
from address_table c where r
ownum<2;
已创建 1 行。
SQL> select * from customer_addresses;
ADD_ID
----------
ADDRESS
--------------------------------------------------------------------------------
1
00002202084F63FB233A98465497891C53997A508A29659D6EA553450D8BE595AFDB382917
SQL> select * from customer_addresses
c,address_table a where c.address=ref(a);
ADD_ID
ADDRESS STREET_ADD POSTAL_COD CITY STATE_PROV CO
---------- ---------- ---------- ---------- ----- ----------
--
1 0000220208
11 311202 hangh zhejiang 1
4F63FB233A zou
9846549789
1C53997A50
8A29659D6E
A553450D8B
E595AFDB38
2917
7.constraint state
在约束中指定状态来控制约束是在什么时候生效如何生效。
DEFERRABLE/NOT
DEFERRABLE:如果没有指定deferrable则默认是not
deferrable，该状态指示在后续的事务中，约束的校验是否延迟到事务的结尾使用set
constraint(s)语句；该状态不能在视图中使用
1)指定not deferrable则在后续的事务中，不能使用set constraint语句来延迟校验。在create
table或者是alter table指定not deferrable时，要确保此时的约束是有效的否则会创建失败。
2)指定deferrable则在后续的事务中可以使用set
constraint(s)条件语句来延迟到commit时来检查约束的有效性。如果没有指定set
constrain(S)语句则也是立即执行检查的。如果检查的约束条件不通过则会返回错误，同时事务回滚。在状态的作用主要在一个事务中需要修改多处，而且仅到多处都都该成功后检查才是有效的，在中间的一些状态下，约束检查都是无效的情况。
deferrable状态不能通过alter语句来修改，只能是删除对应的约束然后重新创建约束的时候指定。
SQL> create table testdefer(age number,name
varchar2(20) unique deferrable);
表已创建。
SQL> insert into testdefer values(1,'xu')
2 ;
已创建 1 行。
SQL> insert into testdefer values(1,'xu')
2 ;
insert into testdefer values(1,'xu')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST1.SYS_C0011693)
SQL> set constraint sys_c0011693 deferred;
约束条件已设置。
SQL> insert into testdefer values(1,'xu')
2 ;
已创建 1 行。
SQL> insert into testdefer values(1,'li')
2 ;
已创建 1 行。
SQL> commit;
commit
*
第 1 行出现错误:
ORA-02091: 事务处理已回退
ORA-00001: 违反唯一约束条件 (TEST1.SYS_C0011693)
INITIALLY IMMEDIATE/INITIALLY
DEFERRED:该initially设置能被set
constraint(s)替代，该状态只有在指定了deferrable时才可以使用，initially的作用就是在没有指定set
constraint的时候按照initially的设置来执行约束检查，如果指定了set constraint则优先按照set
constraint的设置来执行约束检查。没有指定该状态默认是inially IMMEDIATE.
SQL> create TABLE testiniti(age number,name
varchar2(20),constraint uk_t unique(
name) initially deferred deferrable);
表已创建。
SQL> insert into testiniti values( 1,'xu');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into testiniti values( 1,'xu');
已创建 1 行。
SQL> insert into testiniti values( 1,'xu');
已创建 1 行。
SQL> commit;
commit
*
第 1 行出现错误:
ORA-02091: 事务处理已回退
ORA-00001: 违反唯一约束条件 (TEST1.UK_T)
SQL> select * from
testiniti;
AGE NAME
---------- --------------------
1 xu
SQL> select constraint_name
from user_cons_columns where table_name='TESTINITI';
CONSTRAINT_NAME
------------------------------
UK_T
SQL> SET
CONSTRAINT UK_T IMMEDIATE;
约束条件已设置。
SQL> insert into testiniti
values( 1,'xu');
insert into testiniti values( 1,'xu')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST1.UK_T)
SQL> insert into testiniti
values( 1,'xu');
insert into testiniti values( 1,'xu')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST1.UK_T)
SQL> alter table testiniti
modify constraint uk_t initially immediate;－－－该约束是可以使用alter修改的
表已更改。
SQL> insert into testiniti
values( 1,'xu');
insert into testiniti values( 1,'xu')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (TEST1.UK_T)
SQL> set constraint uk_t
deferred;
约束条件已设置。
SQL> insert into testiniti
values( 1,'xu');
已创建 1 行。
SQL> commit;
commit
*
第 1 行出现错误:
ORA-02091: 事务处理已回退
ORA-00001: 违反唯一约束条件 (TEST1.UK_T)
VALIDATE/NOVALIDATE:该状态和enable和disable一起使用。
ENABLE:该状态使constraint生效，默认是enable。在唯一约束和主键约束中，如果没有unique
index存在则会创建unique index。在disable 这类约束时如果指定keep
index则相应的index不会被删除；否则在disable的时候会被删除，enable的时候会重新创建索引。要想避免在创主键约束或者唯一约束时使用disable约束时重现创建索引，可以使用非唯一索引来强制该约束。oracle在disable一个约束时不会删除非唯一索引。
SQL> create table testdisable(age number,name
varchar2(20) ,constraint uk_td uni
que(name) disable);
表已创建。
SQL> select index_name,status from user_indexes
where table_name='TESTDISABLE';
未选定行
SQL> ALTER TABLE TESTDISABLE MODIFY CONSTRAINT
UK_TD ENABLE;
表已更改。
SQL> select index_name,status from user_indexes
where table_name='TESTDISABLE';
INDEX_NAME STATUS
------------------------------ --------
UK_TD VALID
SQL> ALTER TABLE TESTDISABLE MODIFY CONSTRAINT
UK_TD DISABLE;
表已更改。
SQL> select index_name,status from user_indexes
where table_name='TESTDISABLE';
未选定行
SQL> ALTER TABLE TESTDISABLE MODIFY CONSTRAINT
UK_TD ENABLE;
表已更改。
SQL> select index_name,status from user_indexes
where table_name='TESTDISABLE';
INDEX_NAME STATUS
------------------------------ --------
UK_TD VALID
SQL> ALTER TABLE TESTDISABLE MODIFY CONSTRAINT
UK_TD DISABLE KEEP INDEX;
表已更改。
SQL> select index_name,status from user_indexes
where table_name='TESTDISABLE';
INDEX_NAME STATUS
------------------------------ --------
UK_TD VALID
1)enable validate：表示地已经存在和将要插入表中的数据都执行检验
2)enable
novalidate：表示对已经存在的数据不进行检验，但是对将要插入的数据互执行校验。(该命令对unique约束和主键约束无效)
SQL> create table testvolidate(age number,name
varchar2(20) constraint nk_t not
null disable);
表已创建。
SQL> select
constraint_name,constraint_type,status from user_constraints where
t
able_name='TESTVOLIDATE';
CONSTRAINT_NAME C STATUS
------------------------------ - --------
NK_T C DISABLED
SQL> insert into testvolidate(age) values(1);
已创建 1 行。
SQL> insert into testvolidate(age) values(3);
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter table testvolidate modify constraint
nk_t enable novalidate;
表已更改。
SQL> insert into testvolidate(id)
values(4);
insert into testvolidate(id) values(4)
*
第 1 行出现错误:
ORA-00904: "ID": 标识符无效
SQL> insert into testvolidate(age) values(4);
insert into testvolidate(age) values(4)
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("TEST1"."TESTVOLIDATE"."NAME")
SQL> alter table testvolidate modify constraint nk_t
enable validate;
alter table testvolidate modify constraint nk_t enable
validate
*
第 1 行出现错误:
ORA-02293: 无法验证 (TEST1.NK_T) - 违反检查约束条件
DISABLE:该状态使相应的constraint不生效。默认是disable novalidate
1)disable validate：在该状态下会删除约束创建的索引，但是保持constraint为valid。
2)disable novalidate：在该状态下，对应的约束是真正的不起作用的。如果某个primary
key被另外的表作为foriege key，此时forienge key为 disable
novalidate，你也不能删除primary key所在的表。
RELY/NORELY:该状态只能在已经创建了约束之后才可以使用，也就是只能在alter table
...modify constraint...中使用。not
null约束中不能出现rely。rely只能在novalidate模式下使用。
USING INDEX:该状态只能在unique和primary约束的时候使用；该状态是使unique和primary使用其设置的索引。
1)在using index后跟schema.index，则数据库会尝试执行指定的索引，如果找不到该索引，或者使用该索引来执行约束不成功则会报错
2)在using index中指定create index的子句，则数据库会尝试创建索引，并使用该索引来执行约束，不成功则报错
3)如果没有指定任何值则数据库会自动创建索引，索引名与约束名相同，如果是分区表，在可以创建locally或者是globally分区索引。
using index状态的限制：
1)视图中不能使用该状态
2)该状态不能在not null，foreign key 和check约束中使用
3)不能在组织索引表中指定该状态
4)using index中指定的索引或者创建的索引中指定并行度
5)using index中指定的索引或创建的索引不能指定 indextype is
create table test( id number, name varchar2(20),category varchar2(15),constraint uk_id unique(id) using index(create unique index uk_id_index on test(id)),constraint uk_n unique(name,id) using index uk_id_index);
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
