SQL - 제약 조건
1. 제약조건의 종류
1.1 제약조건이란?
- 오라클에서 제약 조건은 테이블의 특정 열에 지정
- 제약 조건을 지정한 열에 제약 조건에 부합하지 않는 데이터를 저장할 수 없다.
- 제약 조건 지정 방식에 따라 기존 데이터의 수정 혹은 삭제 가능 여부도 영향을 받는다.
| NOT NULL | 지정한 열에 NULL 허용 X (데이터 중복 허용) |
| UNIQUE | 지정한 열이 유일한 값을 가져야 합니다. (중복될 수 없다) NULL 가능 |
| PRIMARY KEY | 지정한 열이 유일한 값, NULL x, 테이블에 하나만 지정 |
| FOREIGN KEY | 다른 테이블의 열을 참조하여 존재하는 값만 입력할 수 있다. |
| CHECK | 설정한 조건식을 만족하는 데이터만 입력 가능 |
* 데이터 무결성이란?
데이터베이스에 저장되는 데이터의 정확성과 일관성 보장
| 영역 무결성 | 열에 저장되는 값 적정 여부 확인, 정해 놓은 범위 만족하는 데이터임을 규정 |
| 개체 무결성 | 테이블에 데이터를 유일하게 식별할 수 있는 기본키는 반드시 값을 가져야하며 null과 중복x |
| 참조 무결성 | 참조 테이블의 외래키 값은 참조 테이블의 키본키로서 존재해야하며, null 가능 |
위와 같은 제약 조건은 설계 시점에 주로 지정한다. 물론 추가,변경,삭제 가능 -> DDL을 통해 활용
2. 빈값을 허락하지 않는 NOT NULL
- 열에 데이터 중복 여부와는 상관없이 NULL의 저장을 허용하지 않는 제약 조건이다.
- 반드시 열값이 존재해야하는 경우 사용한다.
- NOT NULL을 지정하면 UPDATE문을 사용하여 열 값을 NULL로 수정하는 것도 불가능 합니다.
CREATE TABLE TABEL_NOTNULL(
LOGIN_ID VARCHAR(20) NOT NULL,
LOGIN_PWD VARCHAR(20) NOT NULL,
TEL VARCHAR2 (20)
);
2.1 제약조건 확인하기
- USER_CONSTRAINTS데이터 사전을 통해 확인 가능
SELECT OWER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS;
2.2 제약 조건 이름 지정하기
- 이름을 직접 지정하지 않으면, 알아서 제약조건 명이 생성됨
CREATE TABLE TABEL_NOTNULL(
LOGIN_ID VARCHAR(20) CONSTRAINT TBLNN2_LGNID_NN NOT NULL,
LOGIN_PWD VARCHAR(20) CONSTRAINT TBLNN2_LGNPW_NN NOT NULL ,
TEL VARCHAR2 (20)
);* NOT NULL같은 경우에는 제약조건명을 딱히 지정하지 않음
2.3 이미 생성한 테이블에 제약조건 지정
- MODIFY키워드를 사용
ALTER TABLE TABLE_NOTNULL MODIFY (TEL NOT NULL);
ALTER TABLE TABLE_NOTNULL MODIFY (TEL TBLNN_TEL_NN NOT NULL);- 제약조건 명만 변경하기
ALTER TABLE TABLE_NOTNULL2
RENAME CONSTRAINT TBLNN_TEL_NN TO TBLNN2_TEL_NN;
2.4 제약 조건 삭제
ALTER TABLE TABLE_NOTNULL2
DROP CONSTRAINT TBLNN2_TEL_NN;
3. 중복되지 않는 값 UNIQUE
- 열에 저장할 데이터의 중복을 허용하지 않고 싶을 때 사용합니다. NULL값은 가능
- 지정방법은 NOT NULL과 동일함
CREATE TABLE TABEL_NOTNULL(
LOGIN_ID VARCHAR(20) UNIQUE,
LOGIN_PWD VARCHAR(20) NOT NULL,
TEL VARCHAR2 (20)
);
4. 유일한 값 PRIMARY KEY
- UNIQUE와 NOT NULL제약 조건의 특성을 모두 가짐
- 테이블에 하나만 지정해야한다. 또한 특정 열을 PRIMARY KEY로 지정하면 자동으로 인덱스가 만들어진다.
CREATE TABLE TABEL_NOTNULL(
LOGIN_ID VARCHAR(20) PRIMARY KEY,
LOGIN_PWD VARCHAR(20) NOT NULL,
TEL VARCHAR2 (20)
);
* CREATE문에서 제약조건 지정하는 다른 방식
- 인라인,열레벨 제약조건은 위에서 제약조건을 명시했던 것과 일치
- 아웃라인,테이블 레벨 제약 조건은 아래와 같음
CREATE TABLE TABLE_NAME(
열이름 타입
PRIMARY KEY(COL1), -- 제약조건 이름을 지정 x
CONSTRAINT CONSTRAINT_NAME UNIQUE(COL2) -- 제약조건 이름 지정
)
5. 다른 테이블과 관계를 맺는 FOREIGN KEY
- 서로 다른 테이블 간 관계를 정의하는데 사용
- 특정 테이블에서 PRIMARY KEY 제약 조건을 지정한 열을 다른 테이블의 특정 열에서 참조하겠다는 의미
- 참조 대상 테이블을 부모, 참조하는 테이블을 자식이라고 함
- 부모테이블 기본키열에 없는 값을 자식 테이블 외래키에서 사용하려고 하면 에러 발생한다.
INSERT INTO EMP(DEPTNO) VALUES (50);
--DEPTNO는 DEPT에 기본키이자 EMP의 외래키, DEPT DEPTNO에 없는 값을 EMP에서 참조하려고 하면 안됨
5.1 FOREIGN KEY 지정하기
CREATE TABLE 테이블 명(
--인라인
열이름 열자료형 CONSTRAINT [제약조건명] REFERENCES 참조테이블 (참조할 열)
--아웃라인
CONSTRAINT [제약조건명] FOREIGN KEY(열) REFERENCES 참조테이블 (참조할 열)
)
5.2 FOREIGN KEY로 참조 행 데이터 삭제하기
DELETE FROM DEPT_FK --참조테이블
WHERE DEPTNO = 10;
-- 참조하는 자식 테이블이 DEPT_FK의 DEPTNO = 10 값 참조 중 따라서 삭제 불가
- 기본키 가진 테이블에서 기본키 특정 열값을 삭제하기 위해서는
1. 현재 삭제하려는 열 값을 참조하는 데이터 먼저 삭제
2. 현재 삭제하려는 열 값 참조하는 데이터 수정
3. 자식테이블에서 FOREIGN KEY 제약조건 해제
** 회원(PK) - 주문(FK) -> 주문이 회원 PK를 FK로 사용하느냐 아니냐에 따라 식별, 비식별 관계
일부로 위와 같은 관계를 만들기도 한다.
- 열 데이터 삭제시 이 데이터 참조하고 있는 데이터 삭제
CONSTRAINT [제약조건명] REFERENCES 참조테이블(참조할 열) ON DELETE CASCADE- 열 데이터 삭제시 이 데이터 참조하는 데이터 NULL로 수정
CONSTRAINT [제약조건명] REFERENCES 참조테이블(참조할 열) ON SET NULL* 두개의 차이점은 DELETE CASCADE시 참조하는 열의 모든 데이터(행 자체)삭제
NULL은 그 열의 값만 NULL로 변경함
6. 데이터 형태와 범위 정하는 CHECK
- 열에 저장할 수 있는 값의 범위 또는 패턴을 정의할 때 사용합니다.
CREATE TABLE TABLE_CHECK(
열이름 테이터형 CONSTRAINT [제약조건명] CHECK (값의 범위 ex LENGTH(열)>3)
)
7. 기본값을 정하는 DEFAULT
- 제약조건과 별개로 특정 열에 값이 지정되지 않았을 경우 기본값 지정
CREATE TABLE TABLE_DEFAULT(
열이름 데이터형 DEFAULT '1234'
);
** 제약 조건 비활성화, 활성화
- 제약조건은 필요하지만 종종 거슬림 (테스트나, 신규개발 등등에서) 따라서 지정된 제약조건을 키고 끌 수 있다.
ALTER TABLE 테이블 명
DISABLE [NOVALIDATE/VALIDATE (선택)] CONSTRAINT 제약조건명
-- ENABLE [NOVALIDATE/VALIDATE (선택)] CONSTRAINT 제약조건명