[SQL] 제약 조건

안녕하세요. 이번 글은 제약조건에 대한 설명입니다.

- 제약 조건이란?

• 테이블 내 특정 칼럼에 조건에 부합하지 않는 값을 입력할 수 없도록 하는 조건
• 대표적으로 NOT NULL, CHECK, PRIMARY KEY, FORGEIN KEY, UNIQUE가 있다.

- 제약 조건 정보를 모두 볼 수 있는 테이블

SELECT * FROM USER_CONSTRAINTS;

USER_CONSTRAINTS 테이블은 현재 로그인하고 있는 계정에 존재하는 모든 테이블의 제약 조건을 보여주고 있다.

칼럼명	설명
OWNER	제약 조건의 소유자
CONSTRAINT_NAME	제약 조건의 명칭 (별도의 지정이 없으면 임의값 배정)
CONSTRAINT_TYPE	제약 조건 타입 (C : CHECK, NOT NULL, U : UNIQUE, P : Primary Key, R : Foreign Key)
TABLE_NAME	제약 조건이 명시되어있는 테이블명

상단의 이미지는 테이블 생성하는 쿼리문이며 제약 조건의 종류와 제약 조건의 명칭을 지정할 수 있다는 점을 확인할 수 있다. 학번인 STU_NUM은 기본키로 설정하였으며 성별 정보를 담는 STU_GENDER는 CHECK를 통해 남자와 여자 두 개의 데이터만 담을 수 있도록 제한한 점을 확인할 수 있으며 나머지는 NOT NULL을 통해 빈 값을 허용하지 않도록 두었다.

STU_NAME VARCHAR2(300) NOT NULL CONSTRAINT STU_INFO_NAME_NN

다음과 같이 [칼럼명] [데이터타입] [제약조건] CONSTRAINT [제약조건명]을 통해 테이블 생성과 동시에 제약 조건을 설정할 수 있다. 데이터 생성 이후에 USER_CONSTRAINTS 테이블 뒤에 TABLE_NAME = '[테이블명]'을 붙이고 쿼리문을 실행하면 생성한 테이블에 대한 모든 제약 조건에 대한 정보를 확인할 수 있다.

ALTER TABLE STUDENT_INFO 
     MODIFY (STU_DEPT NULL);

상단의 쿼리문은 테이블 내의 칼럼에 대해서 제약 조건을 변경하는 쿼리문이다. 제약 조건을 기존 NOT NULL에서 NULL로 변경하라는 뜻을 가지고 있다. 해당 쿼리문을 실행하면 제약 조건을 무리없이 바꿀 수 있지만 반대로 NULL에서 NOT NULL로 변경하려고 할 때는 다를 수 있다. 왜냐하면 기존에 테이블에 저장되어 있는 데이터 중에 NULL값이 하나라도 있다면 변경하려는 NOT NULL이라는 제약 조건에 위배된다는 점에서 변경이 안되는 경우가 존재하기 때문이다. 그렇기 때문에 제약 조건은 가급적이면 처음 테이블을 생성할 때 계획하고 지정하는 것을 권장한다. 물론 기존 NULL이었던 데이터를 빈 값이 없도록 수정한 후에 제약 조건을 수정할 수 있다.

ALTER TABLE STUDENT_INFO RENAME CONSTRAINT STU_INFO_ACA_NN TO STU_INFO_STATUS_NN;

상단의 쿼리문은 테이블 내 기존 제약조건명을 다른 이름으로 새롭게 수정하는 역할을 담당하고 있다. 쿼리문 형식은 ALTER TABLE [테이블명] RENAME CONSTRAINT [기존명칭] TO [새명칭]이다.

해당 쿼리문 실행 후 USER_ CONSTRAINTS 테이블을 조회하면 정상적으로 명칭이 변경된 점을 확인할 수 있다.

ALTER TABLE STUDENT_INFO DROP CONSTRAINT STU_INFO_STATUS_NN;

상단 쿼리문을 통해 기존에 지정되어 있던 테이블 내 제약조건을 삭제할 수 있다.

- 빈 값은 허용하지 않는다. NOT NULL

INSERT INTO STUDENT_INFO VALUES ('이땡땡', '20240101', '1', '예술디자인대학', '서양화과', NULL ,'남자');

상단에서 언급했던 STUDENT_INFO 테이블에 STU_ACA이라는 칼럼에 값을 넣지 않은 상태로 상단 쿼리문을 실행하면 ORA-01400: NULL을 ("SCOTT"."STUDENT_INFO"."STU_ACA") 안에 삽입할 수 없습니다 라는 알림창과 함께 테이블에 데이터가 입력되지 않는 점을 확인할 수 있다. 왜냐하면 STU_ACA는 NOT NULL이라는 제약 조건이 존재하기 때문에 빈 값을 허용하지 않기 때문이다. 이렇듯 NOT NULL은 빈 값이 삽입되는 것을 허용하지 않는다는 점을 알 수 있다.

