當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

constraints

發(fā)布時(shí)間：2024/1/18 编程问答 31 豆豆

生活随笔收集整理的這篇文章主要介紹了 constraints 小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

1 、約束的類型

Table 5-1 Types of Constraints

Constraint TypeDescriptionSee Also

NOT NULL	Allows or disallows inserts or updates of rows containing a null in a specified column.	"NOT NULL Integrity Constraints"
Unique key	Prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.	"Unique Constraints"
Primary key	Combines a NOT NULL constraint and a unique constraint. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.	"Primary Key Constraints"
Foreign key	Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called thereferenced key.	"Foreign Key Constraints"
Check	Requires a database value to obey a specified condition.	"Check Constraints"
REF	Dictates types of data manipulation allowed on values in a REF column and how these actions affect dependent values. In an object-relational database, a built-in data type called aREF encapsulates a reference to a row object of a specified object type. Referential integrity constraints onREF columns ensure that there is a row object for theREF.	Oracle Database Object-Relational Developer's Guide to learn aboutREF constraints

2、NOT NULL:? 非空約束

?非空約束要求表的一個(gè)列沒有空值，默認(rèn)情況下，表的所有行是可以為空的，null的數(shù)據(jù)類型為字符型，至于說是varchar還是char未知

2.1、非空約束的創(chuàng)建

建表時(shí)創(chuàng)建非空約束??????

???? SQL> create table rice (id number,????????????????????????

??????????????? name varchar2(2) not null);???

?????? 表已創(chuàng)建。

建表后創(chuàng)建非空約束

???? SQL> create table rice1 (id number,???????????????????????????????????????

???????????????????????????????? ? name varchar2(2) );

? 表已創(chuàng)建。

SQL> alter table rice1 modify name varchar2(2) not null;

表已更改。

? SQL> desc rice1

?名稱???????????????????????????????????????????????????????????????????????????? 是否為空? 類型

?------------------------------------------------------------------------------ -------- ----

?ID??????????????????????????????????????????????????????????????????????????????????? NUMBER

?NAME????????????????????????????????????????????????????????????????????????? NOT NULL VARCHAR2(2)

刪除非空約束

? SQL> alter table rice1 modify name varchar2(2) null;
表已更改。

SQL> desc rice1
?名稱????????????????????????????????????????????????????????????????????????????????????????????????????? 是否為空? 類型
?----------------------------------------------------------------------------------------------------------------- -------- -------
?ID???????????????????????????????????????????????????????????????????????????????????????????????????????????????? NUMBER
?NAME?????????????????????????????????????????????????????????????????????????????????????????????????????????????? VARCHAR2(2)

有非空約束的表，插入空值
SQL> insert into rice values(1,'');
insert into rice values(1,'')
????????????????????????? *
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01400: 無法將 NULL 插入 ("SCOTT"."RICE"."NAME")

2.3 什么時(shí)候創(chuàng)建非空約束
?默認(rèn)情況下，一個(gè)列上是可以包含空值的，為了保證那一列上不在具有空值，可以創(chuàng)建非空約束。
? （1）列必須是非空的情況
?? （2）

You want to allow index scans of the table, or allow an operation that requires indexing all rows.

Oracle Database indexes do not store keys whose values are all NULL. Therefore, for the preceding kinds of operations, at least one indexed column must have aNOTNULL constraint.

????? 我對這句話我總是很不理解，你想通過索引掃描整個(gè)表，必須添加非空約束

舉例：

?????????? SQL> create table rice(id number not null , name varchar2(5));

??????? 表已創(chuàng)建。

??????? SQL> create table rice1(id number? , name varchar2(5));

??????? 表已創(chuàng)建

??????? SQL> desc rice
????? ?名稱???????????????????????????????????????????????????????????????????????????????????????是否為空? 類型

???? ? --------------------------------------------------------------------------------------------------------- -------?

???? ID???????????????????????????????????????????????????????????????????????????????????????? NOT NULL NUMBE???

???? ?NAME?????????????????????????????????????????????????????????????????????????????????????VARCHAR2(5)

??????????SQL >desc rice1????

?????????? 名稱????????????????????????????????????????????????????????????????????????????????????????????????????? 是否為空? 類型?????????

??????? ?----------------------------------------------------------------------------------------------------------------- -------- --------

????????????????? ID?????????????????????????????????????????????????????????????????????????????????????????????????????? NUMBER??????????????????????????

