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

Nh÷ng rµng buéc Môc ®Ých Sau bµi häc nµy, b¹n cã thÓ: Miªu t¶ c¸c rµng buéc T¹o vµ duy tr× c¸c rµng buéc Rµng buéc lµ g×? Ðp buéc mét sè quy ®Þnh ë møc b¶ng vµ cét. Ng¨n xo¸ b¶ng nÕu cã sù phô thuéc Nh÷ng d¹ng rµng buéc cã gi¸ trÞ trong Oracle: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK H­íng dÉn vÒ rµng buéc §Æt tªn rµng buéc hoÆc Oracle Server sÏ sinh tªn nhê sö dông ®Þnh d¹ng: SYS_ Cn. T¹o rµng buéc: Cïng lóc khi t¹o b¶ng. Sau khi t¹o b¶ng. X¸c ®Þnh rµng buéc ë møc cét hoÆc b¶ng. Xem rµng buéc trong tõ ®iÓn d÷ liÖu. X¸c ®Þnh rµng buéc X¸c ®Þnh rµng buéc Møc cét Møc b¶ng column [CONSTRAINT constraint_name] constraint_type, column,... [CONSTRAINT constraint_name] constraint_type (column, ...), Rµng buéc NOT NULL §¶m b¶o cét kh«ng cã gi¸ trÞ NULL. EMP EMPNO ENAME JOB ... COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ... Rµng buéc NOT NULL X¸c ®Þnh ë 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); Rµng buéc UNIQUE DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Rµng buéc UNIQUE Rµng buéc UNIQUE X¸c ®Þnh t¹i møc b¶ng hoÆ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)); Rµng buéc PRIMARY KEY DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON PRIMARY KEY Rµng buéc PRIMARY KEY X¸c ®Þnh møc b¶ng hoÆ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)); Rµng buéc FOREIGN KEY 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 Rµng buéc FOREIGN KEY X¸c ®Þnh t¹i møc b¶ng hoÆ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¸ cña rµng buéc FOREIGN KEY FOREIGN KEY X¸c ®Þnh cét ë b¶ng con t¹i møc b¶ng. REFERENCES ChØ b¶ng vµ cét trong b¶ng cha. ON DELETE CASCADE Cho phÐp xo¸ ë b¶ng cha vµ xo¸ c¸c hµng phô thuéc ë b¶ng con. Rµng buéc CHECK X¸c ®Þnh ®iÒu kiÖn mµ mçi hµng ph¶i ®¸p øng Kh«ng sö dông biÓu thøc: Tham chiÕu ®Õn CURRVAL, NEXTVAL, LEVEL, Vµ ROWNUM. Gäi ®Õn hµm SYSDATE, UID, USERENV. C©u truy vÊn h­íng ®Õn c¸c gi¸ trÞ kh¸c trong b¶ng kh¸c. ..., deptno NUMBER(2), CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99),... Thªm rµng buéc Thªm (add) hoÆc bá (drop), kh«ng thay ®æi. V« hiÖu (disable) rµng buéc hoÆc lµm (enable) rµng buéc cã hiÖu lùc. Thªm rµng buéc NOT NULL nhê lÖnh MODIFY. ALTER TABLE table ADD [CONSTRAINT constraint] type (column); Thªm rµng buéc Thªm rµng buéc FOREIGN KEY cho b¶ng EMP. SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno); Table altered. Bá rµng buéc Bá rµng buéc tõ b¶ng EMP. SQL> ALTER TABLE emp 2 DROP CONSTRAINT emp_mgr_fk; Table altered. Bá rµng buéc PRIMARY KEY vµ FOREIGN KEY liªn quan. SQL> ALTER TABLE dept 2 DROP PRIMARY KEY CASCADE; Table altered. V« hiÖu rµng buéc Thùc hiÖn lÖnh DISABLE trong c©u ALTER TABLE. ¸p dông CASCADE ®Ó v« hiÖu nh÷ng rµng buéc phô thuéc. SQL> ALTER TABLE emp 2 DISABLE CONSTRAINT emp_empno_pk CASCADE; Table altered. Lµm cho rµng buéc cã hiÖu lùc LÖnh ENABLE ®Ó rµng buéc cã hiÖu lùc.  Danh môc (index) cho UNIQUE vµ PRIMARY KEY ®­îc tù ®éng t¹o ra nÕu enable rµng buéc UNIQUE vµ PRIMARY KEY . SQL> ALTER TABLE emp 2 ENABLE CONSTRAINT emp_empno_pk; Table altered. Xem rµng buéc Truy vÊn b¶ng USER_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'; HiÖn cét liªn quan rµng buéc 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'; Sö dông view USER_CONS_COLUMNS Tãm t¾t Miªu t¶ vµ t¹o c¸c d¹ng rµng buéc sau: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK Truy vÊn b¶ng USER_CONSTRAINTS ®Ó xem tªn vµ ®Þnh nghÜa rµng buéc. Thùc hµnh Thªm rµng buéc cho b¶ng ®ang cã. Thªm cét cho b¶ng. Xem th«ng tin trong tõ ®iÓn d÷ liÖu.