完整性約束的型別（Types of Integrity Constraints）

Data Integrity 學習（一）

完整性約束的型別（Types of Integrity Constraints）學習

資料完整性的技術保障( Techniques for Guaranteeing Data Integrity)

1、   enforcing business rules with triggered stored database procedures,as described in “overview of triggers”

2、   using stored procedures to completely control access to data,as described in “introduction to server-side programming”

3、   enforing business rules in the code of a database application

4、   using oracle database integrity constraints,which are rules defined at the column or object level that restrict values in the database

完整性約束的優點( Advantages of Integrity Constraints)

1、 declarative ease

because you define integrity constraints using SQL statements,no additional programming is required when you define or alter a table. The SQL statements are easy to write and eliminate programming errors.

2、 centralized rules

integrity constraints are defined for tables and are stored in the data dictionary.thus,data entered by all applications must adhere to the same integrity constraints.if the rules change at the table level,then applications need not change.also,applications can use metadata in the data dirtionary to immediately inform. users of violations,even before the database checks the SQL statement.

3、 flexibility when loading data

you can disable integrity constraints temporarily to avoid performance overhead when loading large amounts of data.when the data load is complete,you can re-enable the integrity constraints.

 

完整性約束的型別（Types of Integrity Constraints）

Constraint Type	Description	See Also
NOT NULL	Allows or disallows inserts or updates of rows containing a in a specified column.
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.
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.
Foreign key	Designates a column as the foreign key and establishes a relationship between the foreign key and a primary or unique key, called the referenced key.
Check	Requires a database value to obey a specified condition.
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 a REF encapsulates a reference to a row object of a specified object type. Referential integrity constraints on REF columns ensure that there is a row object for the REF .	Oracle Database Object-Relational Developer's Guide to learn about REF constraints

 

完整性約束的型別總結 ：

1、 not null : 所有的列預設為NULL；SQL語法中’’和null 都被oracle認為是null；’null’被認為是null字元

2、 unique key : 唯一鍵約束保證資料的唯一性，null 通常被oracle 認為滿足唯一鍵約束，也就是唯一鍵約束列可以有重複的NULL值

3、 primary key : 非空唯一，自動建立索引

4、 foreign key : 子表的外來鍵列插入資料在父表主鍵列找到相應匹配的資料，允許插入；子表外來鍵列NULL也是很特殊，不受父表主鍵列的約束

5、 check constraints : 可以自定義檢查約束，不滿足條件的SQL自動回滾，同一列並行的CHECK需滿足互斥

6、 REF （瞭解不深，期待後續學習）

 

1、 not null (   all column default null;   SQL statements ‘’ or null is null;    ’null’ is chars)

doudou@TEST> desc t1

 Name                                                   Null?     Type

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

 ID                                                              NUMBER

 NAME                                                            VARCHAR2(40)

doudou@TEST> desc t2

 Name                                                   Null?     Type

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

 ID                                                              NUMBER

 NAME                                                   NOT NULL VARCHAR2(40)

【 Null? 列未指明 not null 都是預設為 null, 既允許為 null 】

doudou@TEST> insert into t1 values (1,null);

1 row created.

doudou@TEST> insert into t2 values (1,null);

insert into t2 values (1,null)

                         *

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DOUDOU"."T2"."NAME")

【 null is null so insert null for name is not allow 】

doudou@TEST> insert into t1 values (1,'');

1 row created.

doudou@TEST> insert into t2 values (2,'');

insert into t2 values (2,'')

                         *

ERROR at line 1:

ORA-01400: cannot insert NULL into ("DOUDOU"."T2"."NAME")

【 ’’ is null so insert null for name is not allow 】

doudou@TEST> insert into t1 values (3,'null');

1 row created.

doudou@TEST> insert into t2 values (3,'null');

1 row created.

【 ’null’ is chars so null insert into name 】

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t1;

        ID NAME

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

         1

         1

         3 null

doudou@TEST> select * from t2;

        ID NAME

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

         3 null

null 總結：

1、 all column default null;     

2、    SQL statements ‘’ or null is null;      

3、    ’null’ is chars

 

2 、 Unique Constraints

唯一約束，不允許有重複值

doudou@TEST> create table t3 (id number ,constraint id_uk unique (id));

Table created.

doudou@TEST> insert into t3 values (1);

1 row created.

doudou@TEST> insert into t3 values (1);

insert into t3 values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (DOUDOU.ID_UK) violated

a null always satisfies a unique key constraint ，但是 null 值不會被記錄

doudou@TEST> insert into t3 values (null);

1 row created.

doudou@TEST> insert into t3 values (null);

1 row created.

doudou@TEST> insert into t3 values (null);