????????????????? NAME????????????????????????????????????????????????????????????????????????????????????????????? VARCHAR2(5)?

SQL>? begin

? 2?? for i in 1..1000 loop

? 3?? insert into rice values(i,'ai');?

4?? commit;

? 5?? insert into rice1 values(i,'bi');

? 6?? commit;

? 7?? end loop;?

?8?? end;

? 9?? /

PL/SQL 過程已成功完成。

SQL> create index ind_id on rice(id);

索引已創(chuàng)建。

SQL> create index ind_id1 on rice1(id);

索引已創(chuàng)建。

SQL>? select /*+ index(rice ind_id)*/count(*) from rice;

執(zhí)行計(jì)劃

----------------------------------------------------------

Plan hash value: 3112229557

-------------------------------------------------------------------

-------------------------------------------------------------------

|?? 0 | SELECT STATEMENT |??????? |???? 1 |???? 8?? (0)| 00:00:01 ||?? 1 |?

SORT AGGREGATE? |??????? |???? 1 |??????????? |????????? ||?? 2 |??

?INDEX FULL SCAN| IND_ID |? 1000 |???? 8?? (0)| 00:00:01 |

-------------------------------------------------------------------

SQL>? select /*+ index(rice ind_id1)*/count(*) from rice1;

執(zhí)行計(jì)劃

----------------------------------------------------------

Plan hash value: 120591672

--------------------------------------------------------------------

--------------------------------------------------------------------

|?? 0 | SELECT STATEMENT?? |?????? |???? 1 |???? 3?? (0)| 00:00:01 ||?? 1 |?

?SORT AGGREGATE??? |?????? |???? 1 |??????????? |????????? ||?? 2 |??

?TABLE ACCESS FULL| RICE1 |? 1000 |???? 3?? (0)| 00:00:01 |

--------------------------------------------------------------------

結(jié)論：表rice上有非空約束，要求走索引，走了索引，但是rice2上沒有非空約束，要求走索引沒有走索引

3、Unique key: 唯一約束

（1）唯一約束要求一列或者組合列上是唯一的，沒有重復(fù)的，唯一約束適合在任何列或組合列上建，當(dāng)然這些列或組合列值不能重復(fù)。

（2）唯一約束和主鍵約束是不同的，因?yàn)榭罩涤肋h(yuǎn)滿足唯一約束，而空值卻不滿足于主鍵約束

唯一約束的限制：

None of the columns in the unique key can be of LOB, LONG, LONG RAW, VARRAY, NESTED TABLE,OBJECT,REF,TIMESTAMPWITHTIMEZONE, or user-defined type. However, the unique key can contain a column ofTIMESTAMPWITHLOCALTIMEZONE.
A composite unique key cannot have more than 32 columns.
You cannot designate the same column or combination of columns as both a primary key and a unique key.
You cannot specify a unique key when creating a subview in an inheritance hierarchy. The unique key can be specified only for the top-level (root) view.

3.1建立唯一約束

建表時(shí)建立唯一約束

????? SQL> create table rice(email varchar2(7)

??????????????????????? constraint e_notnull not null ,

???????????????????????? constraint e_uk unique(email));

???? 表已創(chuàng)建。

????? SQL> insert into rice values('a');
????? 已創(chuàng)建 1 行。

????? SQL> insert into rice values('a');
??????? insert into rice values('a')
???????? *
??????? 第 1 行出現(xiàn)錯(cuò)誤:
?????? ORA-00001: 違反唯一約束條件 (SCOTT.E_UK)

建表后建立唯一約束

???????????? SQL> create table rice1(id number,name varchar(2));
??????????? 表已創(chuàng)建。

?????????? SQL> alter table rice1 add constraint c_idd unique(id);
????????? 表已更改。

刪除唯一約束??????
???????????? SQL> alter table rice1 drop constraints c_idd;
??????????? 表已更改。

? 3.2 唯一約束可以插入多個(gè)空值 ? ??????

???????? SQL> drop table rice;??????????

??? 表已刪除。

SQL> create table rice(id number constraint c_id not null,

?????????????????????? name char(5) ,

??????????????????????? constraint c_name unique(name));

表已創(chuàng)建。

????? SQL> insert into rice values(1,'a');

已創(chuàng)建 1 行。

SQL> insert into rice values(1,'');

