iWiz ShareBase

IT Specialist À±ÅÂÇöÀÇ iWiz ShareBase´Â IT»Ó ¾Æ´Ï¶ó °¢Á¾ Àâ´ÙÇÑ Áö½ÄµéÀ» ÇÔ²² ³ª´©´Â Áö½Ä°øÀ¯ Ä¿¹Â´ÏƼÀÔ´Ï´Ù.

iWiz,ShareBase,À±ÅÂÇö,Java,JSP,EJB,IT,Á¤º¸±â¼ú,À¥ÇÁ·Î±×·¡¹Ö,PHP,ASP,DBMS,MySQL,¼­¹ö,³×Æ®¿öÅ©,server,network,WAS,À¥¾ÖÇø®ÄÉÀ̼Ç,ºí·Î±×,blog,À¥¼­¹ö,DB,¿À¶óŬ,oracle,mysql,JRun,À¥·ÎÁ÷,ÅèĹ,tomcat,¾ÆÆÄÄ¡,ÀÚµ¿Â÷,EF½î³ªÅ¸,·Î¶Ç 6/45

°¶·¯¸® Pixelgrapher.com | ·Î¶Ç 6/45 ¹øÈ£»ý¼º ¹× Åë°è µ¥ÀÌÅÍ | Àüü±â»çº¸±â | Àüü±Û #1 | Àüü±Û #2 | Àüü±Û #3 | Àüü±Û #4 | Àüü±Û #5 | Àüü±Û #6 | Àüü±Û #7 | Àüü±Û #8 | Àüü±Û #9 | Àüü±Û #10 |
HOME iWiz
ShareBase
Remember 0523 & 0818
Áö½ÄÀº ³ª´­¼ö·Ï Ä¿Áý´Ï´Ù - iWiz's ShareBase
µ¥ÀÌÅͺ£À̽º µ¥ÀÌÅͺ£À̽º(DBMS)¿¡ °ü·ÃµÈ °¢Á¾ ÀÚ·áµéÀÔ´Ï´Ù.


  ¹ÚÁã(2004-03-26 15:08:25, Hit : 37575, Vote : 38

¿À¶óŬÀÇ Constraint(Á¦¾àÁ¶°Ç)


¿À¶óŬÀº Á¾¼ÓµÈ Å×À̺íÀÇ »èÁ¦¸¦ ¹æÁöÇÏ°í Å×ÀÌºí¿¡ À¯È¿ÇÏÁö ¾ÊÀº µ¥ÀÌŸ°¡ ÀԷµǴ °ÍÀ» ¹æÁöÇϱâ À§ÇÏ¿© constraint¸¦ »ç¿ëÇÕ´Ï´Ù.

 

1. constraint Áöħ

    1) Á¦¾àÁ¶°Ç¿¡ À̸§À» ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é Oracle server°¡ SYS_CnÀÇ Çü½ÄÀ¸·Î ÀÚµ¿À¸·Î À̸§À» »ý¼ºÇÕ´Ï´Ù.

    2) Á¦¾àÁ¶°ÇÀº Å©°Ô Å×ÀÌºí ·¹º§°ú ¿­ ·¹º§·Î Á¤ÀÇÇÒ ¼ö ÀÖ½À´Ï´Ù.

 

