SQL_5 (primary, foreign key)

🙅‍♂️휴대폰으로 볼 때 혹시 글자나 숫자가 화면에 다 안나오면, 휴대폰 가로로 돌리시면 됩니다

<목차>

1. primary key(기본키)
2. foreign key(외래키)
3. 예제
4. 실습

이번 실습은 sql 로컬환경으로 접속합니다

# mysql 로컬환경
cd C:\Users\withj\Documents\sql_ws
mysql -u root -p
use zerobase;

1. primary key(기본키)

• 테이블의 각 레코드를 식별
• 중복되자 않은 고유값을 포함
• NULL 값을 포함 못함
• 테이블 당 1개의 기본키
  

1-1) PRIMARY KEY 지정

CREATE TABLE 테이블명
(
    컬럼1 datatype NOT NULL,
    컬럼2 datatype NOT NULL,
    ...
    CONSTRAINT 프라이머리 키 별칭     # -------- 생략 가능 --------------------
    PRIMARY KEY (컬럼1, 컬럼2, ...)
);
⬇️
⬇️


#-----------------  1개의 컬럼을 기본키로 지정하는 경우 --------------------------- 
CREATE TABLE person
(
    pid int NOT NULL,
    name varchar(16),
    age int,
    sex char,
    PRIMARY KEY (pid)
);


#-----------------  2개의 컬럼을 기본키로 지정하는 경우 ---------------------------
CREATE TABLE animal
(
    name varchar(16) NOT NULL,
    type varchar(16) NOT NULL,
    age int,
    PRIMARY KEY (name, type)
);

1-2) PRIMARY KEY 삭제

ALTER TABLE 테이블명
DROP PRIMARY KEY;
⬇️
⬇️
# ex-1)
ALTER TABLE person
DROP PRIMARY KEY;
--------------------------------
# ex-2)
ALTER TABLE animal
DROP PRIMARY KEY;

1-3) 이미 만들어진 테이블에 PRIMARY KEY 생성

ALTER TABLE 테이블명
ADD PRIMARY KEY (컬럼1, 컬럼2, ...)
⬇️
⬇️
#-----------------  1개의 컬럼을 기본키로 지정하는 경우 --------------------------- 
ALTER TABLE person
ADD PRIMARY KEY (pid);

#-----------------  2개의 컬럼을 기본키로 지정하는 경우 --------------------------- 
# 키 묶음 이으로 PK_animal로 설정
ALTER TABLE animal
ADD CONSTRAINT PK_animal
PRIMARY KEY (name, type);

2. foreign key(외래키)

• 한 테이블을 다른 테이블과 연결해주는 역할
• 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)
  

2-1) 왜래키 생성

use zerobase;

# ---------------- 기본형 -----------------------------
CREATE TABLE 테이블명
(
    컬럼1 datatype NOT NULL,
    컬럼2 datatype NOT NULL,
    컬럼3 datatype,
    컬럼4 datatpye
    ...
    CONSTRAINT 기본키 묶음 이름
    PRIMARY KEY (컬럼1, 컬럼2, ...),
    CONSTRAINT 외래키 묶음 이름
    FOREIGN KEY (컬럼3, 컬럼4, ...) REFERENCES 참조 테이블명(참조 컬럼)
);
⬇️
⬇️


# -------------------------- 예시1 --------------------------------
# order 테이블의 pid는 person테이블의 pid를 참조한다
CREATE TABLE orders
(
    oid int NOT NULL,
    order_no varchar(16),
    pid int,
    PRIMARY KEY (oid),
    CONSTRAINT FK_person
    FOREIGN KEY (pid) REFERENCES person(pid)
);
# 끝나고 desc orders; 
# 확인결과 --> oid가 기본키, pid가 외래키(MUL)



# -------------------------- 예시2 --------------------------------
# 테이블 생성에서 외래키 지정할 때, constraint 생략 가능
CREATE TABLE job
(
    jid int NOT NULL,
    name varchar(16),
    pid int,
    PRIMARY KEY (jid),
    FOREIGN KEY (pid) REFERENCES person(pid)
);
# 끝나고 desc orders; 
# 확인결과 --> jid가 기본키, pid가 외래키(MUL)

