Học Oracle cơ bản (Bài 11 )

Thªm c¸c Constraint Néi dung M« t¶ c¸c constraint T¹o vµ b¶o tr× c¸c constraint Constraint lµ g×? C¸c constraint thiÕt lËp nguyªn t¾c møc b¶ng. Constraint ng¨n viÖc xo¸ c¸c b¶ng nÕu cã c¸c b¶ng phô thuéc Sau ®©y lµ c¸c kiÓu constraint trong Oracle: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK Mét sè h­íng dÉn Constraint Tªn cña constraint cã thÓ do hÖ thèng sinh ra theo ®Þnh d¹ng SYS_Cn. T¹o mét constraint: Vµo lóc b¶ng ®­îc t¹o Sau khi b¶ng ®­îc t¹o §Þnh nghÜa constraint ë møc b¶ng hay cét. Xem th«ng tin vÒ constraint trong data dictionary. §Þnh nghÜa Constraint §Þnh nghÜa Constraint Constraint møc cét Constraint møc b¶ng column [CONSTRAINT constraint_name] constraint_type, column,... [CONSTRAINT constraint_name] constraint_type (column, ...), NOT NULL Constraint §¶m b¶o kh«ng cho gi¸ trÞ null trªn cét EMP EMPNO ENAME JOB ... COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ... NOT NULL Constraint §Þnh nghÜa constraint ë møc cét 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); UNIQUE Key Constraint DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON UNIQUE key constraint UNIQUE Key Constraint §Þnh nghÜa ë møc b¶ng hay møc cét SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13), 5 CONSTRAINT dept_dname_uk UNIQUE(dname)); PRIMARY KEY Constraint DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON PRIMARY KEY PRIMARY KEY Constraint §Þnh nghÜa ë møc b¶ng hay møc cét 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 Constraint DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS ... PRIMARYKEY EMP EMPNO ENAME JOB ... COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 ... FOREIGNKEY FOREIGN KEY Constraint §Þnh nghÜa ë møc b¶ng hay møc cét 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)); Tõ kho¸ FOREIGN KEY Constraint  FOREIGN KEY §Þnh nghÜa cét trong b¶ng con ë møc b¶ng REFERENCES §Þnh nghÜa cét trong b¶ng cha ON DELETE CASCADE Cho phÐp qu¸ tr×nh xo¸ c¸c hµng trªn b¶ng cha ®ång thêi xo¸ c¸c hµng trªn b¶ng con CHECK Constraint §Þnh nghÜa ®iÒu kiÖn mµ mçi cét cÇn tho¶ m·n C¸c m« t¶ mµ kh«ng cho phÐp: Tham kh¶o tíi CURRVAL, NEXTVAL, LEVEL vµ to¸n tö gi¶ ROWNUM Gäi tíi c¸c hµm SYSDATE, UID, USER vµ USERENV C¸c query tham chiÕu tíi c¸c gi¸ trÞ trªn cét kh¸c ..., deptno NUMBER(2), CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99),... Thªm mét Constraint Thªm huÆc xo¸ chø kh«ng söa ®­îc mét constraint Enable hay disable constraints Thªm NOT NULL constraint b»ng sö dông mÖnh ®Ò MODIFY ALTER TABLE table ADD [CONSTRAINT constraint] type (column); Thªm mét Constraint Thªm FOREIGN KEY constraint vµo b¶ng EMP ®Ó chØ ®Þnh r»ng mét manager ph¶i tån t¹i nh­ mét employee hîp lÖ trong b¶ng EMP . SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno); Table altered. Xo¸ mét Constraint Xo¸ manager constraint tõ b¶ng EMP . SQL> ALTER TABLE emp 2 DROP CONSTRAINT emp_mgr_fk; Table altered. Xo¸ PRIMARY KEY constraint Tõ b¶ng DEPT vµ xo¸ FOREIGN KEY kÕt hîp víi nã trªn cét EMP.DEPTNO. SQL> ALTER TABLE dept 2 DROP PRIMARY KEY CASCADE; Table altered. Disable c¸c Constraint Thùc thi mÖnh ®Ò disable trong c©u lÖnh alter table ®Ó disable constraint ¸p dông tuú chän CASCADE ®Ó disable c¸c constraint phô thuéc. SQL> ALTER TABLE emp 2 DISABLE CONSTRAINT emp_empno_pk CASCADE; Table altered. Enable c¸c Constraint Sö dông mÖnh ®Ò disable ®Ó disable c¸c constraint  Mét UNIQUE hay PRIMARY KEY index ®­îc tù ®éng t¹o ra nÕu enable UNIQUE key hay PRIMARY KEY constraint. SQL> ALTER TABLE emp 2 ENABLE CONSTRAINT emp_empno_pk; Table altered. Xem th«ng tin Constraints Truy xuÊt USER_CONSTRAINTS table ®Ó xem tÊt c¶ c¸c constraint CONSTRAINT_NAME C SEARCH_CONDITION ------------------------ - ------------------------- SYS_C00674 C EMPNO IS NOT NULL SYS_C00675 C DEPTNO IS NOT NULL EMP_EMPNO_PK P ... SQL> SELECT constraint_name, constraint_type, 2 search_condition 3 FROM user_constraints 4 WHERE table_name = 'EMP'; Xem th«ng tin c¸c cét kÕt hîp víi constraint CONSTRAINT_NAME COLUMN_NAME ------------------------- ---------------------- EMP_DEPTNO_FK DEPTNO EMP_EMPNO_PK EMPNO EMP_MGR_FK MGR SYS_C00674 EMPNO SYS_C00675 DEPTNO SQL> SELECT constraint_name, column_name 2 FROM user_cons_columns 3 WHERE table_name = 'EMP'; Truy vÊn th«ng tin tõ view USER_CONS_COLUMNS. Tæng kÕt T¹o c¸c lo¹i constraints: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK Truy vÊn USER_CONSTRAINTS ®Ó xem tÊt c¶ c¸c constraint thuéc vÒ user ®ã