2. constraint Á¤ÀÇ ¿¹Á¦

    SQL> CREATE TABLE EXAMPLE(

                ID               NUMBER(6),

                NAME         VARCHAR2(20)    [CONSTRAINT NAME_CTR] NOT NULL,     

                                                                     ----> ¿­ ·¹º§(Á¦¾àÁ¶°Ç À̸§À» »ý·«ÇÏ¸é ½Ã½ºÅÛÀÌ ÀÚµ¿À¸·Î À̸§ »ý¼º

                ...

                

                CONSTRAINT EXAMPLE_ID_PK PRIMARY KEY(ID));                           

                                                                     ----> Å×ÀÌºí ·¹º§(Á¦¾àÁ¶°Ç À̸§ÀÌ EXAMPLE_ID_PK·Î »ý¼º)

 

3. constraint À¯Çü

    1) NOT NULL : ÇØ´ç¿­¿¡ ³Î°ªÀÌ ¾øµµ·Ï Çϱâ À§ÇÑ Á¦¾àÁ¶°Ç

    2) UNIQUE : ÁöÁ¤µÈ ¿­¿¡ ´ëÇØ ³Î°ªÀº Çã¿ëÇϳª, µ¿ÀÏÇÑ °ªÀº Çã¿ëµÇÁö ¾Ê´Â Á¦¾àÁ¶°Ç

                       ¿À¶óŬ ¼­¹ö´Â ÁöÁ¤µÈ ¿­¿¡ ´ëÇØ ÀÚµ¿À¸·Î À妽º¸¦ »ý¼º

                       [¿¹Á¦] SQL> ...CONSTRAINT EXAMPLE_ID_UK UNIQUE(ID)

    3) PRIMARY KEY : Å×À̺íÀÇ °¢ ÇàÀ» ½Äº°Çϱâ À§ÇØ Å×À̺í´ç ÇϳªÀÇ ±âº»Å°¸¦ »ý¼º

                                Áߺ¹µÈ °ª°ú ³Î°ªÀ» °¡Áú ¼ö ¾ø´Ù

                                ¿À¶óŬ ¼­¹ö´Â ÁöÁ¤µÈ ¿­¿¡ ´ëÇØ ÀÚµ¿À¸·Î À妽º¸¦ »ý¼º

    4) FOREIGN KEY : µ¿ÀÏÇÑ Å×ÀÌºí ¶Ç´Â ´Ù¸¥ Å×ÀÌºí¿¡¼­ ±âº»Å° ¶Ç´Â °íÀ¯Å°¸¦ ÂüÁ¶ÇÏ´Â Á¦¾àÁ¶°Ç

                                ºÎ¸ð Å×À̺íÀÇ °ª°ú ÀÏÄ¡Çϰųª ³Î°ªÀ» °¡Á®¾ß ÇÑ´Ù.

                                ON DELETE CASCADE : ºÎ¸ð Å×À̺íÀÇ ÇàÀÌ »èÁ¦µÇ´Â °æ¿ì ÀÚ½Ä Å×À̺íÀÇ Á¾¼Ó ÇàÀ» »èÁ¦

                                ON DELETE SET NULL : ºÎ¸ð Å×À̺íÀÇ ÇàÀÌ »èÁ¦µÇ´Â °æ¿ì Á¾¼Ó ¿Ü·¡Å° °ªÀ» ³Î·Î º¯È¯

                                [¿¹Á¦] ...CONSTRAINT EXAMPLE_ID_FK FOREIGN KEY(ID) REFERENCES DEPT(ID)

    5) CHECK : °¢ ÇàÀÌ ¸¸Á·½ÃÄÑ¾ß ÇÏ´Â Á¶°ÇÀ» Á¤ÀÇ

                      [¿¹Á¦] SAL       NUMBER(10)    CONSTRAINT EXAMPLE_SAL_CK

                                                                      CHECK(SAL > 1000)

 

4. constraint¸¦ Á¶È¸ÇÏ´Â µ¥ÀÌŸ µñ¼Å³Ê¸®

    1) USER_CONSTRAINTS

        Å×ÀÌºí »ý¼º ÈÄ DESC·Î Å×ÀÌºí ±¸Á¶¸¦ º¸¸é NOT NULLÁ¦¾àÁ¶°Ç¸¸ º¸ÀÌ°í, ´Ù¸¥ Á¦¾à Á¶°ÇÀº º¸ÀÌÁö ¾Ê°Ô µÈ´Ù.

        À̶§ ´Ù¸¥ Á¦¾àÁ¶°ÇÀ» º¸±â À§ÇØ USER_CONSTRAINTS¶ó´Â µ¥ÀÌŸ µñ¼Å³Ê¸®°¡ Á¦°øµÈ´Ù.

        ¿©·¯ ÇʵåÁß CONSTRAINT_TYPEÀ̶ó´Â Çʵ常 »ìÆ캸µµ·Ï ÇÏ°Ú´Ù. ³ª¸ÓÁö´Â ½±°Ô ÀÌÇØ°¡ µÉ °ÍÀÌ´Ù.

        P : PRIMARY KEY, R : FOREIGN KEY, C : CHECK ¶Ç´Â NOT NULL, U : UNIQUE

    2) USER_CONS_COLUMNS

        Á¦¾à Á¶°Ç À̸§°ú ¿¬°üµÈ Ä÷³À» º¼ ¼ö ÀÖ°Ô ÇÏ´Â µ¥ÀÌŸ µñ¼Å³Ê¸®

 