# 자동생성된 CONSTRAINT 확인
SHOW CREATE TABLE 테이블명;
⬇️
show create table job;
# 외래키의 별칭 확인
#➡️ CONSTRAINT `job_ibfk_1`

2-2) 외래키 삭제

ALTER TABLE 테이블명 DROP FOREIGN KEY 외래키명;
⬇️
⬇️
ALTER TABLE orders drop foreign key FK_person;

# desc orders; 
# 확인결과 잘 모르겠음 

# show create table orders;
constraint 해제됨 --> KEY `FK_person` (`pid`)

테이블 생성 후 외래키 지정

ALTER TABLE 테이블명
ADD FOREIGN KEY (컬럼1) REFERENCES 참조테이블명(참조컬럼);
⬇️
⬇️
# order 테이블의 외래키를 pid로 지정할거고 person 테이블의 pid를 참조할 것이다
ALTER TABLE orders
ADD FOREIGN KEY (pid) REFERENCES person(pid);

# show create table job;
확인 결과 --> CONSTRAINT `job_ibfk_1`(외래키 별칭)

3. 예제

police_station과 crime_status 테이블 사이에 관계를 보자
AWS RDS(databse-1)의 zerobase에서 작업합니다

cd C:\Users\withj\Documents\sql_ws
aws 로그인
use zerobase;
show tables;



# ---------------- 테이블 상태 분석 ------------------------------
# police_station 테이블의 컬럼확인
desc police_station;

# 중복제외 한 컬럼의 고유값 갯수 확인
select count(distinct 컬럼명) from 테이블명;
⬇️
⬇️
# police_station 테이블의 name 컬럼 중복 제거하고 몇개인지 확인 --> 31
select count(distinct name) from police_station;

# select count(name) from police_station; --> 124

# crime_status 테이블의 police_station 컬럼 중복 제거하고 몇개인지 확인
select count(distinct police_station) from crime_status;



# --------------- 테이블이 서로 매칭되는지 확인 -------------------------
# police_station 테이블에서 중복되지 않는 name 컬럼의 고유값 상위 3개
select distinct name from police_station limit 3;

# crime_status 테이블에서 중복되지 않는 police)station 컬럼의 고유값 상위 3개
select distinct police_station from crime_status limit 3;



# --------------------------- 도표 시각화 -------------------------------
# crime_status 테이블에서 police_station 컬럼을
# police_station 테이블에서 name 컬럼을
# 이어 붙이기
# 2개로 그룹화

select c.police_station, p.name
from crime_status c, police_station p
where p.name like concat ('서울', c.police_station, '경찰서')
group by c.police_station, p.name;

4. 실습

4-1) 다음과 같이 study_id가 PRIMARY KEY, patient_id가 person 테이블의 pid와 연결된 FOREIGN KEY로 지정된 study 테이블을 생성하세요

# mysql 로컬환경
cd C:\Users\withj\Documents\sql_ws
mysql -u root -p
use zerobase;



----------------------------------------------------------
create table study
(
study_id int not null,
study_Date date,
study_time time,
patient_id int,
primary key (study_id),
constraint FK_study foreign key (patient_id) references person (pid)
);

# 테이블 확인
show create table study;

4-2) 생성한 테이블의 PRIMARY KEY를 삭제하세요

# 테이블 확인
desc study;

# 키 삭제
alter table study
drop primary key;

# 다시 테이블 확인
desc study;

4-3) 생성한 테이블의 FOREIGN KEY를 삭제하세요

alter table study
drop foreign key FK_study;

# 테이블 정보 확인 
# 맨 아랫줄에 foreign key 연결 해제된 것 확인 가능
show create table study;

4-4) study 테이블의 patient_id를 person 테이블을 pid와 연결된 FOREIGN KEY로 등록하세요

# 테이블 확인
desc study;

# 외래키 생성(person 테이블의 pid컬럼을 참조) 
alter table study
add foreign key (patient_id) references person (pid);

# 테이블 정보 확인 
# 맨 아랫줄에 foreign key 연결됨
show create table study;

4-5) study 테이블의 study_id를 PRIMARY KEY로 등록하세요

# 테이블 확인
desc study;

# 고유키 생성
alter table study
add primary key (study_id);

# 다시 테이블 확인
desc study;