반응형
250x250
Notice
Recent Posts
Recent Comments
Link
관리 메뉴

Yeonee's Story

Oracle SQL 요약정리 - 제약조건 CONSTRAINTS 종류 및 표현법(컬럼레벨방식,테이블레벨방식), 제약조건 다양한 활용예시문 본문

카테고리 없음

Oracle SQL 요약정리 - 제약조건 CONSTRAINTS 종류 및 표현법(컬럼레벨방식,테이블레벨방식), 제약조건 다양한 활용예시문

yeonee 여니 2023. 6. 6. 01:09
728x90
반응형
SMALL

안녕하세요.

https://blog.naver.com/sysysy0302여니입니다 :)

 

 

<제약조건 CONSTRAINTS>
    
    - 원하는 데이터값만 유지하기 위해서 (보관하기 위해서) 특정 컬럼마다 설정하는 제약
      (데이터 무결성 보장을 목적으로)
    - 제약조건이 부여된 컬럼에 들어올 데이터에 문제가 있는지 없는지 자동으로 검사할 목적
    
    - 종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY
    
    - 컬럼에 제약조건을 부여하는 방식 : 컬럼레벨방식 / 테이블레벨방식

 

--> 컬럼레벨방식 : 컬럼명 자료형 제약조건 => 제약조건을 부여하고자하는 컬럼 뒤에 곧바로 기술하는 방법

--> 테이블 레벨방식 : 모든 칼럼을 다 기술하고, 그 이후에 제약조건을 나열

 

 


 

1. NOT NULL 제약조건
        해당 컬럼에 반드시 값이 존재해야만 할 경우 사용
        => 즉 NULL값이 절대 들어와서는 안되는 컬럼에 부여하는 제약조건
           삽입/수정시 NULL값을 허용하지 않도록 제한하는 제약조건
           
      --  컬럼레벨 방식으로만 등록 가능.

 

ex)-- NOT NULL 제약조건을 설정한 테이블 만들기

CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

 

-- 추가할 행에 직접 NOT NULL 제약조건 걸기

INSERT INTO MEM_NOTNULL
VALUES (1, 'user01', 'pass01', '소연', '여', '010-1111-1111', 'thdusthdus@naver.com');

SELECT * FROM MEM_NOTNULL
VALUES (2, 'user01', 'pass01', NULL, '여', '010-1111-1111', 'thdusthdus@naver.com');
-- DDL 계정에 MEM_NOTNULL 테이블에 NOTNULL 제약조건이 부여된 칼럼들에 NULL값이 들어갈수없어서
-- 오류가 발생함.

INSERT INTO MEM_NOTNULL
VALUES (1, 'user01', 'pass01', '소연',NULL,NULL,NULL);

-- NOT NULL 제약조건이 걸리지 않은 칼럼명에는 NULL값이 들어갈 수 있음

 


2. UNIQUE 제약조건
        칼럼에 중복값을 제한하는 제약조건
        삽입/수정시 기존에 추가, 수정이 되지 않게 제약
       
    --  칼럼/ 테이블 레벨방식 둘다 가능

 

ex)

CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, --컬럼레벨방식
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)

    UNIQUE(MEM_ID) -- 테이블 레벨 방식
);

 

INSERT INTO MEM_UNIQUE
VALUES(1,'user02','pass01','thdus','ksy','010-1111-1111','thdusthdus@naver.com');

/*
    UNIQUE 제약조건에 위배되었으므로 INSERT실패
    어느컬럼에 어느 문제가 있는지 구체적으로 알려주지 않음
    DDL.SYS_C007062 : 제약조건의 이름으로만 제약조건 위배를 알려준다.(스크립트 출력창 확인)
    제약조건 부여시 직접 제약조건명(☆)을 지정해주지 않으면 시스템에서 임의의 제약조건명을 부여해줌.
*/


-- GENDER칼럼에 '남', '여'라는 값만 들어가게 하고 싶음

 

3. CHECK 제약조건
        컬럼에 기록될 수 있는 값에 대한 조건을 설정할 수 있다.
        예 ) 성별 '남' 혹은 '여' 만 들어오게끔 하고 싶다.
        
        [표현법]
        CHECK (조건식) --; 조건식이 TRUE인 경우에만 성립

 

ex)

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE NOT NULL,
    UNIQUE(MEM_ID) --테이블 레벨 방식
); 


INSERT INTO MEM_CHECK
VALUES(1,'user01','pass01','김소연','여',null,null,SYSDATE);

INSERT INTO MEM_CHECK
VALUES(1,'user02','pass01','김소연','연',null,null,SYSDATE);
-- ; CHECK 제약조건을 걸어주었기 때문에 성별값에 제약조건에서걸어준 '남', '여' 아닌 값인 '연'이 들어와서 오류뜸

--INSERT INTO MEM_CHECK
--VALUES(1,'user02','pass01','김소연',NULL,null,null,SYSDATE);