已創(chuàng)建 1 行。

SQL> insert into rice values(1,'');

已創(chuàng)建 1 行。

SQL> insert into rice values(2,'');

已創(chuàng)建 1 行。

SQL> select *From rice;?????

?? ID NAME

---------- -----????

???? 1 a??????

?? 1??????

?? 1?????

??? 2

3.3、查看約束：

SQL>? select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,DEFERRABLE,DEFERRED From dba_constraints WHERE table_name='RICE';

OWNER???????????????????? CONSTRAINT_NAME??????????????? C TABLE_NAME???????????????????? DEFERRABLE???? DEFERRED
---------------------------------------------- ------------------------------ - ------------------------------ -------------- ---------
SCOTT???????????????????????????????????? C_ID?????????????????????????? C RICE?????????????????? NOT DEFERRABLE IMMEDIATE
SCOTT????????????????????????????????? C_NAME?????????????????????? U RICE?????????????????? NOT DEFERRABLE IMMEDIATE

3.4、對比唯一約束和主鍵約束

????????? SQL> truncate table rice;

SQL> alter table rice add constraints pk_id1 primary key(id1)
? 2? ;

SQL>? select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,DEFERRABLE,DEFERRED From dba_constraints WHERE table_name='RICE';

OWNER??????????????????????????????? CONSTRAINT_NAME??????????????? C TABLE_NAME???????????????????? DEFERRABLE???? DEFERRED
--------------------------------- ------------------------------ - ------------------------------ -------------- ---------
SCOTT?????????????????????????????????? C_ID?????????????????????????? C RICE?????????????????? NOT DEFERRABLE IMMEDIATE
SCOTT????????????????????????????????? PK_ID1???????????????????????? P RICE?????????????????? NOT DEFERRABLE IMMEDIATE
SCOTT???????????????????????????????? C_NAME???????????????????????? U RICE?????????????????? NOT DEFERRABLE IMMEDIATE

主鍵約束顯示為p 唯一約束顯示為p

?? id1 列建主鍵約束

?? id列是非空約束

?? name 是唯一約束

SQL> insert into rice values(1,'a',1);

已創(chuàng)建 1 行。

SQL> select *From rice;

??????? ID NAME???????? ID1
---------- ----- ----------
???????? 1 a????????????? 1

主鍵約束不能為空

SQL> insert into rice values(1,null,null);
insert into rice values(1,null,null)
?????????????????????????????? *
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01400: 無法將 NULL 插入 ("SCOTT"."RICE"."ID1")

主鍵是唯一的，不能重復(fù)

SQL> insert into rice values(1,null,1);
insert into rice values(1,null,1)
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-00001: 違反唯一約束條件 (SCOTT.PK_ID1)

唯一約束列可以插入空值
???????????????? SQL> insert into rice values(1,null,2);

?????????????? 已創(chuàng)建 1 行。

唯一約束不能重復(fù)（空值比較特殊）

SQL> insert into rice values(1,'a',2);
insert into rice values(1,'a',2)
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-00001: 違反唯一約束條件 (SCOTT.C_NAME)

3.5 唯一索引和唯一約束的區(qū)別

（1）創(chuàng)建方法不一樣

?SQL> create table rice(id number,id1 number);
表已創(chuàng)建。

SQL> create unique index u_id on rice(id);
索引已創(chuàng)建。

SQL> alter table rice add constraint c_id1 unique(id1);
表已更改。

（2）數(shù)據(jù)庫在創(chuàng)建唯一約束時(shí)強(qiáng)制創(chuàng)建或者重復(fù)使用列上的索引，如果之前的列上沒有索引，那么強(qiáng)制創(chuàng)建的索引是唯一索引，如果列上已存在索引，就重用存在的索引，不管此索引是否唯一。

建唯一約束自動(dòng)創(chuàng)建唯一索引

QL> drop table rice;

表已刪除。

QL> create table rice(id number,id1 number);

表已創(chuàng)建。

QL> alter table rice add constraints c_unique? unique (id);

表已更改。

SQL>? select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,DEFERRABLE,DEFERRED From dba_constraints WHERE table_name='RICE';

