Oracle約束
導讀：什么是約束?約束是表級強制執行的規則，當表中數據有相互依賴性時，可保護數據不被刪除。Oracle數據庫是一種功能性很強大的數據庫系統，Oracle數據庫中的數據是絕對要保密的，下文中就為大家介紹Oracle數據庫的約束問題。
Oracle 有如下類型的約束:
NOT NULL
UNIQUE Key
PRIMARY KEY
FOREIGN KEY
CHECK
Oracle使用SYS_Cn格式命名約束.
創建約束:
在建表的同時創建
建表后創建
可定義列級或表級約束.
可通過數據字典表查看約束.
建表時創建約束
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 );
表已創建。
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));
外鍵約束的關鍵字
FOREIGN KEY
定義子表的哪一列作為外鍵約束
REFERENCES
指示主表和參照的列
ON DELETE CASCADE
刪除主表記錄時將子表相關記錄刪除
#p#
ON DELETE SET NULL
將外鍵引用置為空值
CHECK 約束
定義每一記錄都要滿足的條件
條件表達式不允許有:
CURRVAL, NEXTVAL, LEVEL, ROWNUM
SYSDATE, UID, USER, USERENV 函數
參照其他記錄的值
..., 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;
刪除主鍵約束和相關的外鍵約束
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
使約束失效
在ALTER TABLE 語句中執行DISABLE子句可使完整性約束失效
使用 CASCADE 選項可使依賴的完整約束失效
SQL> ALTER TABLE emp
2 DISABLE CONSTRAINT emp_empno_pk CASCADE;
使用ENABLE子句將失效的約束生效
SQL> ALTER TABLE emp
2 ENABLE CONSTRAINT emp_empno_pk;
當使UNIQUE 或 PRIMARY KEY約束生效時,會自動創建 UNIQUE 或PRIMARY KEY 索引.
延遲約束驗證
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)
【編輯推薦】
