[SQL] NOT NULL 제약조건과 FK제약조건

NOT NULL 제약조건

NOT NULL 제약조건은 해당 열에 NULL을 허용하지 않는다는 제약조건이다.

 

CREATE TABLE MEMBER( -- 회원
   MEMBER_ID VARCHAR(255) PRIMARY KEY, --PK MID
   MEMBER_PASSWORD VARCHAR(20) NOT NULL, -- 비밀번호
   MEMBER_NAME VARCHAR(20) NOT NULL,	-- 이름
   MEMBER_PHONE VARCHAR(20) -- 전화 번호
);

 

위와 같이 MEMBER 테이블을 생성할 때

NOT NULL을 해당 컬럼에 적으면 제약조건을 걸어 테이블을 생성할 수 있다.

 

NOT NULL 제약조건은 NULL을 허용하지 않기 때문에, 데이터를 INSERT 할 때 NULL인 데이터가 삽입된다면

오류가 발생하게 된다.

 

 

FK 제약조건

FK는 FOREIGN KEY (==외래키, 외부키)라고 하며, 테이블 간 관계를 정의하는 데 사용한다.

FK제약조건은 ALTER문을 사용하여 생성할 수 있다.

 

ex. 게시판 테이블

CREATE TABLE BOARD( -- 게시판
	BOARD_NUM INT PRIMARY KEY, -- 번호 PK
	BOARD_TITLE VARCHAR(2000) NOT NULL, -- 제목
	BOARD_CONTENT VARCHAR(2000), -- 내용
	BOARD_WRITER_ID VARCHAR(255) NOT NULL, -- 작성자 FK 
	BOARD_REGISTRATION_DATE DATE DEFAULT SYSDATE -- 작성 날짜
);

 

ALTER문을 사용한 FK 제약조건

ALTER TABLE BOARD -- BOARD 테이블 변경
ADD CONSTRAINT FK_BOARD_WRITER_ID -- FK 제약조건을 추가, 제약조건 이름은 FK_BOARD_WRITER_ID
FOREIGN KEY (BOARD_WRITER_ID) -- BOARD_WRITER_ID가 FK
REFERENCES MEMBER(MEMBER_ID) --그 FK 값은 MEMBER테이블의 MID에서 가져온다.
ON DELETE CASCADE; -- 의존하고 있는 원본 데이터(회원탈퇴시) 사라지면 연결 데이터(글) 사라짐

 

 

---

 

문제점

 

이번 중간 프로젝트를 진행하면서 이와 관련하여 한 가지 문제점이 발생했다.

 

결제 테이블

CREATE TABLE PAYMENT( -- 결제
   PAYMENT_NUM INT PRIMARY KEY, -- 결제 번호 PK : C에서 UUID를 활용하여 조합한 번호, String
   PAYMENT_MEMBER_ID VARCHAR(255) NOT NULL, -- 결제자 ID FK
   PAYMENT_PRODUCT_NUM INT NOT NULL, -- 상품 번호 FK
   PAYMENT_ORDER_NUM VARCHAR(50) NOT NULL, -- 주문 번호 (포트원에서 생성되는 번호)
   MERCHANT_UID VARCHAR(100) NOT NULL, -- C에서 사용하는 번호 (UUID + 시간 조합)
   PAYMENT_REGISTRATION_DATE DATE DEFAULT SYSDATE, -- 결제 날짜
   PAYMENT_PRICE INT NOT NULL, -- 결제 금액
   PAYMENT_STATUS VARCHAR(20) NOT NULL CHECK(PAYMENT_STATUS IN('결제완료','결제취소')), -- 결제 상태
   PAYMENT_METHOD VARCHAR(20) NOT NULL -- 결제 방법
);

결제 테이블을 위와 같이 생성을 했다.

대부분 NOT NULL 제약조건이 걸려 있어 NULL이 허용되지 않는 것을 볼 수 있다.

 

 

FK제약조건

-- FK제약조건 (멤버)
ALTER TABLE PAYMENT --PAYMENT 테이블 변경
ADD CONSTRAINT FK_PAYMENT_MEMBER_ID -- 제약조건 추가할거야
FOREIGN KEY (PAYMENT_MEMBER_ID) -- FK키는 PAYMENT_MEMBER_ID
REFERENCES MEMBER(MEMBER_ID) -- FK는 MEMBER테이블의 MEMBER_ID에 의존하고 있어
ON DELETE SET NULL; -- 회원이 탈퇴해도 결제 내역은 남겨둠. 회원 정보만 NULL로 바뀜 (추후 관리자 기능에 필요..!!)

-- FK제약조건 (상품)
ALTER TABLE PAYMENT--PAYMENT 테이블 변경
ADD CONSTRAINT FK_PRODUCT_PRODUCT_NUM -- FK 제약조건 추가
FOREIGN KEY (PAYMENT_PRODUCT_NUM) --PAYMENT_PRODUCT_NUM
REFERENCES PRODUCT(PRODUCT_NUM) -- PRODUCT 테이블의 PRODUCT_NUM이야
ON DELETE SET NULL; -- 상품이 삭제돼도, 결제 내역은 남아있고 / 상품 정보만 NULL 처리됨

 