-- 추가적으로 NULL값을 못 들어오게 하고 싶다면 NOT NULL 제약조건도 같이 걸어주면 됨.


 * DEFAULT 설정
    특정 칼럼에 들어올 값에 대한 기본값 설정 가능(제약조건은 아님)
    
    EX) 회원가입일 컬럼에 회원정보가 삽입된 순간의 시간을 기록하고싶다
      => DEFAULT 설정으로 SYSDATE를 넣어주면됨

 

ex) -- 회원가입을 항상 SYSDATE 로 받고 싶은경우

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
    UNIQUE(MEM_ID) --테이블 레벨 방식
); 

 

INSERT INTO MEM_CHECK
VALUES(1,'user01','pass01','김소연','여',null,null,NULL);

INSERT INTO MEM_CHECK(MEM_NO,MEM_ID,MEM_PWD,MEM_NAME)
VALUES(1,'user01','pass01','김소연');

 

SELECT *FROM MEM_CHECK; -- 테이블 확인

==> 출력 : 1 user01 pass01 김소연 (null) (null) (null) 23/06/06 


-- 지정이 안된 칼럼에는 기본적으로 NULL값이 들어간다
-- 만약 DEFAULT값이 부여되어 있다면 NULL값이 아닌 DEFAULT값으로 들어가게 된다.



4. PRIMARY KEY(기본키) 제약조건
        테이블에서 각 행들의 정보를 유일하게 식별할 수 있는 칼럼에 부여하는 제약조건
        -> 각 행들을 구분할 수 있는 식별자의 역할
        예) 사번, 부서아이디, 직급코드, 학생번호, 클래스 번호 ... 
        => 식별자의 조건 : 중복X , 값이 없어도 안됨(NOT NULL + UNIQUE)
        
        주의사항 : 한 테이블당 한개의 칼럼만 지정가능 -- ; UNIQUE는여러개가능

 

ex 1) 

CREATE TABLE MEM_PRIMARYKEY1(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
    UNIQUE(MEM_ID) --테이블 레벨 방식
); 

INSERT INTO MEM_PRIMARYKEY1
1번--VALUES(1,'user01','pass01','김소연',null,null,null,DEFAULT);
2번--VALUES(NULL,'user01','pass01','김소연',null,null,null,DEFAULT);
-- 1번) 1이 중복이라 UNIQUE제약조건에 위반, 2번) NULL값이 오면 안되므로 NOT NULL제약조건 위반

ex 2)

CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER CONSTRAINT MEM_PK2 PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
    UNIQUE(MEM_ID), --테이블 레벨 방식
    CONSTRAINT MEM_PK2 PRIMARY KEY(MEM_NO) -- 테이블레벨방식
); 

CREATE TABLE MEM_PRIMARYKEY2(
    MEM_NO NUMBER ,
    MEM_ID VARCHAR2(20) NOT NULL, --;NOT NULL 뒤에 PRIMARY KEY 추가시 에러남
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
    CONSTRAINT MEM_PK2 PRIMARY KEY(MEM_NO, MEM_ID) -- 테이블레벨방식
); 

-- PRIMARY KEY가 한테이블에 2개이상 사용될수 없다. 단, 두 컬럼을 하나로 묶어서 하나의 PRIMARY KEY로는 설정 가능.
-- 두가지 컬럼을 묶어서 PRIMARY KEY로 설정했을경우 => 복합키 ★의외로 많이 사용됨★

 

 

INSERT INTO MEM_PRIMARYKEY2
VALUES(1,'user01','pass01','김소연',null,null,null,DEFAULT);

INSERT INTO MEM_PRIMARYKEY2
VALUES(1,'user02','pass01','김소연',null,null,null,DEFAULT);

INSERT INTO MEM_PRIMARYKEY2
VALUES(2,'user02','pass01','김소연',null,null,null,DEFAULT);

INSERT INTO MEM_PRIMARYKEY2
VALUES(2,'user02','pass01','김소연',null,null,null,DEFAULT);
-- 복합키의 경우 두 컬럼의 값이 완전히 중복되어야지만 제약조건에 위배된다.

INSERT INTO MEM_PRIMARYKEY2
VALUES(NULL,'user02','pass01','김소연',null,null,null,DEFAULT);
-- 복합키일경우 한 컬럼에 값이 NULL이면 제약조건에 위배된다.

 


5. FOREIGN KEY(외래키)
        해당 컬럼에 다른 테이블에 존재하는 값만 들어와야하는 칼럼에 부여하는 제약조건
      => "다른 테이블(==부모테이블)을 참조한다" 라고 표현
            즉, 참조된 다른 테이블(==부모테이블)이 제공하고 있는 값만 들어올 수 있다.
        예) KH계정에서
        EMPLOYEE테이블(자식테이블) <-------- DEPARTMENT테이블(부모테이블)
                              DEPT_CODE             ------ DEPT_ID
         => DEPT_CODE에는 DEPT_ID에 존재하는 값들만 들어올수 있다.
      => FOREIGN KEY제약조건으로 다른 테이블과 관계를 형성할 수 있다.(JOIN)
      
      [표현법]   ★그냥 암기
      > 컬럼레벨 방식
      컬럼명 자료형 CONSTRAINT 제약조건명 REFERENCES 참조할테이블명(참조할칼럼명)
      
      > 테이블레벨방식
      CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할칼럼명)

      참조할테이블 == 부모테이블
      생략가능한부분 : CONSTRAINT 제약조건명, 참조할 칼럼명(테이블, 칼럼레벨 모두 생략가능)
      => 참조할 칼럼명이 생략되는 경우 자동적으로 참조할 테이블의 PRIMARY KEY에 해당하는 컬럼이 참조컬럼으로 지정됨
      
      주의사항 : 참조할 칼럼의 타입(부모테이블칼럼), 외래키로 지정할 칼럼타입(자식테이블칼럼)이 같아야한다.

 

