Microsoft PowerPoint - MIS_Lec03.ppt [相容模式]

Size: px

Start display at page:

Download "Microsoft PowerPoint - MIS_Lec03.ppt [相容模式]"

遍戚
9 years ago
Views:

1 關聯性與限制條件之管理 Constraint Type Add, Rename, Drop Constraints Constraint Information

2 Integrity Constraints 限制條件 Entity Integrity Constraints 配合主鍵限制 Referential Integrity Constraints 配合外來鍵限制 Domain Integrity Constraints 配合基數限制加入限制條件後, 資料庫將自動檢查輸入之資料是否符合條件, 輸入未符合之資料時會傳回錯誤訊息 Constraint Type NOT NULL 不允許空值存在 UNIQUE 不允許重複值存在 ( 但允許空值 ) PRIMARY KEY UNIQUE and NOT NULL FOREIGN KEY 存在於子表格中與父表格相關聯 CHECK 檢查資料是否符合定義域 ( 基數限制 )

3 建立限制條件 CREATE TABLE tablename (col datatype [DEFAULT expr] [CONSTRAINT col_cons cons_type,], [CONSTRAINT table_cons cons_type (cons_col) REFERENCES parent_table (pk_col) [ON DELETE option],,]); tablename,col,datatype: 表格名稱, 欄位名稱, 資料型態 expr: 欄位預設值 ( 如果有設定 DEFAULT) col_cons: column level 限制條件名稱 (inline) table_cons: table level 限制條件名稱 (out-of-line) cons_type: 限制條件種類 cons_col: 加上限制條件之欄位名稱 parent_table: 設定 fk 時所關聯之父表格名稱 pk_col: 設定 fk 時所關聯之父表格主鍵名稱 option: 當刪除具限制條件之 pk 資料時, 控制是否將關聯鍵之資料也刪除 (CASCADE) 或改為空值 (SET NULL)

4 建立 Not Null 或 Unique SQL> CREATE TABLE dept 2 (deptno NUMBER(2) PRIMARY KEY, 3 dname VARCHAR2(15) UNIQUE, 4 loc VARCHAR2(15) NOT NULL) ; 建立具 column level 限制條件之部門資料表 deptno: 部門編號, 設為主鍵 dname: 部門名稱, 必須是唯一值 loc: 部門位置, 不允許空值未手動建立限制條件名稱時, 系統會自動命名如 SYS_C 之類的名稱格式如欲加上限制條件名稱, 以 UNIQUE 為例, 可依規定設計如下 dname VARCHAR2(15) CONSTRAINT dept_dname_un UNIQUE,

5 建立 Primary Key SQL> CREATE TABLE dept 2 (deptno NUMBER(2), 3 dname VARCHAR2(15) UNIQUE, 4 loc VARCHAR2(15) NOT NULL, 5 CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno)); deptno 以附加條件的方式設為主鍵 dept_deptno_pk: 結合表格名稱欄位名稱及限制鍵型態作為主鍵名稱 table level 限制條件的方式如同在建立完表格之後再另外加限制條件, 維護上比較有條理, 多習慣用於主鍵外來鍵的設定

6 建立 Foreign Key SQL> CREATE TABLE emp 2 (empno NUMBER(4), 3 ename VARCHAR2(20) NOT NULL, 4 job VARCHAR2(20), 5 mgr NUMBER(4), 6 hiredate DATE, 7 sal NUMBER(7,2), 8 comm NUMBER(7,2), 9 deptno NUMBER(2), 10 CONSTRAINT emp_empno_pk PRIMARY KEY (empno), 11 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 12 REFERENCES dept(deptno)); 建立員工資料表 (emp), 並與部門資料表 (dept) 之 deptno 建立關聯以 empno 為主鍵,emp_empno_pk 為主鍵名稱以 deptno 為外來鍵, emp_deptno_fk 為外來鍵名稱此範例中相關聯的表格使用相同欄位名稱之關聯鍵 (deptno), 亦可使用不同名稱, 只要指定所關聯的表格名稱, 即可建立關聯

