[DB] KEY의 종류

ho's·2022년 7월 12일

db key 반정규화 정규화

🥏 데이터베이스 키의 개념과 종류.

데이터 베이스 키의 종류를 알아보자.
유투브에 대멀쌤이라는 영상을 보게 되었다.

후보키는 외 기본키가 대슈?

🔑 키의 종류

일단, 키의 종류에는 5가지가 있고, 아래와 같은 구조를 따른다.

후보키(Candidate Key)

릴레이션(테이블)에서 각 튜플을 유일하게 식별할 수 있는 속성이나 속성의 집합을 말한다.
후보키가 될 수 있는 조건은 유일성과 최소성을 모두 만족해야 한다.
기본키(Primary Key)

기본키(Primary Key)

기본키는 후보키 중에서 행을 식별하기 위해 특별히 선택된 키를 말한다.
기본키는 중복될 수 없으며, NULL값이 올 수 없다.
유일성과 최소성을 만족해야 한다.
UNIQUE는 한 테이블의 여러 필드에 설정할 수 있다.
PRIMARY KEY는 테이블당 오직 하나의 필드에만 설정할 수 있다.
PRIMARY KEY 제약 조건은 테이블의 데이터를 쉽고 빠르게 찾도록 도와주는 역할을 한다.

대체키(Alternate key)

데이터 베이스에서 레코드(DB테이블에서 가로방향 한 줄)를 유일하게 식별할 수 있는 후보키 중에서 기본키(Primary Key)로 선택되지 않고 남은 키(Key)를 의미한다. 보조키라고도 부른다.

외래키(Foreign Key)

두 테이블을 서로 연결하는데 사용되는 키
외래키가 포함된 테이블을 자식 테이블이라고 하고, 외래키 값을 제공하는 테이블을 부모 테이블이라 한다.

외래키 예제

create table department(
	id int auto_increment primary key,
    name varchar(20) not null,
    code char(13) not null unique key
);

create table employee(
	id int auto_increment primary key,
    name varchar(20) not null,
    code char(13) not null unique key,
    dept_id int,
    foreign key (dept_id) references department(id)
);

위와 같이 부모,자식 관계가 형성된 것을 알 수 있다.

슈퍼키(Super Key)

데이터베이스에서 관계(테이블)의 행을 고유하게 식별할 수 있는 속성 또는 집합을 의미한다. 슈퍼 키는 대상관계의 모든 속성이 함수 종속하는 속성의 집합으로 정의할 수 있다.

🥏 제약조건

제약조건이란 데이터의 무결성을 지키기 위한 제한된 조건을 의미한다.
특정 데이터를 입력할 때 무조건적으로 입력되는 것이 아닌, 어떤 조건을 만족했을 때에 입력되도록 제약할 수 있다.
MySQL은 데이터의 무결성을 위해서 다음의 5가지 제약조건을 제공한다.

PRIMARY KEY 제약 조건
FOREIGN KEY 제약 조건
UNIQUE 제약 조건
CHECK 제약 조건
DEFAULT 정의
NULL 값 허용

🏉 PK 제약조건

기본 키에 입력되는 값은 중복될 수 없으며, NULL값이 입력될 수 없다.
ex) 인터넷 쇼핑몰에서는 회원 테이블의 기본 키를 회원 아이디로 설정했을 것이다. 아이디가 중복되거나 아이디 없이 회원가입을 할 수 있는경우는 없기 때문이다.
기본키는 1개만 갖는다.

💩 방법1

CREATE TABLE (테이블명) (
	칼럼명   자료형  제약조건 PRIMARY KEY,
);

위와 같이 KEY가 설정된 것을 알 수 있다.

또는 PRIMARY KEY를 맨 뒤에 작성하여 PK를 설정하는 방법도 있다.

CREATE TABLE member
(	mem_id CHAR(8) NOT NULL,
	mem_name	VARCHAR(10) NOT NULL,
    height		TINYINT UNSIGNED NULL,
    PRIMARY KEY (mem_id)
);

DESCRIBE member;

💩 방법2

PK의 이름을 바꾸고 싶다면 아래와 같이 입력하면 된다.

CREATE TABLE userTBL(
	userID CHAR(8) NOT NULL,
    name	VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    CONSTRAINT PRIMARY KEY '변경될 PK' (userID) 
);

위와 같은 결과가 나타내어 진다.

MYSQL은 Primary Key로 지정하면 항상 키 이름을 'PRIMARY'로 보여준다.
하지만 Foreign Key는 하나의 테이블에 여러개가 생성될 수 있으므로 이름을 지정해서 관리하는 것이 편하다.

