[Oracle] DAY25_데이터 딕셔너리 | 제약조건

제약조건(CONSTRAINTS)

사용자가 원하는 조건의 데이터만 유지하기 위해서 특정 컬럼에 설정하는 제약.
데이터 무결성(중복 / NULL 최소화) 보장을 목적으로 함 

 

✔ 제약 조건 종류 : PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY.

 

제약 조건 확인 

1. USER_CONSTRAINTS: 사용자가 작성한 제약조건을 확인하는 딕셔너리 뷰 

2. USER_CONS_COLUMNS : 제약조건이 걸려 있는 컬럼을 확인하는 딕서너리 뷰 

1.NOT NULL ( NULL  X)

해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용 

삽입 / 수정 시 NULL값을 허용하지 않도록 컬럼레벨에서 제한 

1. 테이블 만들기 
CREATE TABLE USER_USED_MN(
    USER_NO NUMBER NOT NULL, -- 사용자 번호 (무조건 있어야 함==NULL이면 안된다)
                             -- 컬럼 레벨 제약조건 설정 
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

2. 테이블 값 추가 
INSERT INTO USER_USED_MN
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
ㄴ 값추가 가능 

INSERT INTO USER_USED_MN
VALUES(NULL, NULL, NULL, NULL, NULL, '010-1234-5678', 'hong123@kh.or.kr');
ㄴ 값 추가 불가능 USER_NO가 NULL값이면 안된다고 제약 조건을 걸어 NULL이 들어갈 수 없다.

2-1. UNIQUE ( NULL  O)

컬럼에 입력 값에 대해서 중복을 제한하는 제약조건

컬럼레벨에서 설정가능, 테이블 레벨에서 설정 가능

단, UNIQUE 제약조건이 설정된 컬럼에 NULL값은 중복 삽입 가능 

1. 테이블 만들기 
CREATE TABLE USER_USED_UK(
    USER_NO NUMBER,
    --USER_ID VARCHAR2(20) UNIQUE ,--컬럼레벨 제약조건 설정 (제약조건명 미지정)
    --USER_ID VARCHAR2(20) CONSTRAINT USER_ID_U UNIQUE,
                                             --제약 조건명 
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    
    -- 테이블 레벨 제약조건 설정 부분 
    --UNIQUE(USER_ID) --테이블 레벨 제약조건 설정(제약조건명 미지정)
    CONSTRAINT USER_ID_U UNIQUE(USER_ID) --제약조건명 지정 
);

2. 컬럼 값 추가 

INSERT INTO USER_USED_UK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_USED_UK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
ㄴ 위에서 USER_ID UNIQE 값으로 제약조건을 걸었기에, 
   같은 칼럼명을 사용할 수 없다. 
   
INSERT INTO USER_USED_UK
VALUES(1, NULL, 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
ㄴ 하지만 NULL값은 중복으로 작성 가능하다.

● 컬럼 레벨 제약 조건 설정 

제약조건명 미지정 : UESR_ID VARCHAR2(20) UNIQUE  

제약조건명 지정 : UESR_ID VARCHAR2(20) UESR_ID_U UNIQUE 

 

●  테이블 레벨 제약 조건 설정 

제약조건명 미지정  : UNIQUE(USER_ID)

제약조건명 지정 : CONSTRAINT USER_ID_U UNIQUE(USER_ID)

 

 2-2. UNIQUE 복합키 

두개 이상의 컬럼을 묶어서 하나의 UNIQUE 제약 조건을 설정함
모든 컬럼의 값이 같아야 중복으로 취급함 !!
복합키는 테이블 레벨로만 지정할 수 있다. 

CREATE TABLE USER_USED_UK2(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) ,
    USER_PWD VARCHAR2(30) , 
    USER_NAME VARCHAR2(30) ,
    GENDER VARCHAR2(10) ,
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)   ,
    
    -- 테이블 레벨 제약조건 설정
    CONSTRAINT USER_ID_NAME_U UNIQUE(USER_ID, USER_NAME)
);

INSERT INTO USER_USED_UK2
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_USED_UK2
VALUES(2, 'user02', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
--> USER_ID가 다름 -> 삽입 성공 

INSERT INTO USER_USED_UK2
VALUES(2, 'user02', 'pass01', '고길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
--> USER_NAME 다름 -> 삽입성공

INSERT INTO USER_USED_UK2
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
--> 여러 컬럼을 묶어서 UNIQUE 제약 조건이 설정 되어 있으면
--  두컬럼이 모두 중복되는 값일 경우에만 오류 발생
-- ORA-00001: 무결성 제약 조건(PJA.USER_ID_NAME_U)에 위배됩니다

SELECT * FROM USER_USED_UK2;

●  테이블 레벨 제약 조건 설정 

제약조건명 지정 :  CONSTRAINT USER_ID_NAME_U UNIQUE(USER_ID, USER_NAME)

 

3-1 . PRIMARY KEY (기본키) (NOT NULL + UNIQUE)

테이블에서 한 행의 정보를 찾기위해 사용할 컬럼을 의미함 (주민등록키..)

테이블에 대한 식별자(IDENTIFIER)역할을 함

NOT NULL + UNIQUE 제약조건을 의미 

한 테이블당 한개만 설정 할 수 있음 

컬럼레벨,테이블레벨 둘다 설정 가능함

한 개 컬럼에 설정할 수도 있고, 여러개의 칼럼을 묶어서 설정할 수 있음 (복합키)

CREATE TABLE USER_USED_PK(

    USER_NO NUMBER /*CONSTRAINT USER_NO_PK*/ /* PRIMARY KEY*/,--컬럼레벨 PK 지정 
                  /* 생략가능*/
    UEER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30),
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50), 
    
    CONSTRAINT USE_NO_PK PRIMARY KEY(USER_NO)--테이블 레벨 PK 지정 

);