OWNER????????????????????????????????? CONSTRAINT_NAME??????????????? C TABLE_NAME???????????????????? DEFERRABLE???? DEFERRED
-------------------------------------- ------------------------------ - ------------------------------ -------------- --------
SCOTT??????????????????????????????????? C_UNIQUE?????????????????????? U RICE?????????????????? NOT DEFERRABLE IMMEDIATE
SQL>? select INDEX_NAME,INDEX_TYPE,UNIQUENESS,STATUS From user_indexes where table_name='RICE';

INDEX_NAME???????????????????? INDEX_TYPE????????????????? UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
C_UNIQUE?????????????????????? NORMAL????????????????????? UNIQUE??? VALID

在列id上建立唯一索引，在列id1上建立普通索引，然后在兩個(gè)列上個(gè)建立唯一約束，查看

SQL> alter table rice drop constraint c_unique;
已更改。

SQL> create unique index id_uniqe on rice(id);
索引已創(chuàng)建。

SQL> create? index id_nouniqe on rice(id1);
索引已創(chuàng)建。

SQL> alter table rice add constraint c_id unique(id);
表已更改。

SQL> alter table rice add constraint c_id1 unique(id1);
表已更改。

SQL>? select INDEX_NAME,INDEX_TYPE,UNIQUENESS,STATUS From user_indexes where table_name='RICE';
INDEX_NAME???????????????????? INDEX_TYPE????????????????? UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
ID_NOUNIQE???????????????????? NORMAL????????????????????? NONUNIQUE VALID
ID_UNIQE?????????????????????? NORMAL????????????????????? UNIQUE??? VALID

SQL>? select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,DEFERRABLE,DEFERRED From dba_constraints WHERE table_name='RICE';

OWNER CONSTRAINT_NAME??????????????? C TABLE_NAME???????????????????? DEFERRABLE???? DEFERRED
----- ------------------------------ - ------------------------------ -------------- ---------
SCOTT C_ID1????????????????????????? U RICE?????????????????????????? NOT DEFERRABLE IMMEDIATE
SCOTT C_ID?????????????????????????? U RICE?????????????????????????? NOT DEFERRABLE IMMEDIATE

創(chuàng)建唯一約束重用列上以前存在的索引，在刪約束的時(shí)候，不會(huì)自動(dòng)刪除存在的索引，如果是創(chuàng)建索引的時(shí)候，自動(dòng)創(chuàng)建的唯一索引，在刪除唯一約束的時(shí)候，自動(dòng)刪除唯一索引。

SQL> alter table rice drop constraints c_id;
表已更改。

SQL> alter table rice drop constraints c_id1;
表已更改。

SQL>? select INDEX_NAME,INDEX_TYPE,UNIQUENESS,STATUS From user_indexes where table_name='RICE';

INDEX_NAME???????????????????? INDEX_TYPE????????????????? UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
ID_NOUNIQE???????????????????? NORMAL????????????????????? NONUNIQUE VALID
ID_UNIQE?????????????????????? NORMAL????????????????????? UNIQUE??? VALID

結(jié)論：1、唯一約束是可以插入多個(gè)空值的

??????????? 2、唯一約束和唯一索引是不一樣的

4、Primary key:主鍵約束

（1）主鍵唯一確定一行，一個(gè)表上只能有一個(gè)主鍵，主鍵可以確定主鍵列上是沒有重復(fù)值，那一列或組合列不是空值。

（2）創(chuàng)建主鍵約束會(huì)隱式創(chuàng)建一個(gè)唯一索引和非空約束

?（3）如果在創(chuàng)建主鍵約束前，列上就已經(jīng)存在索引，那么會(huì)重復(fù)使用存在的索引，如果不存在索引，那么會(huì)在那一列上建立一個(gè)唯一索引和約束同名。

?????? 在刪除主鍵約束時(shí)，如果是在創(chuàng)建主鍵是自動(dòng)創(chuàng)建的唯一索引，刪除約束時(shí)同時(shí)刪除索引，如果創(chuàng)建主鍵約束時(shí)，重復(fù)使用已存在索引，刪除約束時(shí)，索引仍然存在。

4.1創(chuàng)建主鍵

建表時(shí)創(chuàng)建主鍵

SQL> create table rice (id number primary key ,name varchar2(5));
表已創(chuàng)建。
SQL> insert into rice values(1,'a');
已創(chuàng)建 1 行。

SQL> insert into rice values(1,'a');
insert into rice values(1,'a')
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-00001: 違反唯一約束條件 (SCOTT.SYS_C0013515)