7 建立 Check SQL> CREATE TABLE bonus 2 (ename VARCHAR2(20), 3 job VARCHAR2(20), 4 sal NUMBER(7,2), 5 comm NUMBER(7,2), 6 CONSTRAINT bonus_sal_ck CHECK (sal > 1000)); 建立一個 bonus 表格記錄員工的職稱薪資及紅利建立 check 限制條件 bonus_sal_ck, 使薪資必須大於 1000 check 限制條件亦可加在欄位後面, 例如 sal NUMBER(7,2) CONSTRAINT bonus_sal_ck CHECK (sal > 1000), 如未定義限制條件名稱, 系統會自動取名 ( 如 SYS_C003183) 一個欄位的 check 限制條件可不只一個如欲修改 check 限制條件, 須先將其刪除再作新增條件的動作

8 修改 Not Null 限制條件 ALTER TABLE table_name MODIFY col_name [CONSTRAINT cons_name] NULL NOT NULL; Not Null 屬於 column level 限制條件, 可想像為欄位屬性的一種, 利用修改欄位屬性的方式新增或移除 Not Null 限制條件 cons_name: 限制條件名稱, 如未定義, 則系統將會以 SYS_C 之類的格式自動命名 Example: SQL> ALTER TABLE dept 2 MODIFY loc CONSTRAINT dept_loc_nn NOT NULL ; 新增 not null 限制條件 SQL> ALTER TABLE dept 2 MODIFY loc NULL ; 移除 not null 限制條件

9 新增 Unique 限制條件 ALTER TABLE table_name ADD [CONSTRAINT cons_name] UNIQUE (col_name); cons_name: 限制條件名稱, 如未定義, 則系統將會以 SYS_C 之類的格式自動命名 table_name, col_name: 表格及加限制之欄位名稱 Example: SQL> ALTER TABLE dept 2 ADD UNIQUE (dname) ; 新增 unique 限制條件於 dname 欄位並由系統自動命名 SQL> ALTER TABLE dept 2 ADD CONSTRAINT dept_dname_un UNIQUE (dname) ; 新增自行定義之 unique 限制條件於 dname 欄位

10 新增 Check ALTER TABLE table_name ADD [CONSTRAINT cons_name] CHECK (check condition); 如未定義限制條件名稱 ( 即未設定 CONSTRAINT 語句 ), 系統會自動取名 ( 如 SYS_C003183) 一個欄位的 check 限制條件可不只一個如欲修改 check 限制條件, 須先將其刪除再作新增條件的動作 Example: SQL> ALTER TABLE bonus 2 ADD CONSTRAINT bonus_sal_ck CHECK (sal > 1000)); 在 bonus 表格中新增 check 限制條件 bonus_sal_ck, 使薪資必須大於 1000 如表格內已經有資料, 但違反新增之限制條件, 則會出現錯誤訊息, 如無法驗證 (S bonus_SAL_CK) - 違反檢查條件

11 新增 Primary Key 限制條件 ALTER TABLE table_name ADD [CONSTRAINT cons_name] PRIMARY KEY (col_name); cons_name: 主鍵名稱, 如未定義, 則系統將會以 SYS_C 之類的格式自動命名 table_name, col_name: 表格及加限制之欄位名稱 Example: SQL> ALTER TABLE dept 2 ADD PRIMARY KEY (deptno) ; 新增 PK 限制條件於 deptno 欄位並由系統自動命名 SQL> ALTER TABLE dept 2 ADD CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno) ; 新增自行定義之 PK 限制條件於 deptno 欄位

