Oracle约束的学习与测试

最新推荐文章于 2022-07-27 18:04:13 发布

cprkdx1264

最新推荐文章于 2022-07-27 18:04:13 发布

阅读量155

点赞数

约束的学习与测试

参考文档

<>P648

<>P444

1 约束的管理

2 约束的类型

3 约束的检查机制

4 约束的状态

5 Notes

1 约束的管理

1.1 建立约束

Create Inline:

create table emp(

empno number(5) constraint pk_emp primary key,

empname varchar2(20)

);

Create outline:

create table emp( empno number(5), empmname varchar2(20), constraint pk_empno primary key(empno));

添加约束

alter table emp add constraint pk_emp primary key(empno);

禁止约束　相应的索引也被删掉．

alter table emp disable constraint pk_empno;

禁止约束　保留索引

alter table emp disable constraint pk_empno KEEP INDEX;

打开约束，同时创建相应的索引．

alter table emp enable constraint pk_empno;

将主表主键禁止的同时，也将禁止依赖于此主键的外键禁止了．

ALTER TABLE depart DISABLE PRIMARY KEY CASCADE;

重命名约束：

ALTER TABLE emp RENAME CONSTRAINT pk_empno TO pk_emp;

1.2 删除约束:

alter table emp drop primary key [keep index];

alter table emp drop unique (empno,empname);

alter table emp drop constraint pk_emp;

1.3 查询约束的信息

select owner, constraint_type, constraint_name, table_name, status from user_constraints;

查询约束所在的具体列

select owner, constraint_name, table_name, column_name from user_cons_columns;

1.4 推迟约束

Deferring Constraint Checks

当前事务的Constraint Checks全部推迟

SET CONSTRAINTS ALL DEFERRED;

SET CONSTRAINT XXX DEFERRED;

当前会话的Constraint Checks．

ALTER SSSSION SET CONSTRAINTS ALL deferred;

ALTER SSSSION SET CONSTRAINT xxx deferred;

2 约束的类型

2.1 NOT NULL CONSTRAINT

A NOT NULL constraint prohibits a column from containing nulls, default is NULL.

Oracle doesnot index table rows in which all key columns are null except in the case of bitmap indexes. Therefore, if you want an index on all rows of a table, then you must either specify NOT NULL constraints for at least one of the index key column or create a bitmap index.

Restrictions on NOT NULL Constraints:

You cannot specify NULL or NOT NULL in a view constraint

You cannot specify NULL or NOT NULL for an attribute of an object.

2.2 UNIQUE CONSTRAINT

A unique constraint designates a column as a unique key. A composite unique key designates a combination of columns as the unique key.

To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls. To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.

When you specify a unique constraint on one or more columns, Oracle creates an index on the unique key. The name of the index is the same as the name of the constraint.

假定一个composite unique key定义(col1,col2),则(null, null) 与 (null, null)不会违反唯一性约束，而(null,a)　与(null, a)则被视为是相同的，违反唯一性约束．

SQL> drop table t;

Table dropped

SQL> create table t( id number unique, name varchar2(20) );

Table created

SQL> insert into t values(null,'gdut');

1 row inserted

SQL> insert into t values(null,'gdut');

1 row inserted

SQL> rollback;

Rollback complete

UNIQUE约束可以插入NULL. 因为Null不等于Null．所以即使有两行都包含Null，还是同样不违反Unique规则．但Unique并不能唯一地标识每一行．

create table t( id number, name varchar2(20), constraint composite_id_name unique(id,name) );

SQL> insert into t values(null, null);

1 row inserted

SQL> insert into t values(null, null);

1 row inserted

SQL> select count(*) from t where id is null;

COUNT(*)

----------

(null,null) 与(null, null) 都插入成功，不违反Unique composite_id_name约束．

SQL> rollback;

Rollback complete

SQL> insert into t values(1, null);

1 row inserted

SQL> insert into t values(1, null);

insert into t values(1, null)

ORA-00001: unique constraint (TEST.COMPOSITE_ID_NAME) violate

插入不成功

(1,null) 与　(1,null)被认为是相同的．

小结：1 单列UNIQUE约束中可以插入两行NULL.因为NULL不等于NULL.

2 复合UNIQUE约束中也可以插入两行(NULL,NULL),但不能插入两行(1, NULL).

3 加UNIQUEX约束时，Oracle自动在相应的Key Column上创建一个唯一索引．索引名与约束名相同．

Restrictions on Unique Constraints:

1) None of the columns in the unique key can be of LOB, LONG,LONG RAW, VARRAY, NESTED TABLE, OBJECT, REF, TIMESTAMP WITH TIME ZONE, or user-defined type.

2) A composite unique key cannot have more than 32 columns.

3) you cannot designate the same column or combination of columns as both a primary key and a unique key.

2.3 PRIMARY KEY CONSTRIANT

Oracle enforces all PRIMARY KEY constraints using indexes. A primary key constraint created is enforced by the implicit creation of:

1) A unique index on that column

2) A NOT NULL constraint for that column.

(主键约束意味着: 唯一索引和Not Null.)

Restrictions on Primary Key Constraints:

