Oracle約束的學習與測試

約束的學習與測試

參考文件

<>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)則被視為是相同的，違反唯一性約束．

 

 

小結：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.

 

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約束是透過索引來實現的，所以不能透過刪除約束的方式來消除限制．