12 新增 Foreign Key 限制條件 ALTER TABLE table_name ADD [CONSTRAINT cons_name] FOREIGN KEY (col_name) REFERENCES parent_table (ref_col) [ON DELETE option]; table_name, col_name: 表格及加限制之欄位名稱 cons_name: 外來鍵名稱, 如未定義, 則系統將會以 SYS_C 之類的格式自動命名 parent_name: 所關連之父表格名稱 ref_col: 所關連之主鍵欄位 option: 控制是否伴隨關聯欄位同步刪除 Example: SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 3 REFERENCES dept (deptno); 在 emp 新增自行定義之外來鍵於 deptno 欄位與 dept 產生關聯, 當 dept 的關聯欄位資料被刪除時, 外來鍵欄位資料不隨之異動

13 ON DELETE 與整合性限制 SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 3 REFERENCES dept (deptno) 4 ON DELETE SET NULL; 當父表格 dept 的關聯欄位 (deptno) 的資料被刪除時, 子表格 emp 的外來鍵欄位對應的資料設為 null SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 3 REFERENCES dept (deptno) 4 ON DELETE CASCADE; 當父表格 dept 的關聯欄位 (deptno) 的資料被刪除時, 子表格 emp 的外來鍵欄位對應的資料設為也一併刪除

14 重新命名或終止限制條件 ALTER TABLE table_name RENAME CONSTRAINT old_cons TO new_cons; old_cons: 原始限制條件名稱 new_cons: 更名後之限制條件名稱 ALTER TABLE table_name ENABLE DISABLE CONSTRAINT cons_name [CASCADE] ; ENABLE: 啟動限制條件 DISABLE: 終止限制條件 cons_name: 限制條件名稱 CASCADE: 限制條件終止或啟動後亦終止或啟動關聯欄位的限制條件 Example: SQL> ALTER TABLE dept RENAME CONSTRAINT 2 dept_deptno_pk TO dept_dno_pk; 將限制條件 dept_deptno_pk 更名為 dept_dno_pk SQL> ALTER TABLE dept DISABLE CONSTRAINT 2 dept_dno_pk CASCADE ; 終止限制條件 dept_dno_pk( 但未移除 ), 並同時終止相關聯欄位之限制條件

15 刪除限制條件 ALTER TABLE table_name DROP cons_type [(col_name)] [CASCADE] ; cons_type: 限制條件型態 col_name: 如限制條件為 UNIQUE, 需加上欄位名稱 cons_name: 限制條件名稱 CASCADE: 限制條件刪除後亦移除關聯欄位的限制條件 ALTER TABLE table_name DROP CONSTRAINT cons_name [CASCADE] ; cons_name: 限制條件名稱 Example: SQL> ALTER TABLE dept 2 DROP CONSTRAINT dept_deptno_pk CASCADE; 刪除主鍵 dept_deptno_pk 並移除關聯欄位之外來鍵 SQL> ALTER TABLE dept 2 DROP UNIQUE dname 移除 dname 之 UNIQUE 限制條件

16 查詢限制條件 Data Dictionary 資料字典提供資料庫管理人員查詢關聯表格屬性資訊 Oracle 伺服器之資料字典表格分 user_, dba_, all_ 三種層級, 一般使用者綱要權限內可查詢 user_ 層級的資料字典, 如 user_tables, user_constraints, user_views, 等 user_constraints 提供所有被使用者定義於關聯式資料表之限制條件資訊, 如 SQL> SELECT constraint_name, constraint_type, table_name, r_constraint_name 2 FROM user_constraints ; 查詢所有的限制條件之名稱型態與其存在的表格 user_cons_columns 提供被使用者定義限制條件之欄位資訊, 如 SQL> SELECT constraint_name, table_name, column_name 2 FROM user_cons_columns ; 查詢所有限制條件存在於表格的哪些欄位上

数据库系统基础 2/54 第 6 章数据库管理与维护

数据库系统基础 2/54 第 6 章数据库管理与维护数据库系统基础 1/54 数据库系统基础哈尔滨工业大学 2011.~2012. 数据库系统基础 2/54 第 6 章数据库管理与维护数据库系统基础 3/54 第 6 章数据库管理与维护 6.1 数据库管理员的基本职责 6.2 数据库存储与性能管理 6.3 数据库