DROP TABLE USER_USED_PK;

INSERT INTO USER_USED_PK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_USED_PK
VALUES(1, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr');
-- 기본 키 중복으로 오류 
-- ORA-00001: 무결성 제약 조건(PJA.USE_NO_PK)에 위배됩니다

INSERT INTO USER_USED_PK
VALUES(NULL, 'user03', 'pass03', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr');
-- 키본키가 NULL이므로 
-- 오류 'ORA-01400: NULL을 ("PJA"."USER_USED_PK"."USER_NO") 안에 삽입할 수 없습니다 '

● 컬럼 레벨 제약 조건 설정 

 USER_NO NUMBER CONSTRAINT USER_NO_PK PRIMARY KEY

                                    /*                생략가능             */

 

●  테이블 레벨 제약 조건 설정 

   CONSTRAINT USE_NO_PK PRIMARY KEY(USER_NO) 

 

3-1 . PRIMARY KEY 복합기 (테이블 레벨만 가능/  NOT NULL )

CREATE TABLE USER_USED_PK2(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    CONSTRAINT PK_USERNO_USERID PRIMARY KEY(USER_NO, USER_ID) -- 복합키
);

INSERT INTO USER_USED_PK2
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_USED_PK2
VALUES(1, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr');

INSERT INTO USER_USED_PK2
VALUES(2, 'user01', 'pass01', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr');

INSERT INTO USER_USED_PK2
VALUES(1, 'user01', 'pass01', '신사임당', '여', '010-9999-9999', 'sin123@kh.or.kr');
-- 회원번호와 아이디 둘다 중복 되었을때만 제약조건 위배 에러 발생 
-- ORA-00001: 무결성 제약 조건(PJA.PK_USERNO_USERID)에 위배됩니다

SELECT  * FROM USER_USED_PK2;

--PRIMARY KEY(식별키)는 NULL이 들어갈 수 없다. 
-->복합키여도 PRIMARY KEY 이므로 NULL절대 안됨! 
INSERT INTO USER_USED_PK2
VALUES(NULL, 'user01', 'pass01', '신사임당', '여', '010-9999-9999', 'sin123@kh.or.kr');

●  테이블 레벨 제약 조건 설정 

 CONSTRAINT PK_USERNO_USERID PRIMARY KEY(USER_NO, USER_ID) 

 

4. FOREIGN KEY(외부키/외래키/ NULL 사용가능)

다른 테이블의 PK 또는 UNIQUE 제약조건이 설정된 컬럼 

 

참조(REFERENCES)된 다른 테이블의 컬럼이 제공하는 값만 사용할 수 있음
FOREIGN KEY제약조건에 의해서 테이블간의 관계(RELATIONSHIP)가 형성됨
제공되는 값 외에는 NULL을 사용할 수 있음

 

● 컬럼레벨일 경우
컬럼명 자료형(크기) [CONSTRAINT 이름] REFERENCES 참조할 테이블명 [(참조할컬럼)] [삭제룰]

● 테이블레벨일 경우
[CONSTRAINT 이름] FOREIGN KEY (적용할컬럼명) REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]
          --> 테이블 레벨에서만  FOREIGN KEY 단어가 사용된다!

* 참조될 수 있는 컬럼은 PRIMARY KEY컬럼과, UNIQUE 지정된 컬럼만 외래키로 사용할 수 있음
참조할 테이블의 참조할 컬럼명이 생략이 되면, PRIMARY KEY로 설정된 컬럼이 자동 참조할 컬럼이 됨

 

1. USER_GRADE 테이블 만들기 

CREATE TABLE USER_GRADE(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);

2. USER_GRADE 칼럼 추가 

INSERT INTO USER_GRADE VALUES (10, '일반회원');
INSERT INTO USER_GRADE VALUES (20, '우수회원');
INSERT INTO USER_GRADE VALUES (30, '특별회원');

SELECT * FROM USER_GRADE;

3. USER_USED_FK  테이블 만들기 


CREATE TABLE USER_USED_FK(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  GRADE_CODE NUMBER
 ,CONSTRAINT GRADE_CODE_FK FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE
 
);


INSERT INTO USER_USED_FK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr', 10);

INSERT INTO USER_USED_FK
VALUES(2, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr', 10);

INSERT INTO USER_USED_FK
VALUES(3, 'user03', 'pass03', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr', 30);

INSERT INTO USER_USED_FK
VALUES(4, 'user04', 'pass04', '안중근', '남', '010-2222-1111', 'ahn123@kh.or.kr', null);
-- NULL 사용 가능 

SELECT * FROM USER_USED_FK;

INSERT INTO USER_USED_FK
VALUES(5, 'user05', 'pass05', '윤봉길', '남', '010-6666-1234', 'yoon123@kh.or.kr', 50);
-- > 50 이라는 값은 USER_GRADE테이블 GRADE_CODE컬럼에서 제공하는 값이 아니므로 
--   외래키 제약 조건에 위배되어 오류 발생 (==50을 참조하지 않아서 오류 발생한다)
-- ORA-02291: 무결성 제약조건(PJA.GRADE_CODE_FK)이 위배되었습니다- 부모 키가 없습니다 
            --> 참조 무결성 위배

FOREIGN KEY 삭제옵션 

부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떤식으로 처리할 지에 대한 내용을 설정 할 수 있다. 

SELECT * FROM USER_GRADE; --참조 당하는 테이블 == 부모테이블
SELECT * FROM USER_USED_FK;   --참조 하는 테이블 == 자식테이블

DELETE FROM USER_GRADE
WHERE GRADE_CODE = 10;
-- ORA-02292: 무결성 제약조건(PJA.GRADE_CODE_FK)이 위배되었습니다
-- 자식 레코드가 발견되었습니다 -- 자식도 10을 쓰고 있음 그러니 삭제 못해 !

위에 내용처럼, 10을 삭제하게 되면 이미 사용하고 있는 컬럼이 있어서 사용을 하지 못한다. 

 

1) ON DELETE RESTRICTED(삭제제한)로 기본 지정되어 있음 
FOREIGN KEY로 지정된 컬럼에서 사용되고 있는 값일 경우 제공하는 컬럼의 값은 삭제하지 못함 

DELETE FROM USER_GRADE
WHERE GRADE_CODE = 20;

자식객체에서 20을 사용하지 않기 때문에 20은 사용가능하다.

2) ON DELETE SET NULL: 부모키 삭제시 자식키를 NULL로 변경하는 옵션

CREATE TABLE USER_GRADE2(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE2 VALUES (10, '일반회원');
INSERT INTO USER_GRADE2 VALUES (20, '우수회원');
INSERT INTO USER_GRADE2 VALUES (30, '특별회원');

-- ON DELETE SET NUL 삭제 옵션이 적용된 테이블 생성
CREATE TABLE USER_USED_FK2(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER VARCHAR2(10),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  GRADE_CODE NUMBER CONSTRAINT GRADE_CODE_FK2 REFERENCES USER_GRADE2 ON DELETE SET NULL
                                                                     --> 삭제 옵션 추가                
);

--샘플 데이터 삽입
INSERT INTO USER_USED_FK2
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr', 10);

INSERT INTO USER_USED_FK2
VALUES(2, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr', 10);

INSERT INTO USER_USED_FK2
VALUES(3, 'user03', 'pass03', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr', 30);

INSERT INTO USER_USED_FK2
VALUES(4, 'user04', 'pass04', '안중근', '남', '010-2222-1111', 'ahn123@kh.or.kr', null);

COMMIT;

SELECT * FROM USER_GRADE2;
SELECT * FROM USER_USED_FK2;

-- 부모 테이블인 USER_GRADE2에서 GRADE_CODE = 10삭제 
-- ON DELETE SET NULL 옵션이 설정되어 있어 오류 없이 삭제 됨 
DELETE FROM USER_GRADE2 WHERE GRADE_CODE = 10;

SELECT * FROM USER_GRADE2; --부모테이블 삭제 확인 (10없음)
SELECT * FROM USER_USED_FK2; -- 자식 테이블 NULL 변환 확인

4. CHECK

CHECK (컬럼명 비교연산자 비교값)
컬럼에 기록되는 값에 조건 설정을 할 수 있음 

주의 : 비교값은 리터럴만 사용할 수 있음, 변하는 값이나 함수 사용 불가 

 

CREATE TABLE USER_USED_CHECK(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  
  GENDER VARCHAR2(10) CONSTRAINT GENDER_CHECK CHECK(GENDER IN ('남','여'))  ,
  
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);

INSERT INTO USER_USED_CHECK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_USED_CHECK
VALUES(2, 'user02', 'pass02', '홍길동', '남자', '010-1234-5678', 'hong123@kh.or.kr');
-- GENDER 컬럼에 CHECK 제약조건으로 '남' 또는 '여'만 기록이 가능한데 
-- '남자'라는 조건 이외에 값이 들어와 에러 발생 
-- ORA-02290: 체크 제약조건(PJA.GENDER_CHECK)이 위배되었습니다

●  테이블 레벨 제약 조건 설정 

 GENDER VARCHAR2(10) CONSTRAINT GENDER_CHECK CHECK(GENDER IN ('남','여'))  ,