- 빈 값은 허용해도 중복값은 허용하지 않는다. UNIQUE

SELECT * FROM TABLE_UNIQUE;

상단의 TABLE_UNIQUE 테이블의 스크립트를 보면 LOGIN_ID라는 칼럼의 제약 조건이 UNIQUE로 설정되어 있는 점을 확인할 수 있다. UNIQUE는 NULL은 값 자체가 없기 때문에 여러 개 있어도 무방하나 데이터의 중복을 허용하지 않는다. 

INSERT INTO TABLE_UNIQUE VALUES ('TEST_ID_02', '1111', '02-1111-1111');

상단의 쿼리문을 실행하면 ORA-00001: 무결성 제약 조건(SCOTT.SYS_C007645)에 위배됩니다 라는 알림이 발생하는 점을 확인할 수 있는데 이러한 알림이 발생하는 이유는 중복된 값을 허용하지 않는 제약 조건을 위배했기 때문이다. 이렇듯 UNIQUE 제약조건은 중복된 데이터를 허용하지 않는다는 점을 확인할 수 있다.

- 지정해둔 값만 저장할 수 있다. Check

INSERT INTO STUDENT_INFO VALUES ('김땡땡', '20240101', '1', '인문대학', '문헌정보학과', '재학', '남성');

다음과 같이 쿼리문을 입력 후 실행하면 ORA-02290: 체크 제약조건(SCOTT.STU_INFO_GENDER_CK)이 위배되었습니다 라는 알림을 확인할 수 있을 것이다. 이러한 알림이 발생한 이유는 STU_GENDER라는 칼럼에 CHECK 라는 제약 조건이 있는데 여기에는 남자 또는 여자라는 값만 들어오는 것을 허가하고 그 외 값은 삽입되지 않도록 제한되어있기 때문이다.이렇게 지정된 값 외에 다른 값들은 삽입되지 않도록 규정하는 제약 조건을 CHECK라고 한다.

- NOT NULL + UNIQUE = Primary Key

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TABLE_PK';

TABLE_PK라는 테이블의 제약 조건을 보면 다음과 같이 P 라는 타입이 있는 점을 통해 내부에 기본키가 지정되어 있는 점을 확인할 수 있다. 기본키는 일종의 고유번호로서 유일성과 최소성을 가졌다는 점이 특징이다. NULL값이어도 안되며 기존 데이터와 중복되서도 안된다는 뜻이다.

INSERT INTO TABLE_PK VALUES ('TEST_ID_02', 'PWD02', '010-1111-1111');

다음과 같은 쿼리문을 실행하면 ORA-00001: 무결성 제약 조건(SCOTT.SYS_C007650)에 위배됩니다 라는 알림이 발생하는 점을 확인할 수 있다. 해당 알림이 발생하는 이유는 중복 데이터라는 점에서 제약 조건을 위배했기 때문이다.

INSERT INTO TABLE_PK VALUES (NULL, 'PWD02', '010-1111-1111');

상단의 쿼리문을 실행하면 NULL을 ("SCOTT"."TABLE_PK"."LOGIN_ID") 안에 삽입할 수 없습니다 라는 알림이 발생하는데 이는 빈 값이라는 점에서 제약 조건을 위배했기 때문에 데이터가 들어가지 않는다는 점을 확인할 수 있으며 상단의 두 개의 쿼리문을 통해 TABLE_PK 내 LOGIN_ID라는 칼럼이 기본키라는 점을 확인할 수 있다.

- 다른 테이블을 참조하고 연결하는 Foreign Key

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('DEPT_FK', 'EMP_FK');

외래키는 서로 다른 테이블과의 관계를 정의할 때 사용하는 제약조건이며 특정 테이블의 기본키를 다른 테이블의 특정 칼럼에서 본인들의 테이블을 다룰 때 참조하겠다는 의미를 담고 있다. 상단의 쿼리문을 실행해보면 EMP_FK와 DEPT_FK 테이블의 제약 조건에 대한 정보를 확인할 수 있는데 이때 CONSTRAINT_TYPE와 R_CONSTRAINT_NAME이라는 칼럼을 주목해보자. CONSTRAINT_TYPE이 R인 점을 봐서 외래키라는 점을 확인할 수 있으며 DEPTFK_DEPTNO_PK가 DEPT_FK 테이블의 기본키라는 점을 보면 두 테이블 간의 연결 관계가 존재한다는 점과 EMP_FK는 DEPT_FK를 참조하고 있다는 점을 알 수 있다.

