728x90

오라클은 종속된 테이블의 삭제를 방지하고 테이블에 유효하지 않은 데이타가 입력되는 것을 방지하기 위하여 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

728x90

'Programming > DB매니아' 카테고리의 다른 글

MySQL 예약어  (0) 2008.02.26
728x90
ADD ALL ALTER
ANALYZE AND AS
ASC ASENSITIVE BEFORE
BETWEEN BIGINT BINARY
BLOB BOTH BY
CALL CASCADE CASE
CHANGE CHAR CHARACTER
CHECK COLLATE COLUMN
CONDITION CONNECTION CONSTRAINT
CONTINUE CONVERT CREATE
CROSS CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURRENT_USER CURSOR
DATABASE DATABASES DAY_HOUR
DAY_MICROSECOND DAY_MINUTE DAY_SECOND
DEC DECIMAL DECLARE
DEFAULT DELAYED DELETE
DESC DESCRIBE DETERMINISTIC
DISTINCT DISTINCTROW DIV
DOUBLE DROP DUAL
EACH ELSE ELSEIF
ENCLOSED ESCAPED EXISTS
EXIT EXPLAIN FALSE
FETCH FLOAT FLOAT4
FLOAT8 FOR FORCE
FOREIGN FROM FULLTEXT
GRANT GROUP HAVING
HIGH_PRIORITY HOUR_MICROSECOND HOUR_MINUTE
HOUR_SECOND IF IGNORE
IN INDEX INFILE
INNER INOUT INSENSITIVE
INSERT INT INT1
INT2 INT3 INT4
INT8 INTEGER INTERVAL
INTO IS ITERATE
JOIN KEY KEYS
KILL LEADING LEAVE
LEFT LIKE LIMIT
LINES LOAD LOCALTIME
LOCALTIMESTAMP LOCK LONG
LONGBLOB LONGTEXT LOOP
LOW_PRIORITY MATCH MEDIUMBLOB
MEDIUMINT MEDIUMTEXT MIDDLEINT
MINUTE_MICROSECOND MINUTE_SECOND MOD
MODIFIES NATURAL NOT
NO_WRITE_TO_BINLOG NULL NUMERIC
ON OPTIMIZE OPTION
OPTIONALLY OR ORDER
OUT OUTER OUTFILE
PRECISION PRIMARY PROCEDURE
PURGE RAID0 READ
READS REAL REFERENCES
REGEXP RELEASE RENAME
REPEAT REPLACE REQUIRE
RESTRICT RETURN REVOKE
RIGHT RLIKE SCHEMA
SCHEMAS SECOND_MICROSECOND SELECT
SENSITIVE SEPARATOR SET
SHOW SMALLINT SONAME
SPATIAL SPECIFIC SQL
SQLEXCEPTION SQLSTATE SQLWARNING
SQL_BIG_RESULT SQL_CALC_FOUND_ROWS SQL_SMALL_RESULT
SSL STARTING STRAIGHT_JOIN
TABLE TERMINATED THEN
TINYBLOB TINYINT TINYTEXT
TO TRAILING TRIGGER
TRUE UNDO UNION
UNIQUE UNLOCK UNSIGNED
UPDATE USAGE USE
USING UTC_DATE UTC_TIME
UTC_TIMESTAMP VALUES VARBINARY
VARCHAR VARCHARACTER VARYING
WHEN WHERE WHILE
WITH WRITE X509
XOR YEAR_MONTH ZEROFILL
728x90

'Programming > DB매니아' 카테고리의 다른 글

[오라클]오라클의 Constraint(제약조건)  (0) 2008.04.09

+ Recent posts