[SQL] 제약조건(Constraint)에 관한 이야기

 

오늘은~ 테이블을 생성할 때 설정하는 '제약조건'에 대해 알아보겠어요
사실 기본키, 외래키 공부하다가 이것부터 짚고 넘어가야겠다싶어서 급하게 쓰러 온것ㅎ
후딱 가봅시다 저 지금 졸리거든요

 

블로그만 쓰면 졸린 사람

 

---

 

[1] 데이터 무결성과 제약조건

 

 제약조건(Constraint)

테이블의 열(Column)에 대한 규칙 또는 제한사항을 정의하는데 사용되는 SQL 요소로,
데이터의 무결성을 유지함으로써 DB 내 부적절한 데이터를 방지함

 
일단 제약조건이 무엇인지에 대해 먼저 알아보았는데, 여기서 말하는 데이터의 무결성은 또 대체 뭔가싶다.
맥락을 봐서는 대충 뭔가 부적절한 요소가 없게끔 하는 것같은데.. 그럼 또 여기서 말하는 부적절함의 기준은 무엇일까?

 
 데이터 무결성(Data Integrity)

DB에 저장된 데이터의 정확성 및 일관성을 유지하는 것
데이터 무결성은 제약조건으로 강제할 수 있다

 
한 마디로 테이블 열(column)에 제약 조건을 걸게 되면, 여기에 새로운 데이터가 삽입이 되든 혹은 기존 데이터를 수정을 하든.. 부정확하거나 기준에 맞지 않는 값이 존재하는 것을 방지할 수 있다는 것!!!

 

---

 

[2] 제약조건의 종류

 
RDBMS에서는 이 데이터 무결성 유지를 위해
아래와 같은 5가지 제약조건을 제공한다. (제공 안하는 시스템 있으면 말해주세요..)
 

• PRIMARY KEY (기본키)
• FOREIGN KEY (외래키)
• NOT NULL 
• UNIQUE (고유 제약)
• CHECK (체크 제약)

 
차례대로 구문 형식과 특징에 대해 알아보도록 하자..
 
 

1. PRIMARY KEY (기본키) :

테이블에 존재하는 데이들을 고유한 값으로 구분할 수 있게하는 식별자
기본키에 입력되는 값은 중복값 및 NULL 값이 허용되지 않는다

 
세상에 동일한 주민번호를 가진 사람은 단 한 명도 없고, 어느 사이트든 회원가입을 하면 이미 사용 중인 ID는 쓸 수 없듯이 PK는 테이블에 존재하는 수 많은 데이터를 고유한 값으로 구분할 수 있게 한다.
 
하다못해 포켓몬도 도감번호라는 숫자로 구분하는데.. 이와 같은 경우에 PK를 선언하는 방법은 다음과 같다.

CREATE TABLE pokedex( --포켓몬 도감
    poke_no INT PRIMARY KEY, --포켓몬 번호
    poke_name VARCHAR2(20), --포켓몬 이름
    main_type VARCHAR2(20), --포켓몬 타입
    sub_type VARCHAR2(20) --포켓몬 서브타입
);

 
만약 이미 테이블을 생성한 상태에서 제약조건을 추가하고 싶다면?
ALTER TALBLE + ADD CONSTRAINT문을 사용하면 된다

--ALTER TABLE pokedex ADD PRIMARY KEY(컬럼명)
ALTER TABLE pokedex ADD PRIMARY KEY(poke_no);

-- pk명을 지정할 때는 ADD CONSTRAINT 사용
-- ex) pk_no라는 이름으로 poke_no 컬럼에 기본키 제약을 걸기
ALTER TABLE pokedex ADD CONSTRAINT pk_no PRIMARY KEY(poke_no);

 
그리고 PK 제약 조건을 삭제하고싶을 때는 DROP을 사용한다

--PRIMARY KEY를 삭제할 때
ALTER TABLE pokedex DROP PRIMARY KEY;

--PK명으로 지정해서 삭제하고싶을 때
ALTER TABLE pokedex DROP CONSTRAINT pk_no;

 

2. FOREIGN KEY (외래키) :

테이블 간 관계를 설정하고 두 테이블 사이의 참조 관계를 만든다
참조하는 테이블의 PK를 참조값으로 가지며, 이는 데이터의 무결성을 보장하고 테이블 간 일관성을 유지한다

 
FK(외래키)가 정의된 테이블을 자식 테이블이라고 칭하며, 참조 되는 PK(기본키)가 있는 테이블을 부모 테이블이라 부른다. 즉, 부모테이블이 존재하지 않으면 FK 지정도 불가능하다.
또한 참조하는 값이 부모테이블에 존재하지 않으면 참조관계가 성립이 될 수 없기에, 부모 테이블의 PK 컬럼에 존재하는 데이터만 FK값에 입력이 가능하다. (데이터 타입도 마찬가지)
 
사실 고유키인 UNIQUE KEY도 참조가 가능하긴한데 일반적으로는 PK를 참조한다.
 
외래키 생성 구문은 다음과 같다