ex 1)SELECT MEM_ID, GRADE_NAME
FROM MEM
JOIN MEM_GRADE ON GRADE_ID = GRADE_CODE;

-- 문제) 부모테이블에서 데이터값이 삭제된다면?
-- MEM_GRADE테이블에서 GRADE_CODE가 G3인 데이터만 지워보기?
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G3';
-- 자식테이블에서 현재 G3 값을 사용하고 있기 때문에 삭제할 수 없음.
-- 현재 외래키 제약조건 부여시 삭제에 대한 옵션을 별도로 부여하지 않은 상태
-- => 기본값으로 삭제 제한 옵션이 걸려있음

/*
    * 자식테이블 생성시 외래키 제약조건을 부여했을때
    부모테이블의 데이터가 삭제되었을 때 자식테이블에서는 어떻게 처리할지를 옵션으로 정해둘수있다.

    * FOREIGN KEY 삭제 옵션
    - ON DELETE SET NULL : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 NULL로 바꾸겠다.
    - ON DELETE CASCADE  : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 같이 삭제하겠다.
    - ON DELETE RESTRICTED : 삭제를 제한하겠다(기본옵션)
*/

 

ex 2)

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2),
--    GRADE_ID CHAR(2) REFERENCES MEM_GRADE, --외래키 제약조건 (컬럼레벨)
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL,
    UNIQUE(MEM_ID),
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL--외래키 제약조건 (테이블레벨)
);

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';

-- 문제없이 삭제잘되고, G1을 참조하던 자식 테이블의 GRADE_ID 값에 G1대신 NULL값이 들어갔다.

 

ex 3)

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2),
--    GRADE_ID CHAR(2) REFERENCES MEM_GRADE, --외래키 제약조건 (컬럼레벨)
    GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL,
    UNIQUE(MEM_ID),
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE --외래키 제약조건 (테이블레벨)
);

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G2';

-- 문제없이 잘 삭제되었고, 자식테이블의 GRADE_ID 가 G2인 행까지 함께 삭제해버림

 

ex 4) -- 조인문제
-- 전체 회원의 회원번호, 아이디, 비밀번호, 이름, 등급명을 오라클전용구문, ANSI 구문으로 나눠서 작성
-- ANSI
SELECT MEM_NO 회원번호, MEM_ID 아이디, MEM_PWD 비밀번호, MEM_NAME 이름, GRADE_ID 등급명
FROM MEM
JOIN MEM_GRADE ON GRADE_ID = GRADE_CODE;
-- 오라클
SELECT MEM_NO 회원번호, MEM_ID 아이디, MEM_PWD 비밀번호, MEM_NAME 이름, GRADE_ID 등급명
FROM MEM, MEM_GRADE
WHERE GARADE_ID = GRADE_CODE;

/*
    외래키 제약조건이 걸려있지 않더라도 JOIN은 가능함. 단, 두 컬럼의 동일한 의미의 데이터가
    담겨있어야 정상적으로 조회 가능함.
*/


제약조건 부여시 제약조건명도 지정하는 표기법
    
    > 컬럼레벨방식
    CREATE TABLE 테이블명(
        컬럼명 자료형 제약조건1 제약조건2,
        컬럼명 자료형 CONSTRAINT 제약조건명 제약조건,
        ...
    );
    
    > 테이블레벨방식
    CREATE TABLE 테이블명(
        컬럼명 자료형, 
        ...
        
        [CONSTRAINT 제약조건] 제약조건 (칼럼명)
    );
    => 두방식 모두 CONSTRAINT 제약조건이름은 생략가능했었음(시스템이 랜덤한 이름을 부여해줌)

 

ex)

CREATE TABLE MEM_CON_NM( 
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) CONSTRAINT MEM_NAME_NN NOT NULL, -- 컬럼레벨방식 제약조건명 부여
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    CONSTRAINT MEM_ID_UQ UNIQUE(MEM_ID) --테이블 레벨 방식
); 

 

INSERT INTO MEM_CON_NM
VALUES(1,'user01','pass01','김소연',NULL,NULL,NULL);

INSERT INTO MEM_CON_NM
VALUES(2,'user01','pass02','김소연2',NULL,NULL,NULL);
--어떤 컬럼에 어떤 종류의 제약조건인지 한번에 확인가능(오류메세지확인)

INSERT INTO MEM_CON_NM
VALUES(1,'user02','pass02',NULL,NULL,NULL,NULL);


 

 

 

728x90
반응형
LIST