5.  constraint Ãß°¡ / »èÁ¦

    1) constraint Ãß°¡

        EMP_TESTÅ×À̺íÀÇ DEPT_IDÇʵ尡  DETP_TESTÀÇ ID°ªÀ» ÂüÁ¶ÇÏ´Â Á¦¾àÁ¶°ÇÀ» Ãß°¡ÇÏ´Â ¿¹¹®

        SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS

                 WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                 TABLE_NAME                     CONSTRAINT_NAME

                 ------------------------------ -------------

                 EMP_TEST                         EMP_TEST_PK

 

        1) DETP_TESTÅ×À̺íÀÇ IDÇʵ带 PRIMARY KEY°ªÀ¸·Î ¼³Á¤

 

 

           SQL> ALTER TABLE DETP_TEST

                    ADD CONSTRAINT DETP_TEST_PK PRIMARY KEY(ID);

                    Table altered.

        2) EMP_TESTÅ×À̺íÀÇ DEPT_IDÇʵ带 DETP_TESTÀÇ ID°ªÀ» ÂüÁ¶ÇÏ´Â FOREIGN KEY°ªÀ¸·Î ¼³Á¤

                    ALTER TABLE EMP_TEST

                    ADD CONSTRAINT EMP_TEST_FK FOREIGN KEY(DEPT_ID) REFERENCES DETP_TEST(ID);

                    Table altered.

        3) constraint Ãß°¡ ¿©ºÎ È®ÀÎ

            SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS

                     WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                     TABLE_NAME                     CONSTRAINT_NAME

                     ------------------------------ ------------------------------

                     DETP_TEST                      DETP_TEST_PK

                     EMP_TEST                        EMP_TEST_FK

                     EMP_TEST                        EMP_TEST_PK

 

    2) constraint »èÁ¦

        EMP_TESTÅ×À̺íÀÇ DEPT_ID(FOREIGN KEY)ÇʵåÀÇ Á¦¾àÁ¶°ÇÀ» »èÁ¦

        SQL> ALTER TABLE EMP_TEST

                 DROP CONSTRAINT EMP_TEST_FK;

                 Table altered.

       

        SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS

                 WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                 TABLE_NAME                     CONSTRAINT_NAME

                 ------------------------------ ------------------------------

                 DETP_TEST                       DETP_TEST_PK

                 EMP_TEST                        EMP_TEST_PK

 

        DETP_TESTÅ×À̺íÀÇ IDÇʵåÀÇ Á¦¾àÁ¶°Ç(PRIMARY KEY)À» »èÁ¦

        SQL> ALTER TABLE DETP_TEST

                 DROP PRIMARY KEY CASCADE;     ----> CASCADE¸¦ ºÙÀ̸é FOREIGN KEY±îÁö µ¿½Ã¿¡ »èÁ¦°¡ µÊ

                 Table altered.

 

        SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS

                 WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST'); 

                 TABLE_NAME                     CONSTRAINT_NAME

                 ------------------------------ ------------------------------

                 EMP_TEST                         EMP_TEST_PK

 

6. constraintÀÇ enable/disable : Á¦¾àÁ¶°ÇÀ» »èÁ¦/»ý¼ºÇÏÁö ¾Ê°íµµ Á¦¾àÁ¶°ÇÀ» ºñÈ°¼ºÈ­ ÇÒ ¼ö ÀÖ´Ù.

    1) constraintÀÇ disable

        DETP_TESTÅ×À̺íÀÇ IDÇʵåÀÇ Á¦¾àÁ¶°Ç(PRIMARY KEY)À» disable

        SQL> ALTER TABLE DETP_TEST

                 DISABLE CONSTRAINT DETP_TEST CASCADE; ---> CASCADE´Â FOREIGN KEY±îÁö µ¿½Ã¿¡ disableµÊ

                 Table altered.

 

        SQL> SELECT TABLE_NAME, STATUS FROM USER_CONSTRAINTS

                 WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                 TABLE_NAME                     STATUS

                 ------------------------------ --------

                 DETP_TEST                      DISABLED

                 EMP_TEST                       DISABLED

                 EMP_TEST                       ENABLED

 

    2) constraintÀÇ enable

        disableµÈ Á¦¾àÁ¶°ÇÀ» enable½ÃÅ°±â À§Çؼ­´Â PRIMARY KEY¸¦ enable½ÃŲ ÈÄ, FOREIGN KEY¸¦ enable ½ÃÄÑ

        ¾ß ÇÑ´Ù.l

        SQL> ALTER TABLE DETP_TEST

                 ENABLE CONSTRAINT DETP_TEST;

                 Table altered.

        SQL> ALTER TABLE EMP_TEST

                  ENABLE CONSTRAINT EMP_TEST_FK;

                  Table altered.

        SQL> SELECT TABLE_NAME, STATUS FROM USER_CONSTRAINTS

                  WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                  TABLE_NAME                     STATUS   

                  ------------------------------ --------

                  DETP_TEST                      ENABLED

                  EMP_TEST                       ENABLED

                  EMP_TEST                       ENABLED

 

        

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