테이블의 지정된 키를 보는 쿼리문은 아래와 같다.

SHOW KEYS FROM 테이블명;

💩 방법3

DROP TABLE IF EXISTS userTBL;
CREATE TABLE userTBL
(
	userID CHAR(8) NOT NULL,
    name VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL
);
ALTER TABLE userTBL
	ADD CONSTRAINT PK userTBL userID
    	PRIMARY KEY(userID);

해석해보자.

ALTER TABLE usertbl
usertbl을 변경하자
ADD CONSTRAINT PK_usertbl_userID
제약 조건을 추가하자. 추가할 제약조건의 이름은 'PK_usertbl_userID'이다.
PRIMARY KEY(userID)
추가할 제약 조건은 기본 키 제약조건이다. 그리고 제약 조건을 설정할 열은 userID열이다.

🏀 FK 제약조건

두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장해 주는 역할
외래키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존하게 된다.
외래키 테이블에 데이터를 입력할 때에는 꼭 기준 테이블을 참조해서 입력하므로 기준 테이블에 이미 데이터가 존재해야 한다.

💩 방법1

DROP TABLE IF EXISTS buyTBL, userTBL;
CREATE TABLE userTBL
(userID CHAR(8) NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL
);

CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  userID CHAR(8) NOT NULL,
  prodName CHAR(6) NOT NULL,
  FOREIGN KEY(userID) REFERENCES userTBL(userID)
  );

중요한 쿼리는

FOREIGN KEY(userID) REFERENCES userTBL(userID)

첫번째 userID는 buTBL에 있는 userID이고, userTBL에 있는 userID를 참조한다.

외래 테이블 buyTBL의 열에서 참조하는 기준 테이블(userTBLE)의 열(userID)는 기본 키로 설정되어 있는 것이 확인된다.
만약 기준 테이블이 Primary Key 또는 Unique가 아니라면 외래 키 관계는 성립되지 않는다.

💩 방법2

DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
(	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	USERID CHAR(8) NOT NULL,
    prodName CHAR(6) NOT NULL,
    CONSTRAINT FK_userTBL_buyTBL FOREIGN KEY(userID) REFERENCES userTBL(userID)
);

💩 방법3

DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
(	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	userID CHAR(8) NOT NULL,
    prodName CHAR(6) NOT NULL
);
ALTER TABLE buyTBL
	ADD CONSTRAINT FK_userTBL_buyTBL
    FOREIGN KEY (userID)
    REFERENCES userTBL(userID);

ALTER TABLE buytbl
buytbl을 수정한다
ADD CONSTRAINT FK_usertbl_buytbl
제약 조건을 더한다. 제약 조건 이름은 'FK_usertbl_buytbl'로 명명한다.
FOREIGN KEY (userID)
외래 키 제약 조건을 buytbl의 userID에 설정한다.
REFERENCES usertbl(userID)
참조할 기준 테이블은 usertbl 테이블의 userID열 이다.

🏉 FK(외래키) 옵션

💩 기준열의 정보가 변경될 경우

아래와 같은 테이블이 있다고 하자.

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height	TINYINT UNSIGNED NULL
);

CREATE TABLE buy(
	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    mem_id CHAR(8) NOT NULL,
    prod_name CHAR(6) NOT NULL,
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

member테이블에서 mem_id를 바꿀 경우 어떻게 될까?
실행해보자.

UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';

위의 쿼리문을 실행 시키면 다음과 같은 오류가 발생한다.

PK,FK 관계가 설정되어 있어 임의로 수정 및 삭제할 수 없다는 내용이다.

마찬가지로 아래와 같이 mem_id = 'BLK' 인 정보를 지우는 쿼리문을 작성하고 실행시켜보자.

DELETE FROM member WHERE mem_id = 'BLK';

마찬가지로 아래와 같이 오류가 뜬다.

위와 같은 무결성을 해결하기 위한 방법으로, 아래와 같은 기능을 추가해서 실행 시킬 수 있다.

💎 ON UPDATE CASCADE, ON DELETE CASCADE

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(	num	INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	mem_id	CHAR(8) NOT NULL,
    prod_name	CHAR(6) NOT NULL
);

ALTER TABLE buy
	ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

위의 sql쿼리를 실행 한 후,

UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';

를 실행시키면

위와 같이 업데이트가 오류없이 잘 실행이 된다.

🔑 고유키 제약조건

고유키 제약조건은 '중복되지 않는 유일한 값'을 입력해야한다는 것이다.
기본키 제약조건과 다르게 NULL 값을 허용 한다.
기본키는 한개만 설정해야 하지만, 고유키는 여러개를 설정해도 된다.

🏸 고유키 예제

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name	VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL,
email	CHAR(30) NULL UNIQUE
);