SQL> insert into rice values('','a');
insert into rice values('','a')
?????????????????????? *
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01400: 無法將 NULL 插入 ("SCOTT"."RICE"."ID")

刪除主鍵

SQL> alter table rice drop constraints SYS_C0013515;
表已更改。

建表后創(chuàng)建主鍵

方法一：

?????? SQL> alter table rice add constraints pk_id? primary key(id);
?????? 表已更改。

也可以通過

方法二：

????? SQL> create table rice1(id number,name varchar(10));
?????? 表已創(chuàng)建。

?????? SQL> create unique index pk_id1 on rice1(id);
??????? 索引已創(chuàng)建。

?????? SQL> alter table rice1 add? constraints pk_id1 primary key(id );
?????? 表已更改。

兩種方法的卻別是：在表比較大的時(shí)候可以通過方法二先創(chuàng)建唯一索引加上online，這樣可以不鎖表，減少對表的讀寫堵塞，不影響業(yè)務(wù)。

SQL> select si.PROPERTY,ui.index_name
? 2? from sys.ind$ si,dba_indexes ui,dba_objects uo
? 3? where si.obj#=uo.OBJECT_ID
? 4? and ui.index_name=uo.OBJECT_NAME
? 5? and ui.table_name in ('RICE','RICE1');

? PROPERTY INDEX_NAME
---------- ------------------------------
????? 4097 PK_ID
???????? 1 PK_ID1

Pk_id 對應(yīng)的16進(jìn)制為1001，有1000+1,即索引性質(zhì)：unique+ The index was created by a constraint,而PK_id1? 則為unique索引。

5、Foreign key:外鍵約束

當(dāng)兩張表有有相同的一列或者多列的時(shí)候，可以通過外鍵來連接兩個(gè)表的關(guān)系，也可以叫做參照完整性約束，一個(gè)表的約束列必須參照另一個(gè)表相同列中定義的值。

外鍵可以使依賴另外一張表的某個(gè)列或多列，也可以是依賴本張表的某個(gè)列

The relational model permits the value of foreign keys to match either the referenced primary or unique key value, or be null

5.1外鍵的創(chuàng)建

SQL> create table t1 as select *From emp;
表已創(chuàng)建。

SQL> create table t2 as select *From dept;
表已創(chuàng)建。

SQL> select *From t1;
???? EMPNO ENAME????? JOB????????????? MGR HIREDATE????????????? SAL?????? COMM???? DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
????? 7369 SMITH????? CLERK?????????? 7902 17-12月-80??????????? 800??????????????????? 20
????? 7499 ALLEN????? SALESMAN??????? 7698 20-2月 -81?????????? 1600??????? 300???????? 30
????? 7521 WARD?????? SALESMAN??????? 7698 22-2月 -81?????????? 1250??????? 500???????? 30
????? 7566 JONES????? MANAGER???????? 7839 02-4月 -81?????????? 2975??????????????????? 20
????? 7654 MARTIN???? SALESMAN??????? 7698 28-9月 -81?????????? 1250?????? 1400???????? 30
????? 7698 BLAKE????? MANAGER???????? 7839 01-5月 -81?????????? 2850??????????????????? 30
????? 7782 CLARK????? MANAGER???????? 7839 09-6月 -81?????????? 2450??????????????????? 10
????? 7839 KING?????? PRESIDENT??????????? 17-11月-81?????????? 5000??????????????????? 10
????? 7844 TURNER???? SALESMAN??????? 7698 08-9月 -81?????????? 1500????????? 0???????? 30
????? 7900 JAMES????? CLERK?????????? 7698 03-12月-81??????????? 950??????????????????? 30
????? 7902 FORD?????? ANALYST???????? 7566 03-12月-81?????????? 3000??????????????????? 20
????? 7934 MILLER???? CLERK?????????? 7782 23-1月 -82?????????? 1300??????????????????? 10
已選擇12行。

SQL> select *From t2;
??? DEPTNO DNAME????????? LOC
---------- -------------- -------------
??????? 10 ACCOUNTING???? NEW YORK
??????? 20 RESEARCH?????? DALLAS
??????? 30 SALES????????? CHICAGO
??????? 40 OPERATIONS???? BOSTON