* iWiz´Ô¿¡ ÀÇÇؼ­ °Ô½Ã¹° À̵¿µÇ¾ú½À´Ï´Ù (2010-02-03 17:06)



22   ¿À¶óŬ ¹«·á °ü¸® ÅøÀÌ À־ ¼Ò°³ ÇÕ´Ï´Ù. [1]  ±è±â¿µ 2007/04/28 8292 0
21   ¿Ü¿öµµ ¿Ü¿öµµ ±î¸Ô´Â ¿À¶óŬ ÇÔ¼ö (¹Ø¿¡²¨ ÆÛ°¡´Â ´ë½Å ¤¾) [1]  ÀÌÁØÈ£ 2004/07/15 11955 43
20   Default Temporary Tablespace ¼öÁ¤  ¹ÚÁã 2004/05/03 8966 44
19   ¸®µÎ·Î±× ÆÄÀÏÀÇ drop / add / relocation ¹× rename  ¹ÚÁã 2004/04/22 7059 33
18   ¿À¶óŬ ±âº» ¸í·É¾î Á¤¸® [2]  iWiz 2004/04/07 9790 42
17   ¿À¶óŬÀÇ Undo Segment [3]  ¹ÚÁã 2004/04/06 9090 38
16   ER Diagram ÀÛ¼º ÇÁ·Î±×·¥ ERWin »ç¿ë ¹æ¹ý  iWiz 2004/04/04 13143 38
15   import À¯Æ¿¸®Æ¼ [3]  ¹ÚÁã 2004/04/01 6538 35
14   ¼¼¼Ç°ü¸®  ¹ÚÁã 2004/03/31 8165 31
13   LogMiner  ¹ÚÁã 2004/03/31 7341 39
12   ¿À¶óŬ¿¡¼­ÀÇ ¹é¾÷...  ¹ÚÁã 2004/03/30 6336 40
  ¿À¶óŬÀÇ Constraint(Á¦¾àÁ¶°Ç)  ¹ÚÁã 2004/03/26 37575 38
10   Export À¯Æ¿¸®Æ¼ [1]  ¹ÚÁã 2004/03/23 5693 35
9   ºñ±³ / ³í¸® ¿¬»êÀÚ ¹× ¿¬»ê ¿ì¼± ¼øÀ§  ¹ÚÁã 2004/03/22 9336 41
8   NULLÀ̶õ?? [2]  ¹ÚÁã 2004/03/22 7993 32

1 [2]
 

Copyright 1999-2024 Zeroboard / skin by zero
iWiz ShareBase, ¨ÏCopyleft by iWiz.  For more information contact .
º» À¥»çÀÌÆ®¿¡ °Ô½ÃµÈ À̸ÞÀÏ ÁÖ¼Ò°¡ ÀüÀÚ¿ìÆí ¼öÁý ÇÁ·Î±×·¥À̳ª ±× ¹ÛÀÇ ±â¼úÀû ÀåÄ¡¸¦ ÀÌ¿ëÇÏ¿© ¹«´ÜÀ¸·Î ¼öÁýµÇ´Â °ÍÀ» °ÅºÎÇϸç, À̸¦ À§¹Ý½Ã¿¡´Â Á¤º¸Åë½Å¸Á¹ý¿¡ ÀÇÇØ Çü»çó¹úµÊÀ» À¯³äÇϽñ⠹ٶø´Ï´Ù. [°Ô½ÃÀÏ 2004. 1. 31]