위와 같은 테이블을 만든 후, 데이터를 넣어보자.

INSERT INTO member VALUES('BLK', '블랭핑크', 163,'pink@gmail.com');
INSERT INTO member VALUES('TWC' '트와이스', 167, NULL);
INSERT INTO member VALUES('APN', '에이핑크', 164,'pink@gmail.com');

실행 시킨다면 아래와 같은 오류가 뜬다.

🔑 체크 제약조건

입력되는 데이터를 점검하는 기능을 한다.
ex) 평균 키에 마이너스 값이 입력되지 않도록 하는 것

🧿 예제

DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height	TINYINT	unsigned NULL CHECK(height >= 100),
phone1	CHAR(3)	NULL
);

INSERT INTO member VALUES('BLK', '블랭핑크', 163, NULL);
INSERT INTO member VALUES('TWC', '트와이스', 99, NULL);

위의 쿼리문을 실행 시키면 아래와 같은 오류가 발생한다.
CHECK조건을 걸었기 때문이다.

🧿 예제2

ALTER를 이용해 제약조건을 추가해 보자.

ALTER TABLE member
	ADD CONSTRAINT
    CHECK (phone1 IN('02', '031', '032', '054', '055', '061'));

위와 같은 제약조건을 설정 한 후, 아래 쿼리문을 실행시키면

INSERT INTO member VALUES('TWC', '트와이스', 176 , '02');
INSERT INTO member VALUES('OMY', '오마이보이', 167 , '010');

위와 같은 오류가 발생한다.

🎨 DEFAULT 정의

DROP TABLE IF exists member;
CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
  mem_name VARCHAR(10) NOT NULL,
  height	TINYINT UNSIGNED NULL DEFAULT 160,
  phone1	CHAR(3)	NULL
);

ALTER TABLE member
	ALTER COLUMN phone1 SET DEFAULT '02';
    
INSERT INTO member VALUES('RED' , '레드벨벳', 161, '054');
INSERT INTO member VALUES('SPC', '우주소녀', default, default);

🔮 NULL 값 허용

NULL 값을 허용하려면 생략하거나 NULL을 사용한다.
사용하지 않으려면 NOT NULL을 사용한다.
PK가 설정된 열에서는 NULL값이 있을 수 없으므로 자동으로 NOT NULL로 인식된다.

📀 개체 무결성(기본키와 관련)

기본키는 NULL값이 올 수 없으며, 중복될 수 없음을 나타내는 제약조건이다.
개체 무결성은 개체를 식별하기 위해서 오류가 없도록 하기 위한 제약조건이다.

📀 참조 무결성(외래키와 관련)

외래키는 NULL 값이 올 수 없으며, 참조 릴레이션(테이블)의 기본키와 같아야 하는 제약조건으로 테이블 참조 시 오류가 없도록 하기 위한 제약조건이다.
아래 [교수] 테이블의 '학과번호' 에서 참조하는 [학과]티이블의 '학과번호'의 값은 반드시 존재해야 한다.

즉, 참조 무결성은 외래키가 기본키와 같아야 한다는 것을 의미한다.

참고 영상 및 글

ho's

그래야만 한다

이전 포스트

velog 따라하기 (1)

다음 포스트

[DB] KEY의 종류

🥏 데이터베이스 키의 개념과 종류.

🔑 키의 종류

후보키(Candidate Key)

기본키(Primary Key)

대체키(Alternate key)

외래키(Foreign Key)

슈퍼키(Super Key)

🥏 제약조건

🏉 PK 제약조건

💩 방법1

💩 방법2

💩 방법3

🏀 FK 제약조건

💩 방법1

💩 방법2

💩 방법3

🏉 FK(외래키) 옵션

💩 기준열의 정보가 변경될 경우

💎 ON UPDATE CASCADE, ON DELETE CASCADE

🔑 고유키 제약조건

🏸 고유키 예제

🔑 체크 제약조건

🧿 예제

🧿 예제2

🎨 DEFAULT 정의

🔮 NULL 값 허용

📀 개체 무결성(기본키와 관련)

📀 참조 무결성(외래키와 관련)

velog 따라하기 (1)

[알고리즘] 최대점수 구하기(DFS)

0개의 댓글