SELECT * FROM EMP_FK;

SELECT * FROM DEPT_FK;

상단 두 개의 테이블을 봤을 때 공통적인 칼럼 DEPTNO를 확인할 수 있으며 또한 참조관계를 보면 EMP_FK에서 DEPTNO 칼럼을 추가할 때는 무조건 DEPT_FK 테이블을 참조하도록 되어있다. 이를 통해 EMP_FK 테이블에서는 DEPT_FK 내 DEPTNO에 저장되어 있는 값만을 저장할 수 있으며 DEPT_FK에 없는 데이터는 EMP_FK에서 저장할 수 없다는 점을 확인할 수 있다.

INSERT INTO EMP_FK VALUES ('1234', 'BLUE', 'CLERK', '5678', SYSDATE, 2800, NULL, 20);

해당 쿼리문을 실행해보면 ORA-02291: 무결성 제약조건(SCOTT.EMPFK_DEPTNO_FK)이 위배되었습니다- 부모 키가 없습니다 라는 알림을 확인할 수 있는데 이는 참조되고 있는 테이블 내에 해당되는 데이터가 없기 때문에 발생한 것이다.

INSERT INTO EMP_FK VALUES ('1234', 'BLUE', 'CLERK', '5678', SYSDATE, 2800, NULL, 10);

상단의 쿼리문 같이 DEPT_FK에 있는 데이터를 확인해서 실행하면 상단 이미지처럼 정상적으로 데이터가 입력된 점을 확인할 수 있다.

CONSTRAINT EMPFK_DEPTNO_FK REFERENCES DEPT_FK (DEPTNO)

다음과 같이 CONSTRAINT [제약조건명] REFERENCES [참조 대상 테이블] ([참조 칼럼])을 통해 테이블을 생성할 때 참조할 테이블을 바탕으로 외래키를 설정할 수 있는 점 확인할 수 있다.

DELETE FROM DEPT_FK WHERE DEPTNO = '10';

다음과 같이 두 개의 테이블은 참조 관계를 가지고 있는데 부모 테이블인 DEPT_FK에 있는 값을 자식 테이블인 EMP_FK에서 참조해서 사용하고 있는 상태이다. 이러한 상황에서 상단 쿼리문을 실행하면 ORA-02292: 무결성 제약조건(SCOTT.EMPFK_DEPTNO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다 이라는 알림이 발생하는 것을 볼 수 있다. 참조 관계의 테이블 사이에서 자식 테이블이 부모 테이블의 데이터를 참조한 건이 하나라도 있으면 삭제할 수 없다.

그럼에도 불구하고 삭제해야된다고 하면 자식 테이블인 EMP_FK에서의 참조하고 있는 행에 대해서 데이터를 수정 또는 삭제하거나 부모 테이블 내 데이터를 제거하면서 참조하고 있는 자식 테이블의 데이터도 함께 지워야 한다.

후자의 방법을 사용하려면 하단의 방법을 사용해야 한다.

• CONSTRAINT [제약조건명] REFERENCES [참조 대상 테이블]([참조 칼럼]) ON DELETE CASCADE
• CONSTRAINT [제약조건명] REFERENCES [참조 대상 테이블]([참조 칼럼]) ON DELETE SET NULL

첫 번째부터 보면 부모 테이블에서 데이터 삭제 시 해당 값을 참조하고 있는 자식 테이블의 데이터도 함께 삭제되도록 설정하는 것이며 두 번째는 부모 테이블 데이터 삭제 시 참조하고 있는 자식 테이블의 데이터는 NULL값으로 변경하도록 설정하는 것이다. 데이터를 최대한 보존하는 방향으로 가야되면 두 번째 방법을 사용하는 것이 좋다.

- 빈 값이면 기본 값으로 자동 삽입한다. Default

다음은 DEFAULT 제약 조건인데 상단의 쿼리문처럼 뒤에 DEFAULT [기본값]을 통해 특정 칼럼에 별도로 값을 지정하지 않으면 설정해둔 값이 자동으로 삽입될 수 있도록 설정할 수 있다.

INSERT INTO TABLE_DEFAULT (LOGIN_ID, TEL) VALUES ('TEST_ID2', '010-1234-5678');

다음과 같이 LOGIN_PWD에 아무런 값없이 INSERT문을 실행하면 오류 발생없이 기본값으로 설정해둔 값이 정상적으로 들어가있는 점을 확인할 수 있다.