SQL> alter table t1 add constraint? fk_t2 foreign key(deptno) references t2(deptno);
alter table t1 add constraint? fk_t2 foreign key(deptno) references t2(deptno)
?????????????????????????????????????????????????????????????????????? *
第 1 行出現(xiàn)錯(cuò)誤:
ORA-02270: 此列列表的唯一關(guān)鍵字或主鍵不匹配

SQL> alter table t2 add constraint pk_t2 primary key(deptno);
表已更改。

SQL> alter table t1 add constraint? fk_t1 foreign key(deptno) references t2(deptno);
表已更改。

SQL> update t1 set deptno=null where deptno=10;
已更新3行。

SQL> select *From t1;

???? EMPNO ENAME????? JOB????????????? MGR HIREDATE????????????? SAL?????? COMM???? DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
????? 7369 SMITH????? CLERK?????????? 7902 17-12月-80??????????? 800??????????????????? 20
????? 7499 ALLEN????? SALESMAN??????? 7698 20-2月 -81?????????? 1600??????? 300???????? 30
????? 7521 WARD?????? SALESMAN??????? 7698 22-2月 -81?????????? 1250??????? 500???????? 30
????? 7566 JONES????? MANAGER???????? 7839 02-4月 -81?????????? 2975??????????????????? 20
????? 7654 MARTIN???? SALESMAN??????? 7698 28-9月 -81?????????? 1250?????? 1400???????? 30
????? 7698 BLAKE????? MANAGER???????? 7839 01-5月 -81?????????? 2850??????????????????? 30
????? 7782 CLARK????? MANAGER???????? 7839 09-6月 -81?????????? 2450
????? 7839 KING?????? PRESIDENT??????????? 17-11月-81?????????? 5000
????? 7844 TURNER???? SALESMAN??????? 7698 08-9月 -81?????????? 1500????????? 0???????? 30
????? 7900 JAMES????? CLERK?????????? 7698 03-12月-81??????????? 950??????????????????? 30
????? 7902 FORD?????? ANALYST???????? 7566 03-12月-81?????????? 3000??????????????????? 20
????? 7934 MILLER???? CLERK?????????? 7782 23-1月 -82?????????? 1300

已選擇12行。

結(jié)論：(1)外鍵參考的表上的列必須有主鍵

?????????? (2)外鍵可以為空

5.2? 外鍵和主表的引用鍵在刪除的時(shí)候會(huì)有一些矛盾，比如，在刪除主表的引用鍵的行，或者修改主表引用鍵

DML Statements Allowed by Update and Delete No Action

DML StatementIssued Against Parent TableIssued Against Child Table

INSERT	Always OK if the parent key value is unique	OK only if the foreign key value exists in the parent key or is partially or all null
UPDATE NO ACTION	Allowed if the statement does not leave any rows in the child table without a referenced parent key value	Allowed if the new foreign key value still references a referenced key value
DELETE NO ACTION	Allowed if no rows in the child table reference the parent key value	Always OK
DELETE CASCADE	Always OK	Always OK
DELETE SET NULL	Always OK	Always OK

（1）刪除主鍵，外鍵數(shù)據(jù)也級聯(lián)刪除

SQL> create table t1 (id number

??????????????????????????????????? ?,name varchar2(10),

???????????????????????????????????? constraints pk_id primary key(id));
表已創(chuàng)建。

SQL> create table t2(id number,

?????????????????????????????????? course varchar2(5),

constraints fk_id foreign key (id) references t1(id) on delete cascade);

表已創(chuàng)建。

SQL> insert into t1 values(1,'a');

已創(chuàng)建 1 行。

SQL> insert into t1 values(2,'b');

已創(chuàng)建 1 行。

SQL> insert into t1 values(3,'c');

已創(chuàng)建 1 行。

SQL> insert into t2 values(2,'maths');

已創(chuàng)建 1 行。

SQL> insert into t2 values(3,'maths');

已創(chuàng)建 1 行。

SQL> commit;

提交完成。

SQL> select *from t1;

??????? ID NAME
---------- --------------------
???????? 1 a
???????? 2 b
???????? 3 c

SQL> select *from t2;

??????? ID COURSE
---------- ----------
???????? 2 maths
???????? 3 maths

SQL> update t1 set id=null where id=2;
update t1 set id=null where id=2
????????????? *
第 1 行出現(xiàn)錯(cuò)誤:
ORA-01407: 無法更新 ("SCOTT"."T1"."ID") 為 NULL

SQL> delete t1 where id=2;