1) A table or view can have only one primary key.

2) The size of the primary key cannot exceed approximately one database block.

再加上UNIQUE中的三个限制．

2.4 FOREIGN KEY CONSTRAINT

A foreign key constraint ( also called a referential integrity constraint) designates a column as the foreign key and establishes a relationship between that foreign key and a specificed primary or unique key, called the referenced key.

除了外键Referential Integrity,还有Self-Referential Integrity Constraints. 同一个表中的引用．如员工与经理的关系．

相应的术语:

Foreign key: The column or set of columns included in the definition of the referential integrity constraint that reference a referenced key.

Referenced key: The unique key or primary key of the same or different table that is referenced by a foreign key. (可以是唯一键,不一定是主键)

Depenndent or child table: The table that includes the foreign key.

Referenced or parent table: The table that is referenced by the child table’s foreign key.

Restrictions on foreign constraints:

1) The child and parent tables must be on the same database (在分布式数据库中，外键不能跨节点，但触发器可以)

2) 外键允许为Null

加上一些类似其它约束的限制．

Actions ON DELETE:

Actions supported by Oracle when parent key is modified: UPDATE and DELETE NO ACTION, and DELETE CASCADE.(更改Parent Key时所允许的动作)

Update and Delete No Action(Default):意味着只有还有外键引用，就不能删除主表中的键值．

On delete cascade. 表示删除父表的时候，级联子表中引用的记录．

On delete set null: 表示删除父表的时候, 将子表中所引用记录的外键列设置为Null.

(不同的On delete字句与所加的锁有关系吗？要测试,无影响)

Concurrency control, indexes and foreign keys

You almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted.(外键总是应该被索引，加什么索引呢？参考<<外键加索引对锁的影响学习与测试>>)

2.5 CHECK CONSTRAINT

A check constraint lets you specify a condition that each row in the table must satisfy.

Oracle doesnot verify that conditions of check constraints are not mutally exclusive. Also donot assume any particular order of evaluation of the conditions.(如果有多个Check constraints, Oracle并不对它们之间是否有冲突做任何检查,也不能假定几个Check约束的检查顺序)

Restrictions on check constraints:

1) Cannot specify a check constraint for a view. Instead, define view using with check option clause.

2) Cannot refer to columns of other tables.

3) Check condition cannot include user-defined functions …

2.6 REF CONSTRAINT

Use for type REF. 先不学习．

3 约束的检查机制

IMMEDIATE(语句级) and DEFERRED(事务级)

3.1 IMMEDIATE 语句级Oracle performs its constraint checking after the statement has been completely executed.

create table staff(

employee_id number primary key,

name varchar2(20),

manager_id number references staff(employee_id));

insert into staff(1,'gdut',1);

insert into staff(2,'gdut',null);

外键可为空.

临时用表;

create table temp( id number, name varchar2(20), no number);

insert into temp values(3,'gdut',4);

insert into temp values(4,'gdut',3);

commit;

insert into staff values(3,'gdut',4)

ORA-02291: 违反完整约束条件 (TEST.SYS_C002123) - 未找到父项关键字

SQL> insert into staff values(4,'gdut',3);

insert into staff values(4,'gdut',3)

ORA-02291: 违反完整约束条件 (TEST.SYS_C002123) - 未找到父项关键字

Amultiple row INSERT statement, such as an INSERT statement with nested SELECT statement, can insert rows that reference one another.

(批量插入,所有插入完后再Check Constraint)

SQL> insert into staff select id,name,no from temp;

2 rows inserted

以上测试虽然只是基于Referential Integity,实际上所有约束(NOT NULL, UNIQUE KEY, PRIMARY KEY, FOREIGN KEY, CHECK )的检查一般都在语句执行完后再做.也可推迟到事务结束后再做.?如何推迟的呢?

3.2 DEFERRED(事务级)

A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to rollback;(推迟到事务提交时才检查约束,谨慎使用)

If a constraint causes an action(for example, delete cascade), that action is always tabked as part of the statement that caused it, whether the constraint is deferred or immediate.(不管约束有没推迟，由约束引起的动作都作为执行语句的一部分，如果由于违反约束而引起事务或语句的回滚，也将回滚掉由此引起的动作．)

4 约束的状态

4.1 DEFERRABLE CLAUSE

The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINTS statement. Default is NOT DEFERRABLE.(是否可以通过使用SET CONSTRAINTS词句来推迟约束检查到事务提交时)

4.2 INITIALLY IMMEDIATE and INITIALLY DEFERRED

INITIALLY IMMEDIATE indicate that oracle should check this constraint at the end of each subsequent SQL statement. Default.

INITIALLY DEFERRED indicate that oracle should check this constraint at the end of subsequent transactions.

This clause is not valid if you have declared the constraint to be NOT DEFERRABLE.

EANBEL and DISABLE

VALIDATE and NOVALIDATE

这四个选项参考<学习与测试>>

RELY and NORELY

RELY and NORELY are valid only when you are modifying an existing constraint.

不太懂什么意思．

5 NOTES

1 Unique and Primary key约束是通过索引来实现的，所以不能通过删除约束的方式来消除限制．