기술노트 블로그

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

제약조건

기본 키와 외래 키가 대표적인 제약조건입니다.

데이터의 무결성을 유지합니다. (완전무결한 코드)

예를 들어, 제약조건을 통해서 중복된 아이디가 NAVER에 접속하는 것을 방지한다.

기본키 제약조건

그 많은 회원 중에서 아이디만 알면 그 회원이 누구인지를 알 수가 있다.

그것이 바로 기본키(PRIMARY KEY)이다.

중복 X, NULL X

기본키로 생성한 것은 자동으로 '클러스터 인덱스'가 생성이 됩니다.

한 테이블에는 하나의 기본키(PRIMARY KEY)만 등록이 가능하다.

열들을 줄여서 다시 실습을 해보도록 하겠습니다.

실습

#1번째 방식

member, buy 테이블을 다시 생성하고

열들을 줄여서 생성한다.

그리고 DESCRIBE member; 명령어를 통해서 

result 창에서 member테이블을 파악할 수 있다.

#2번째 방식

member 테이블을 다시 생성해본다.

이번에는 PRIMARY KEY 문법을 다른 문법으로 바꿔서 사용을 해본다.

PRIMARY KEY(mem_id)

이 결과는 첫번째 방식과 동일하다.

#3번째 방식

member 테이블에 PRIMARY KEY를 

ALTER TABLE 명령문을 통해서

ADD CONSTRAINT PRIMARY KEY (mem_id);

를 추가할 수 있다.

#4번째 방식

주로, PRIMARY KEY 추가를 위해서는 첫번째 방식을 주로 사용을 한다.

하지만 다른 곳에서는 다른 방식을 선호할 수도 있기 때문에

기본키 제약조건 설정 방식을 모두 소개한다.

외래키 제약조건

회원 테이블을 기준 테이블이라고 하고,

구매 테이블을 참조 테이블이라고 한다.

이와 같은 방식으로 외래키 제약조건을 만들고 데이터의 무결성은 만든다.

그래서 조인을 할 수 있도록 설계가 되어있다.

#1번째 방식

다음과 같이 테스트해보기 위해

다시 member, buy 테이블을 DROP하고 CREATE한다.

buy 테이블에서 FOREIGN KEY(mem_id) REFERENCES member(mem_id);

#2번째 방식

user_id 라는 컬럼명으로 FOREIGN KEY를 만들어보았다.

user_id 처럼 이렇게 꼭 mem_id처럼 이름이 같지 않아도 무방하다. 하지만 권장하지는 않는다.

동일하게 쓰는 것을 권장을 합니다.

#3번째 방식

ALTER TABLE을 통해서 이후에 

ADD CONSTRAINT FOREIGN KEY(mem_id) REFERENCES member(mem_id);

이렇게 외래키를 추가할 수 있다.

만약에 기준 테이블의 아이디를 BLK에서 PINK로 바꾸었다.

그러면 이전 참조 테이블의 아이디가 BLK인 행들은 어떻게 될까?

해당 화면과 같이 member, BLK를 하나 추가 시켰다.

그리고 buy에 지갑과 맥북을 추가 시켰다.

이렇게 회원을 한 명 등록하고, 두개의 물품을 구매를 했다.

구매목록을 확인을 하였다.

내부 조인을 통해서 회원 정보도 일부 같이 확인을 할 수 있다.

이번에는 아이디를 바꾸어 보겠다.

오류가 발생하는 것을 확인할 수 있다.

DELETE문도 적용이 안 된다.

이번에는 회원 탈퇴를 해볼까요?

근데 회원이 탈퇴도 되지 않아요.

왜냐하면 그냥 회원을 삭제해버리면 연관이 있는 구매 테이블에 있던 데이터가 결함이 있는 데이터가 된다.

기준 테이블의 데이터를 수정할 때

자동으로 참조 테이블도 변경된다면 문제가 없지 않을까요?

ON UPDATE CASCADE 그리고 ON DELETE CASCADE를 사용할 수 있다.

ALTER TABLE 시

ADD CONTRAINT 를 통해서

FOREIGN KEY(mem_id) REFERENCES member(mem_id)

ON UPDATE CASCADE 

ON DELETE CASCADE

를 추가해줄 수 있다.

그러면 회원 테이블을 UPDATE 하였는데도 참조 테이블인 구매 테이블의 정보들도 모두 UPDATE 된 것을 확인할 수 있다.

또한 회원 테이블을 DELETE 시 참조 테이블에서도 DELETE가 이루어지는 것을 볼 수 있다.

기타 제약조건

고유키는 중복을 허용하지 않지만, NULL은 허용한다.

NULL은 여러값이 입력되어도 괜찮다.

에이핑크를 INSERT할 때 오류가 발생하는 것을 볼 수 있다.

고유키 제약조건을 범했기 때문이다.

체크 제약조건

예를 들어 연락처의 국번에 02, 031, 041, 055 중 하나만 입력되도록 해야 할 때

또는 평균키는 100이상의 값만 입력되도록 해야 할 때

CHECK(조건)을 사용한다.

CHECK 제약조건을 범하였으므로

트와이스는 INSERT되지 않은 것을 확인할 수 있다.

기본값 정의

입력을 안 하면 자동으로 넣어줘 하는 값이다.

ALTER TABLE member 

ALTER COLUMN phone1 SET DEFAULT '02';

를 통해서 member 테이블의 기본값 제약조건에 '02'를 추가한다.

INSERT문으로 입력할 때는 반드시 default로 입력을 한다.

그 결과로 우주소녀, 160과 02가 입력된 것을 볼 수 있다.

널 값 허용

예를 들어 이름 없는 사람은 없다.

회원가입했는데 이름이 없으면 이름을 못 찾으니까 반드시 NOT NULL을 추가해서 

NULL값 입력을 방지할 수 있다.

감사합니다.

https://www.youtube.com/watch?v=BUHj-behLyc&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=14