PAYMENT_MEMBER_ID와 PAYMENT_PRODUCT_NUM가 PAYMENT 테이블에서의 FK키인데,

설계 시 이 두 가지는 부모 테이블의 데이터가 삭제되어도 데이터를 남기기로 한 상태이다.

이때 ON DELETE에 SET NULL을 명시해서, 데이터가 삭제되면 FK키가 NULL로 변경되도록 해야 한다.

 

그러나 FK 키에 NOT NULL 제약조건을 걸어둔 상태라 NULL값이 허용되지 않는 상황이다.

이를 해결하기 위해서는?

 

방법 1)

NOT NULL 제약조건을 제거해 NULL을 허용하고, FK 제약조건을 위처럼 ON DELETE SET NULL 한다.

 

방법 2)

임의의 데이터를 만들어서 '탈퇴한 회원', '삭제된 상품' 임을 알려준다.

 ==>> 회원탈퇴 시 회원정보를 삭제하지 않고 회원 상태를 "탈퇴" 혹은 ID를 "탈퇴한 사용자"로 UPDATE 한다.

 

 

 

이번에 우리는 1번 방식을 선택했다.

1번을 선택하는 경우 결제한 회원과 결제한 상품 정보가 NULL이 될 수 있기 때문에 데이터 무결성이 깨지지만,

2번 방식을 사용하는 경우 회원/상품 상태와 관련 내역들을 모두 UPDATE 해야 하며, 트랜젝션도 신경 써야 하기 때문에

성능에 영향을 줄 수 있다.

 

우리는 규모가 작은 프로젝트이며, 추가 로직이 없고 유지보수가 용이한 1번 방식을 선택했다.

 

 

수정 후

CREATE TABLE PAYMENT( -- 결제
   PAYMENT_NUM INT PRIMARY KEY, -- 결제 번호 PK : C에서 UUID를 활용하여 조합한 번호, String
   PAYMENT_MEMBER_ID VARCHAR(255), -- 결제자 ID FK
   -- FK 제약조건 ON DELETE SET NULL 조건으로 인하여 NOT NULL 제약조건 삭제
   PAYMENT_PRODUCT_NUM INT, -- 상품 번호 FK
   -- FK 제약조건 ON DELETE SET NULL 조건으로 인하여 NOT NULL 제약조건 삭제
   PAYMENT_ORDER_NUM VARCHAR(50) NOT NULL, -- 주문 번호 (포트원에서 생성되는 번호)
   MERCHANT_UID VARCHAR(100) NOT NULL, -- C에서 사용하는 번호 (UUID + 시간 조합)
   PAYMENT_REGISTRATION_DATE DATE DEFAULT SYSDATE, -- 결제 날짜
   PAYMENT_PRICE INT NOT NULL, -- 결제 금액
   PAYMENT_STATUS VARCHAR(20) NOT NULL CHECK(PAYMENT_STATUS IN('결제완료','결제취소')), -- 결제 상태
   PAYMENT_METHOD VARCHAR(20) NOT NULL -- 결제 방법
);


-- FK제약조건 (멤버)
ALTER TABLE PAYMENT --PAYMENT 테이블 변경
ADD CONSTRAINT FK_PAYMENT_MEMBER_ID -- 제약조건 추가할거야
FOREIGN KEY (PAYMENT_MEMBER_ID) -- FK키는 PAYMENT_MEMBER_ID
REFERENCES MEMBER(MEMBER_ID) -- FK는 MEMBER테이블의 MEMBER_ID에 의존하고 있어
ON DELETE SET NULL; -- 회원이 탈퇴해도 결제 내역은 남겨둠. 회원 정보만 NULL로 바뀜 (추후 관리자 기능에 필요..!!)

-- FK제약조건 (상품)
ALTER TABLE PAYMENT--PAYMENT 테이블 변경
ADD CONSTRAINT FK_PRODUCT_PRODUCT_NUM -- FK 제약조건 추가
FOREIGN KEY (PAYMENT_PRODUCT_NUM) --PAYMENT_PRODUCT_NUM
REFERENCES PRODUCT(PRODUCT_NUM) -- PRODUCT 테이블의 PRODUCT_NUM이야
ON DELETE SET NULL; -- 상품이 삭제돼도, 결제 내역은 남아있고 / 상품 정보만 NULL 처리됨

 

 

 

*참고사항

실무에서는 회원 탈퇴 시 :

회원 보관 기간 3 ~ 5년이 있기 때문에, 회원이 탈퇴를 해도 바로 데이터를 삭제하지 않고

UPDATE 한다. (DELETE 없음) 그리고 보관기간이 지나면 모든 데이터를 삭제한다.