--일반적으로 FK컬렴명과 참조컬럼명은 동일하게 짓는다

--테이블 생성 시 설정
CONSTRAINTS 외래키 이름 FOREIGN KEY(컬럼명) REFERENCES 부모테이블명(참조컬럼명)

-- 테이블 생성 후 설정
ALTER TABLE 테이블명 
ADD CONSTRAINTS 외래키 이름 FOREIGN KEY (참조컬럼) REFERENCES 부모테이블명(참조컬럼명)

 
그렇다면 이러한 참조키는 왜 필요한 것인가?
 
예를 들어 어떤 잉어킹 광인 포켓몬 트레이너가 포켓몬을 열심히 포획하는 경우를 생각해보자.. 잉어킹만 6마리
하지만 그럴 때마다 매번 포획하는 중복되는 포켓몬에 대해 '도감번호, 포켓몬 이름, 타입... 그리고 별명, 성별, 레벨'을 붙여 데이터를 기록하면 너무 번거롭고, 컬럼도 너무 많이 차지해서 데이터의 양도 방대해질 것이다.

CREATE TABLE mypokemon(
    poke_no INT, --포획한 포켓몬의 도감번호
    poke_name VARCHAR2(20) -- 포획한 포켓몬 이름
    main_type VARCHAR(20), -- 포획한 포켓몬 메인타입
    sub_type VARCHAR2(20), -- 포획한 포켓몬 서브타입
    nickname VARCHAR2(50), --포획한 포켓몬에게 붙여준 별명
    gender CHAR(1), --포획한 포켓몬의 성별
    lv INT --포획한 포켓몬의 레벨
);

 
참고로 포켓몬 레벨 컬럼을 level로 표기하려했다가.. 잘못된 식별자라는 오류가 계속 떴다..
level이라는 함수가 이미 존재하고 있기 때문이니 참고하세요...
 
암튼 잉어킹을 5마리를 잡았을 때의 데이터를 기록하자면 이런 느낌

poke_no	poke_name	main_type	sub_type	nickname	gender	lv
129	잉어킹	물	(null)	잉어잉어	M	13
129	잉어킹	물	(null)	잉여킹	F	21
129	잉어킹	물	(null)	갸라도스짱	M	10
129	잉어킹	물	(null)	아놔	M	22
129	잉어킹	물	(null)	이름짓기	M	16
129	잉어킹	물	(null)	힘들다	F	8

 
위의 표를 살펴 보면, 포획한 포켓몬의 정보 중 '도감번호 ~ 서브타입' 까지의 부분은 매번 중복되어 기록됨을 알 수 있다. 그리고 이는 pokedex 테이블이 지니고 있는 정보와 동일하기 때문에 여기서 나올 수 있는 생각.
 
'만약 어떤 값을 통해 pokedex 테이블을 참조할 수 있다면?' 
 
그래서 myPokemon 테이블에도 동일하게 도감번호를 나타내는 poke_no 컬럼을 만들고, 이를 PK로 지정해 pokedex 테이블의 도감번호를 참조하기로 했다.

DROP TABLE mypokemon;
CREATE TABLE mypokemon(
    poke_no INT, --포획한 포켓몬의 도감번호
    nickname VARCHAR2(50), --포획한 포켓몬에게 붙여준 별명
    gender CHAR(1), --포획한 포켓몬의 성별
    lv INT --포획한 포켓몬의 레벨
    
    --poke_no컬럼을 fk_no라는 이름으로 부모테이블pokedex의 poke_no를 참조
    CONSTRAINT fk_no FOREIGN KEY(poke_no) REFERENCES pokedex(poke_no)
);

 
그럼 아래와 같이 훨씬 간결해진 테이블로도 도감번호를 참조한다면 해당 포켓몬의 종족 특성을 알 수 있는 것이다. 

poke_no	nickname	gender	lv
129	잉어잉어	M	13
129	잉여킹	F	21
129	갸라도스짱	M	10
129	아놔	M	22
129	이름짓기	M	16
129	힘들다	F	8

 
그리고 외래키는 참조하는 테이블의 컬럼에 존재하는 값의 데이터만 입력 받을 수 있기에, 데이터의 일관성 또한 보장이 가능하다. 즉, 게임프리크가 무슨 변덕이 생겨 갑자기 도감에서 잉어킹이라는 포켓몬을 지워버리면 myPokemon 테이블에도 잉어킹의 도감번호는 존재할 수 없는 것.
 
그런데 이럴 때에 생기는 문제가 하나 있다.
위의 말대로 더 이상 필요가 없는 포켓몬이라고 판단해서 잉어킹을 지워버리고싶지만 그렇게 하면..
 

이런 오류가 발생한다

 
mypokemon  테이블을 보다시피 이미 잉어킹을 6마리나 포획한 트레이너가 있는데, 이렇게 막무가내로 도감의 포켓몬을 지워버리면 이 트레이너는 포켓몬 세계에 존재하지도 않는 포켓몬을 들고 다니는 셈이 된다. 이게 바로 데이터 일관성에 위배되는 것. 아무튼 데이터 무결성에 위배된다고 판단하니 오라클에서 그건 안되겠는데? 하고 막아버리는 것이다.
 

