oracle添加男女约束,oracle constraints约束

ALL_CONSTRAINTS

ALL_CONSTRAINTS describes

constraint definitions on tables accessible to the current

user.

Related Views

DBA_CONSTRAINTS describes

all constraint definitions in the database.

USER_CONSTRAINTS describes

constraint definitions on tables in the current user's schema.

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)

NOT NULL

Owner of the

constraint definition

CONSTRAINT_NAME

VARCHAR2(30)

NOT NULL

Name of the constraint

definition

CONSTRAINT_TYPE

VARCHAR2(1)

Type of constraint

definition:

C (check constraint on a

table)

P (primary key)

U (unique key)

R (referential

integrity)

V (with check option, on a

view)

O (with read only, on a

view)

TABLE_NAME

VARCHAR2(30)

NOT NULL

Name associated with

the table (or view) with constraint definition

SEARCH_CONDITION

LONG

Text of search

condition for a check constraint

R_OWNER

VARCHAR2(30)

Owner of table

referred to in a referential constraint

R_CONSTRAINT_NAME

VARCHAR2(30)

Name of the unique

constraint definition for referenced table

DELETE_RULE

VARCHAR2(9)

Delete rule for a

referential constraint (CASCADE or NO

ACTION)

STATUS

VARCHAR2(8)

Enforcement status of

constraint (ENABLED or

DISABLED)

DEFERRABLE

VARCHAR2(14)

Whether the

constraint is deferrable

DEFERRED

VARCHAR2(9)

Whether the

constraint was initially deferred

VALIDATED

VARCHAR2(13)

Whether all data

obeys the constraint (VALIDATED or NOT

VALIDATED)

GENERATED

VARCHAR2(14)

Whether the name of

the constraint is user or system generated

BAD

VARCHAR2(3)

A YES value indicates that this constraint

specifies a century in an ambiguous manner. To avoid errors

resulting from this ambiguity, rewrite the constraint using the

TO_DATE function with a

four-digit year.

RELY

VARCHAR2(4)

Whether an enabled

constraint is enforced or unenforced.

LAST_CHANGE

DATE

When the constraint

was last enabled or disabled

INDEX_OWNER

VARCHAR2(30)

Name of the user

owning the index

INDEX_NAME

VARCHAR2(30)

Name of the index

(only shown for unique and primary-key constraints)

INVALID

VARCHAR2(7)

Whether the

constraint is invalid

VIEW_RELATED

VARCHAR2(14)

Whether the

constraint depends on a view

NOT NULL 設定該欄立不可含Null 值

UNIQUE 設定一個或多個欄位組合，資料內容需唯一不可重覆

PRIMARY KEY 設定資料表格不可重覆、空白或為Null的主鍵值

FOREIGN KEY 設定該欄位值的存在必須關連並參照指定資料表格的欄位值

CHECK 設定需符合所列示的條件值

注意事項

所有的Constraints資料均會存放在系統資料表格(Data

Dictionary)中。建立Constraint時若賦予有意義的名稱，會較易查核Constraint的資料。Constraint標準的命名方式，應加入物件名稱。若未定名，則Oracle會自動以SYS_Cn的唯一名稱為之命名。USER_CONSTRAINTS可查核所設定的Constraintion資料。

設立Constraints

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]

[column_constraint],...

[table_constraint][,...]);

Schema：與資料表格擁有者相同的名稱。

Table：設定資料表格名稱。

DEFAULT expr：為欄位設定預設值。

Column：設定資料表格內的欄位名稱。

Datatype：設定欄位資料型態及長度。

Column_constraint：設定欄位層級的約束條件，以維護資料的完整性。

Table_constraint：設定資料表格層級的約束條件，以維護資料的完整性。

ex:

CREATE TABLE emp(

empno NUMBER(4),

ename VARCHAR2(10),...

deptno NUMBER(7,2) NOT NULL,

CONSTRAINT emp_empno_pk

PRIMARY KEY (EMPNO));

建立 Constraints 的層級

欄位層級的 constraint

column [CONSTRAINT constraint_name] constraint_type,

資料表格層級的 constraint

column,...[CONSTRAINT constraint_name] constraint_type(column,

...),

Column 單一欄位的參照設定，可定義任何型態的Constraint。

Table 一個或多個欄位的參照設定，可定義除了NOT NULL以外的，任何型態Constraint。

NOT NULL Constraint的用途

新增資料時，Oracle會對不給值的欄位，預設填入Null值。若確保該欄位值不可為Null值，需於該欄位設定NOT

NULL的Constraint條件，當資料異動時，即可自動執行資料內容檢核的動作。

ex:

