Oracle約束
導(dǎo)讀：什么是約束?約束是表級(jí)強(qiáng)制執(zhí)行的規(guī)則，當(dāng)表中數(shù)據(jù)有相互依賴性時(shí)，可保護(hù)數(shù)據(jù)不被刪除。Oracle數(shù)據(jù)庫是一種功能性很強(qiáng)大的數(shù)據(jù)庫系統(tǒng)，Oracle數(shù)據(jù)庫中的數(shù)據(jù)是絕對(duì)要保密的，下文中就為大家介紹Oracle數(shù)據(jù)庫的約束問題。
Oracle 有如下類型的約束:
NOT NULL
UNIQUE Key
PRIMARY KEY
FOREIGN KEY
CHECK
Oracle使用SYS_Cn格式命名約束.
創(chuàng)建約束:
在建表的同時(shí)創(chuàng)建
建表后創(chuàng)建
可定義列級(jí)或表級(jí)約束.
可通過數(shù)據(jù)字典表查看約束.
建表時(shí)創(chuàng)建約束
create table OTL_NICOTINE_GRADE ( ID NUMBER not null, SEASON_NO NUMBER(4) not null, RECEIPT_NO NUMBER(8) not null,
GRADE VARCHAR2(10) not null, PROPORTION NUMBER(5,2) not null, WEIGHT NUMBER(10,2) not null, VALUE NUMBER(12,2) not
null, constraint PK_OTL_NICOTINE_GRADE primary key (ID, GRADE), constraint FK_OTL_NICO_REFERENCE_OTL_CHEC foreign
key (ID) references OTL_CHECK_CHEM (ID) )
建表后添加約束
ALTER TABLE (table_name) ADD ( CONSTRAINT (foreign key constraint name) FOREIGN KEY (field name ) REFERENCES
primary_table_name ( primary_table_primary_index_field )
SQL> create table emplyees(
2 employee_id number(6),
3 last_name varchar2(25) not null,
4 salary number(8,2),
5 commission_pct number(2,2),
6 hire_date date,
7 constraint emp_hire_date_1 not null,
8 CONSTRAINT dept_dname_uk UNIQUE(emp_name)
9 );
表已創(chuàng)建。
SQL>
1 select constraint_name,table_name
2 from dba_constraints
3 where table_name='EMPLYEES'
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C003012 EMPLYEES
EMP_HIRE_DATE_1 EMPLYEES
定義主鍵約束PRIMARY KEY
SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE(dname),
6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));
外鍵約束FOREIGN KEY
SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(7,2) NOT NULL,
10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11 REFERENCES dept (deptno));
外鍵約束的關(guān)鍵字
FOREIGN KEY
定義子表的哪一列作為外鍵約束
REFERENCES
指示主表和參照的列
ON DELETE CASCADE
刪除主表記錄時(shí)將子表相關(guān)記錄刪除
#p#
ON DELETE SET NULL
將外鍵引用置為空值
CHECK 約束
定義每一記錄都要滿足的條件
條件表達(dá)式不允許有:
CURRVAL, NEXTVAL, LEVEL, ROWNUM
SYSDATE, UID, USER, USERENV 函數(shù)
參照其他記錄的值
..., deptno NUMBER(2),
CONSTRAINT emp_deptno_ck
CHECK (DEPTNO BETWEEN 10 AND 99),...
加約束
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
可加或刪除約束，但不能修改
可使約束生效和失效
使用MODIFY子句可加 NOT NULL約束
加 FOREIGN KEY 約束到EMP表
SQL> ALTER TABLE emp
2 ADD CONSTRAINT emp_mgr_fk
3 FOREIGN KEY(mgr) REFERENCES emp(empno);
刪除約束
刪除約束emp_mgr_fk
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_mgr_fk;
刪除主鍵約束和相關(guān)的外鍵約束
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
使約束失效
在ALTER TABLE 語句中執(zhí)行DISABLE子句可使完整性約束失效
使用 CASCADE 選項(xiàng)可使依賴的完整約束失效
SQL> ALTER TABLE emp
2 DISABLE CONSTRAINT emp_empno_pk CASCADE;
使用ENABLE子句將失效的約束生效
SQL> ALTER TABLE emp
2 ENABLE CONSTRAINT emp_empno_pk;
當(dāng)使UNIQUE 或 PRIMARY KEY約束生效時(shí),會(huì)自動(dòng)創(chuàng)建 UNIQUE 或PRIMARY KEY 索引.
延遲約束驗(yàn)證
ALTER TABLE AAA ADD (CONSTRAINT AAA_PK PRIMARY KEY(a) DEFERRABLE) ;
ALTER TABLE BBB
ADD (CONSTRAINT BBB_FK FOREIGN KEY(a)
REFERENCES AAA(a)
ON DELETE CASCADE DEFERRABLE)
CREATE OR REPLACE TRIGGER ID_TRG AFTER UPDATE ON AAA FOR EACH ROW
BEGIN
UPDATE BBB SET a=:NEW.a WHERE a=:OLD.a;
END;
查看約束
通過查看 USER_CONSTRAINTS 表可得到用戶的所有約束.
SQL> SELECT constraint_name, constraint_type,
2 sarch_condition
3 FROM user_constraints
4 WHERE table_name = 'EMP';
CONSTRAINT_NAME C SEARCH_CONDITION
SYS_C00674 C EMPNO IS NOT NULL
SYS_C00675 C DEPTNO IS NOT NULL
EMP_EMPNO_PK P
...
查看約束建立在哪些列
通過查詢USER_CONS_COLUMNS 視圖可獲得約束建立在哪些列上
SQL> SELECT constraint_name, column_name
2 FROM user_cons_columns
3 WHERE table_name = 'EMP';
CONSTRAINT_NAME COLUMN_NAME
------------------------- ----------------------
EMP_DEPTNO_FK DEPTNO
EMP_EMPNO_PK EMPNO
EMP_MGR_FK MGR
SYS_C00674 EMPNO
SYS_C00675 DEPTNO
------------------------- ----------------------
constraint_type
約束的類型有如下幾種：
C (check constraint on a table)
P (primary key)
U (unique key)
R (Referential AKA Foreign Key)
V (with check option, on a view)
O (with read only, on a view)
【編輯推薦】
