完整性约束的类型（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 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 the referenced 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 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 condition 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.