已刪除 1 行。

SQL> select *From t1;

??????? ID NAME
---------- --------------------
???????? 1 a
???????? 3 c

SQL> select *From t2;

??????? ID COURSE
---------- ----------
???????? 3 maths

（2）主表刪除，外鍵表設(shè)置成空

SQL> create table t1 (id number,

????????????????????????????????????????? name varchar2(10)? ,

?????????????????????????????????????????? constraints pk_id primary key(id));

表已創(chuàng)建。

SQL> create table t2(id number,

?????????????????????????????????????????? course varchar2(5),

?????????????????????????????????????? constraints fk_id foreign key (id) references t1(id) on delete set null);

表已創(chuàng)建。

SQL>? insert into t1 values(1,'a');

已創(chuàng)建 1 行。

SQL>? insert into t1 values(2,'b');

已創(chuàng)建 1 行。

SQL>? insert into t1 values(3,'c');

已創(chuàng)建 1 行。

SQL>? insert into t2 values(2,'maths');

已創(chuàng)建 1 行。

SQL>? insert into t2 values(3,'maths');

已創(chuàng)建 1 行。

SQL> commit;

提交完成。

SQL> select *from t1;

??????? ID NAME
---------- --------------------
???????? 1 a
???????? 2 b
???????? 3 c

SQL> select *from t2;

??????? ID COURSE
---------- ----------
???????? 2 maths
???????? 3 maths

SQL> delete t1? where id=2;

已刪除 1 行。

SQL> select *from t1;

??????? ID NAME
---------- --------------------
???????? 1 a
???????? 3 c

SQL> select *from t2;

??????? ID COURSE
---------- ----------
?????????? maths
???????? 3 maths

SQL> roll
回退已完成。
SQL> update t1 set id=4 where id=3;
update t1 set id=4 where id=3
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-02292: 違反完整約束條件 (SCOTT.FK_ID) - 已找到子記錄

5.3 鎖和外鍵

在大多數(shù)情況下，用戶需要在外鍵（foreign key）上創(chuàng)建索引。但有一種情況例外，當(dāng)外鍵所引用的主鍵（primary key）或唯一鍵（unique key）從不更新或刪除時(shí)，外鍵上可以不創(chuàng)建索引。?如果不在外鍵上建立索引，那么對主鍵更新時(shí)會(huì)對外鍵表瞬時(shí)產(chǎn)生一個(gè)4號鎖，會(huì)阻止外鍵表的dml操作。?

如果外鍵上沒有定義索引，對父表的主鍵執(zhí)行 DML 操作時(shí)必須獲得子表上的共享行排他表級鎖（share row exclusive table lock）（也稱為share-subexclusive table lock，SSX）。此鎖能夠阻止其他事務(wù)對子表執(zhí)行 DML 操作。SSX 鎖在獲得后立即釋放。如果父表中有多個(gè)主鍵值被更新或刪除，對每行執(zhí)行 DML 操作時(shí)都需要對子表進(jìn)行一次加鎖及解鎖操作。
　如果外鍵上定義了索引，則對父表的主鍵執(zhí)行 DML 操作時(shí)只需獲得子表上的行共享表級鎖（row share table lock）（也稱為 subshare table lock，SS）。此鎖不允許其他事務(wù)排他地對子表加鎖，但允許對父表及子表執(zhí)行 DML 操作。如果對子表進(jìn)行更新操作的同時(shí)有可能存在對父表的更新或刪除操作，則適宜采取本節(jié)講述的鎖機(jī)制。對父表的插入，更新，及刪除操作無需獲得子表上的鎖，但更新及刪除操作需要等待子表索引上的行級鎖。

??????????????? ?如果子表的外鍵約束定義中指定了 ON DELETE CASCADE 選項(xiàng)，則刪除主表數(shù)據(jù)時(shí)將導(dǎo)致相關(guān)的子表數(shù)據(jù)同時(shí)被刪除。在這種情況下，Oracle 采用的鎖機(jī)制與用???????????????戶先手工地刪除子表數(shù)據(jù)，再刪除相關(guān)的主表數(shù)據(jù)時(shí)采用的鎖機(jī)制相同。

6、Check: 檢查約束
check約束是建立一列或者一組列上，要求條件為真，才能插入。

建表時(shí)創(chuàng)建check約束

SQL> create table t1(id number check(id>1 and id<10),

??????????????????????????????????? id1 number check(id1 in (1,2)));