1 row created.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t3;

        ID

----------

         1

Unique key 總結：

1、    唯一約束，約束列的資料為唯一

2、    A null 經常滿足唯一鍵約束

3 、 Primary Key Constraints

主鍵約束，非空唯一，自動建立索引

主鍵表語法

doudou@TEST> create table   t3 (id number primary key,name varchar2(20));

Table created.

主鍵自動生成 not null 約束

doudou@TEST> desc t3

 Name                                                   Null?     Type

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

 ID                                                     NOT NULL NUMBER

 NAME                                                            VARCHAR2(20)

主鍵自動建立主鍵索引 SYS_C007470, 我們未指定索引名，所以資料庫自動生成索引名 SYS_C007470

doudou@TEST>   select index_name,index_type,table_name from user_indexes where table_name='T3';

INDEX_NAME            INDEX_TYPE            TABLE_NAME

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

SYS_C007470           NORMAL –- 一般型別索引              T3

doudou@TEST> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name='T3';

CONSTRAINT_NAME                                               CO TABLE_NAME

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

SYS_C007470                                                   P --- 主鍵索引  T3

doudou@TEST> insert into t3 values (1,1);

 

1 row created.

doudou@TEST> insert into t3 values (1,2);

insert into t3 values (1,2)

*

ERROR at line 1:

ORA-00001: unique constraint (DOUDOU.SYS_C007470) violated   - - 主鍵包含唯一約束

Primary key 總結：

1、   not null

2、   unique key constraint

4 、 Foreign Key Constraints （外來鍵約束 NULL 也很特殊）

語法

doudou@TEST>   create table t3_fk (id number, name varchar2(20),

  2   foreign key (id) references t3);

Table created.

doudou@TEST> desc t3

 Name                                                   Null?     Type

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

 ID                                                     NOT NULL NUMBER

 NAME                                                            VARCHAR2(20)

doudou@TEST> desc t3_fk

 Name                                                   Null?     Type

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

 ID                                                              NUMBER

 NAME                                                            VARCHAR2(20)

doudou@TEST> insert into t3_fk values (1,'fk_1');

1 row created.

雖然主鍵列 id 有 not null 約束，但是外來鍵列 id 沒有 not null 約束，索引 null 可以插入 t3_fk

doudou@TEST>   insert into t3_fk values (null,'fk_2');

1 row created.

父表沒有匹配到值所以報錯，這個也是外來鍵約束主要的功效，主鍵列沒有值，外來鍵列插入值可以認為無意義，所以不能插入

doudou@TEST> insert into t3_fk values (2,'fk_3');

insert into t3_fk values (2,'fk_3')

*

ERROR at line 1:

ORA-02291: integrity constraint (DOUDOU.SYS_C007471) violated - parent key not found

Foreign key 總結

1、    子表的外來鍵列插入資料要去參考父表的主鍵列的資料

2、    子表的外來鍵列允許插入 NULL 值，不受父表主鍵列的約束

 

父表修改與外來鍵的關係

http://space.itpub.net/?uid-26442936-action-viewspace-itemid-753900

 

5 、 Check Constraints （並行 check 在同一列需保證約束為互斥）

A check constraint on a column or set of columns requires that a specified be true or unknown for every row. If DML results in the condition of the constraint evaluating to false, then the SQL statement is rolled back.

  Check constraint 不滿足 check 約束的 SQL ，會自動 ROLL BACK ， check 可以滿足一些自定義約束的需求

doudou@TEST> create table t5_check (id number,name varchar2(40),

  2    constraint t5_check_id check (id>10),

  3   constraint t5_check_name check (name IN ('DOUDOU','XIAOYU')));

Table created.

doudou@TEST> insert into t5_check values (1,'DOUDOU');

insert into t5_check values (1,'DOUDOU')

*

ERROR at line 1:

ORA-02290: check constraint (DOUDOU.T5_CHECK_ID) violated

doudou@TEST> insert into t5_check values (11,' 新年 ');

insert into t5_check values (11,' 新年 ')

*

ERROR at line 1:

ORA-02290: check constraint (DOUDOU.T5_CHECK_NAME) violated

doudou@TEST> insert into t5_check values (11,'DOUDOU');

1 row created.

doudou@TEST> commit;

Commit complete.

doudou@TEST> select * from t5_check;

 

        ID NAME

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

        11 DOUDOU

Check contraints 總結：

1、    check 約束可以自定義約束，違反約束的 SQL 會自動回滾

2、    同一列多個 check 約束需保持他們的是互斥的關係

6 、 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 a REF encapsulates a reference to a row object of a specified object type. Referential integrity constraints on REF columns ensure that there is a row object for the REF .