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 제약조건명