..deptno NUMBER(7,2)

CONSTRAINT emp_deptno_nn NOT NULL…..

使用 UNIQUE Constraint

為一個或組合性的欄位，設立UNIQUE Constraints可確保一個或組合性的欄位值的資料唯一。為一個欄立設立UNIQUE

Constraint時，可接受Null值。

ex:

CREATE TABLE dept(

deptno NUMBER(2),

dname VARCHAR2(14),

loc VARCHAR2(13),

CONSTRAINT dept_dname_uk UNIQUE(dname));

設立UNIQUE Constraint時，Oracle 會隱含式自動為所設立的欄位，建置Unique Index的索引檔。

PRIMARY KEYConstraint

每一個資料表格，僅能擁有一個PRIMARY KEY的Constraints，建立主鍵值。PRIMARY

KEY可以是一個欄位或組合式欄位，其欄位值必須唯一且不可含Null值。

ex:

CREATE TABLE dept(

deptno NUMBER(2),

dname VARCHAR2(14),

loc VARCHAR2(13),

CONSTRAINT dept_dname_uk UNIQUE (dname),

CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

設立PRIMARY KEY Constraint時，Oracle 會隱含式自動為所設立的欄位，建置Unique

Index的索引檔。

FOREIGN KEYConstraint

FOREIGN KEY是維護資料庫資料完整性的Constraint。以FOREIGN

KEY偵測一個或組合式欄位，與自有的或其他資料表格的PRIMARY KEY或UNIQUE

Constraint欄位建立資料關連。如主檔與明細檔(父子檔)間的關連。單一的 FOREIGN

KEY欄位，可接受Null值。設有FOREIGN KEY

Constraint條件的欄位值(子檔)，必須參照並符合被參照檔(父檔)，已存在的欄位值。

ex:

CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept

(deptno));

FOREIGN KEY要設定前，應先確定所要參照的PRIMARY KEY 或 UNIQUE 已事先設置完成，可供參照。

FOREIGN KEY 的保留字

FOREIGN KEY定義於子檔中，且子檔含有可參照父檔的關連欄位。

FOREIGN KEY的設定，由下列保留字所組成：

FOREIGN KEY：於子檔設定可關連參照的欄位。

REFERENCES：用以指定所參照的資料表格名稱及欄位名稱。

ON DELETE CASCADE：設定當父檔的資料刪除時，子檔中相關連的明細資料，亦會一併被刪除。

若未設定ON DELETE CASCADE，在刪除父檔的資料時，若子檔仍有相關連的明細資料存在，會產生錯誤訊息。

CHECK Constraint

若以CHECK

Constraint為欄位設定列示值條件，資料表格中的每一筆資料列，均需符合所列示的限制條件。一個欄位，可設定多個CHECK

Constraint列示條件，衹要邏輯條件不衝突，CHECK Constraint 的個數不限。CHECK

Constraint，可設立於資料表格或欄位層級。

ex:

CONSTRAINT emp_deptno_ck

CHECK (DEPTNO BETWEEN 10 AND 99),...

每一資料列的資料內容，均需滿足檢核條件

不可做為檢核條件的表述句:

參照 CURRVAL, NEXTVAL, LEVEL, 及 ROWNUM 等虛擬欄位名稱呼叫 SYSDATE, UID, USER,

及 USERENV 等函數使用

以其它資料列的資料內容做為檢核條件

新增Constraints

於資料表格建置完成後，可使用ALTER TABLE..ADD方式新增Constraint。

Table：設定資料表格名稱。

Constraint：Constraint的保留字及所指定的Constraint名稱。

Type：設定所要新增的Constraint 種類。

Column：設定Constraint運作的欄位名稱。

ALTER TABLE table

ADD [CONSTRAINT constraint] type (column);

Constraint可以新增、刪除、啟動或關閉，但不能更改其架構。

欲在已建置完成的資料表格，新增NOT NULL Constraint時，需於 ALTER TABLE 指令中，使用

MODIFY子句，否則無法設立。新增NOT NULL Constraint

時，若資料表格中已有資料，Constraint會進行檢核，若已存在有Null值，設立Constraint時會產生錯誤訊息。

ex:

ALTER TABLE emp

ADD CONSTRAINT emp_mgr_fk

FOREIGN KEY(mgr) REFERENCES emp(empno);

刪除Constraints

刪除Constraint前，可先查核系統資料表格中的USER_CONSTRAINTS及USER_CONS_COLUMNS資料內容。確定需求後，再進行刪除作業。

ALTER TABLE table

DROP PRIMARY KEY | UNIQUE (column) |

CONSTRAINT constraint [CASCADE];