表已創(chuàng)建。

SQL> insert into t1 values(11,1)
? 2? ;
insert into t1 values(11,1)
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-02290: 違反檢查約束條件 (SCOTT.SYS_C0013556)

SQL> insert into t1 values(10,1)
? 2? ;
insert into t1 values(10,1)
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-02290: 違反檢查約束條件 (SCOTT.SYS_C0013556)

SQL> insert into t1 values(9,1)
? 2? ;

已創(chuàng)建 1 行。

指定約束名：

SQL> create table t2(x varchar2(2),constraints ckeck_x check(x='f'or x='m'));

表已創(chuàng)建。

刪除約束：

SQL> alter table t2 drop constraints? ckeck_x;

表已更改。

SQL> insert into t2 values('m');

已創(chuàng)建 1 行。

commit；

建表后創(chuàng)建約束：

SQL> alter table t2 add constraints ckeck_a check(x in ('a','b'));
alter table t2 add constraints ckeck_a check(x in ('a','b'))
?????????????????????????????? *
第 1 行出現(xiàn)錯(cuò)誤:
ORA-02293: 無法驗(yàn)證 (SCOTT.CKECK_A) - 違反檢查約束條件

SQL> select *From t2;

X
--
m

因?yàn)楸碇幸呀?jīng)存在數(shù)據(jù)，再建約束和以前的約束不一樣，所以會(huì)報(bào)錯(cuò)

SQL> alter table t2 add constraints ckeck_a check(x in ('a','b')) novalidate;

表已更改。

SQL> insert into t2 values('y');
insert into t2 values('y')
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-02290: 違反檢查約束條件 (SCOTT.CKECK_A)

SQL> insert into t2 values('a');

已創(chuàng)建 1 行。

這時(shí)可以加novalidate，使得約束對已存在的數(shù)據(jù)不檢測，只檢查新添加進(jìn)來的數(shù)據(jù)。

7、REF:

8 約束的狀態(tài)?

你可以指定一個(gè)約束用來約束現(xiàn)有數(shù)據(jù)或是以后新插入的數(shù)據(jù)

If you change the state of any single constraint from ENABLE NOVALIDATE to ENABLE VALIDATE, then the operation can be performed in parallel, and does not block reads, writes, or other DDL operations.

你不能讓外鍵引用一個(gè)關(guān)閉的主鍵或者唯一鍵

Modified DataExisting DataSummary

ENABLE	VALIDATE	Existing and future data must obey the constraint. An attempt to apply a new constraint to a populated table results in an error if existing rows violate the constraint.
ENABLE	NOVALIDATE	The database checks the constraint, but it need not be true for all rows. Thus, existing rows can violate the constraint, but new or modified rows must conform to the rules.
DISABLE	VALIDATE	The database disables the constraint, drops its index, and prevents modification of the constrained columns.
DISABLE	NOVALIDATE	The constraint is not checked and is not necessarily true.

If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specifyKEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.

如果你打開唯一索引或者主鍵約束，在那一列上沒有索引，那么oracle會(huì)創(chuàng)建一個(gè)唯一索引。除非你在關(guān)閉約束的時(shí)候指定keep index。這個(gè)索引會(huì)隨著約束關(guān)閉而刪除，隨著約束的啟用而打開

（1）enable+validate? 對表中現(xiàn)有數(shù)據(jù)以及后來的數(shù)據(jù)都會(huì)檢測，如果有違反約束的，就會(huì)報(bào)錯(cuò)。

（2）enable+novalidate 對表中已經(jīng)存在的數(shù)據(jù)不檢測，對以后的數(shù)據(jù)insert 或者update會(huì)檢測

（3）disable+validate 數(shù)據(jù)庫關(guān)閉約束，刪除約束列上的索引，阻止修改約束列的值，但是約束是合法的，多用在數(shù)據(jù)倉庫中，可以讓你節(jié)省索引的空間

（4）disable+novalidate 約束不會(huì)檢測

You cannot drop a table whose primary key is being referenced by a foreign key even if the foreign key constraint is inDISABLENOVALIDATE state. Further, the optimizer can use constraints inDISABLENOVALIDATE state.

SQL> create table t1(id number check(id>1 and id<10),

??????????????????????????????????? id1 number check(id1 in (1,2)));

表已創(chuàng)建。