이 삭제에 이의 있습니다

 

여기 이의 추가요

 

(그럼 어떻게 지우라고...)

 
이럴 때 나오는 개념이 바로~
 

 ON DELETE CASCADE

부모테이블의 PK가 삭제될 때 이를 참조하고 있는 Key가 포함된 모든 행을 삭제한다.

 
당장 적용해보자

--ALTER TABLE 테이블명 DROP CONSTRAINT 외래키명;
ALTER TABLE mypokemon DROP CONSTRAINT fk_no;

/*ALTER TABLE 테이블명 ADD CONSTRAINT 외래키명 FOREIGN KEY(컬럼명) 
  REFERENCES 부모테이블(컬럼명) ON DELETE CASCADE*/
    
ALTER TABLE mypokemon ADD CONSTRAINT fk_no FOREIGN KEY (poke_no) 
REFERENCES pokedex(poke_no) ON DELETE CASCADE;

 
기존에 존재하는 제약조건을 삭제하고, ON DELETE CASCADE를 적용해 새로 제약조건을 생성했다.
아무튼 이렇게 될 경우 과연 잉어킹들은 잘 삭제가 될까?
 
 

일단 도감에서는 잉어킹의 존재가 잘 사라진듯 하다

 
그렇다면 포획 포켓몬들은 어떻게 됐을까?

전

후

굳
 
트레이너에겐 미안한 일이지만,  아주 깔끔하게 잘 날아갔다
 

비슷한 예시로 ON DELETE SET NULL 이 있는데, 이 친구는 부모 테이블의 값이 삭제될 때 행 삭제 대신 자식 테이블의 값들이 NULL로 설정된다.

ALTER TABLE mypokemon DROP CONSTRAINT fk_no;
ALTER TABLE mypokemon ADD CONSTRAINT fk_no FOREIGN KEY (poke_no) REFERENCES pokedex(poke_no) ON DELETE SET NULL;

DELETE FROM pokedex WHERE poke_name = '잉어킹';

이런 식으로..

 
보통은 ON DELETE CASCADE를 많이 쓴다고는 하는데.. 뭐 입맛에 맞게 갖다 쓰십쇼
 
그리고 마지막으로 부모테이블의 PK가 수정될 때 참조하고 있는 FK도 함께 수정되는 ON UPDATE CASCADE도 있다.
사용 방법은 ON DELETE CASCADE와 동일하니 위 코드 참고~
 

---

 

3. NOT NULL :

해당 열에 NULL 값을 포함하지 못하도록 강제
데이터 무결성을 유지하고 필수적인 데이터를 보장하기 위해 사용

 
어쩐지 굉장히 오랜만에 본론으로 돌아온 느낌이다.. 여기부터는 뜻이랑 사용법만 짚고 넘어가겠습니다.. 힘들어

--컬럼명 데이터타입 NOT NULL
--테이블을 생성할 때
poke_name VARCHAR(20) NOT NULL

--이미 존재하는 테이블에 제약을 추가할 때
ALTER TABLE pokedex MODIFY poke_name VARCHAR(20) NOT NULL;

 
 

4. UNIQUE (고유제약) :

열에 고유한 값만 허용하지만, (중복되는 값을 허용하지 않음)
기본키와 달리 하나 이상의 NULL 값을 포함할 수 있음

 
이와같은 특성으로 FK가 고유제약 키도 참조할 수 있는것..

--테이블 생성시 제약 추가
poke_name VARCHAR(20) CONSTRAINT uk_name UNIQUE(poke_name)

--이미 존재하는 테이블에 제약 추가
ALTER TABLE pokedex ADD CONSTRAINT uk_name UNIQUE(poke_name);

 

5. CHECK (체크제약) :

열의 값이 특정 조건을 충족해야함 (입력 범위를 직접 지정해줌)
지정한 입력 범위를 벗어난 값이 들어올 수 없게한다

 
예를 들어 포켓몬의 레벨 범위는 1 ~ 100까지 유효하다
이때 mypokemon 테이블에 거는 제약은

 --테이블 생성시 제약 추가
 lv INT CONSTRAINT level_check CHECK(lv >= 1 AND lv <= 100)
 
 --이미 존재하는 테이블에 제약 추가
 ALTER TABLE mypokemon ADD CONSTRAINT level_check CHECK(lv >= 1 AND lv <= 100);

 
그리고 마지막으로 위의 모든 제약을 적용한 테이블의 상태를 확인해보자
 

NOT NULL 적용완료

PK 및 고유키 적용 완

FK및 체크제약 적용 완

 

다들 멀쩡히 잘 추가된 모습을 볼 수 있다옹
 

---

 
어우 너무 졸리네요 이놈의 외래키 좀 잘 이해해보겠다고 이 사달이..
이제 진짜 잘래요 안녕 그리고 내일 왜 월요일

히히