DROP指令，若使用CASCADE子句，可將有參照關連的Constraint一併刪除。

關閉Constraints

對於不想刪除再重建的Constraint，可使用ALTER

TABLE..DISABLE指令，暫時性關閉Constraint的檢核作用。

ALTER TABLE emp

DISABLE CONSTRAINT

emp_empno_pk CASCADE;

DISABLE子句，可使用於CREATE TABLE 及ALTER TABLE語法中。

CASCADE子句，可同時關閉有參照關連的Constraint作用。

啟動Constraints

對於被設定為DISABLE的Constraint，可使用ALTER

TABLE…..ENABLE指令，重新啟動Constraint的檢核作用。

ALTER TABLE table

ENABLE CONSTRAINT constraint;

啟動Constraint作用時，會對資料表格中的所有資料列，檢核欄位值的合理性，故已存在的資料內容，需符合Constraint

的檢核條件。

啟動UNIQUE或PRIMARY KEY的Constraint 時，Oralce會自動為所設定的欄位，建置一個Unique

Index索引檔。

ENABLE子句，可使用於CREATE TABLE 及ALTER TABLE語法中。

查核Constraints資訊

資料表格建置完成後，若以DESC指令查驗資料表格的檔案結構，針對Constraint，衹能查看是否有NOT

NULL的Constraint記錄。若要查看資料表格上的所有Constraint設定，可查核USER_CONSTRAINTS系統資料表格檔。若Constraint設立之初，使用者未設定Constraint名稱，所查看到的會是系統所配置的系統名稱。

SELECT constraint_name,constraint_type,search_condition

FROM user_constraints

WHERE table_name = 'EMP';

USER_CONSTRAINT的Constraint Type欄位值定義：

Ｐ：Primary key。

Ｒ：Foreign key。

Ｕ：Unique。

Ｃ：Check 或 Not Null。

查核USER_CONS_COLUMNS

USER_CONS_COLUMNS系統資料表格，可查核Constraint所對應的欄位名稱。尤其是查核系統自動配置的系統名稱(SYS_Cn)所對應的欄位，非常有效。常與USER_CONSTRAINTS的查核，搭配使用。

ex:

SELECT constraint_name,column_name

FROM user_cons_columns

WHERE table_name = 'EMP';

In order to finish realizing a schema in Oracle, it is necessary

to add the required constraints. Oracle supports several types of

constraints, to enforce complex rules may require the use of active

database features such as triggers. In this section the examples

are limited to key constraints.

Constraints can be added to new tables or after table creation.

To add primary key constraints to a new table specify the primary

key after the columns similar to the following:

create table tablename ( columnname type, columnname type ..., primary key(keycolumn,keycolumn,...);

where keycolumn is the name of a column that is part of

the key.

Foreign keys should refer to unique tuples. To create a new

table with foreign keys in addition use:

create table tablename ( columnname type, columnname type ..., primary key(keycolumn,keycolumn,...),

foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable,

foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable,...);

where foreignkeycolumn is the column of the foreign key

in table foreigntable. It must match a column in the current table.

You can also add constraints with the alter table command where

tablename is the name of the table and

tableconstraint is the constraint definition:

Alter table tablename add tableconstraint;

Often you may wish to see what constraints exist on a table. The

view USER_CONSTRAINTS is maintained by Oracle and has information

about constraints. Some interesting columns include, the

CONSTRAINT_NAME, CONSTRAINT_TYPE where P refers to primary key, and

R is a foreign key constraints, the TABLE_NAME on which it is

defined, and the R_CONSTRAINT_NAME for foreign key constraints.

Type DESCRIBE USER_CONSTRAINTS for more informaion.

Once you know the CONSTRAINT_NAME, you can use the

USER_CONS_COLUMNS view to find the COLUMN_NAME and POSITION for

that CONSTRAINT_NAME. You can query these views as you would any

other table:

select column_name, position, constraint_name from User_cons_columns;

At times you may need to create constraints that are

interdependent, such as two tables that refer to each others

primary key in a foreign key constraint. One way to deal with this

problem is to defer constraints. They constraint must be made

deferrable and the constraints mode set to deferred. You can

substitute the names of specific constraints or use the all

keyword. They will be checked at the end of the transaction instead

of after each insert. To enable them make the mode immediate:

create table tablename ( columnname type, columnname type ...,

foreign key(foreignkeycolumn,foreignkeycolumn,...) references foreigntable deferrable);

set constraints all deferred;

set constraints all immediate;

Constraints can be deleted with the drop constraint command.

where tablename is the table name and someconstraint

is the constraint name:

alter table tablename drop constraint someconstraint;