[2023.12.01] SQL_PRIMARY KEY, FOREIGN KEY

- PRIMARY KEY(기본키)

- 테이블의 각 레코드를 식별

- 중복되지 않은 고유값을 포함

- NULL값을 포함할 수 없음

- 테이블 당 하나의 기본키를 가짐(칼럼 하나 혹은 여러개의 칼럼을 하나의 기본키로 지정가능)

- PRIMARY KEY 생성문법1

CREATE TABLE tablename
(
    column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    ...
    CONSTRAINT constraint_name
    PRIMARY KEY (column1, column2, ...)
);

예) 하나의 컬럼을 기본키로 설정하는 경우

CREATE TABLE peroson
(
    pid int NOT NULL,
    name varchar(16),
    age int, 
    sex char, 
    PRIMARY KEY (pid)
);

예) 여러개의 컬럼을 기본키로 설정하는 경우

CREATE TABLE animal
(
    name varchar NOT NULL,
    type varchar NOT NULL,
    age int, 
    PRIMARY KEY (name, type)
); 
= 두개가 하나의 pri라는 뜻

- PRIMARY KEY 삭제 문법

ALTER TABLE tablename
DROP PRIMARY KEY;

테이블당 하나만 있기 때문에 별도로 이름 안 줘도 삭제됨

• 예) 하나의 칼럼이 기본키로 설정된 경우

ALTER TABLE person
DROP PRIMARY KEY;

• 예) 여러개의 칼럼이 기본키로 설정된 경우(삭제하는 방법은 동일)

ALTER TABLE animal
DROP PRIMARY KEY;

- PRIMARY KEY 생성 문법2

ALTER TABLE tablename
ADD PRIMARY KEY(column1, column2, ...);

• 예) 하나의 칼럼을 기본키로 지정하는 경우

ALTER TABLE person
ADD PRIMARY KEY(pid);

• 예) 여러개의 칼럼을 기본키로 지정하는 경우

ALTER TABLE animal
ADD CONSTRAINT PK_animal PRIMARY KEY(name, type);

# CONSTRAINT PK_animal 생략가능 - > 생략하게 되면 자동생성됨

- FOREIGN KEY(외래키)

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

CREATE TABLE tablename

(
    column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    column3 datatype,
    column4 datatype,
    ...
    CONSTRAINT constraint_name
    PRIMARY KEY (column1, column2, ...)
    CONSTRAINT constraint_name
    PRIMARY KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);

• 예) CREATE TABLE에서 FOREIGN KEY를 지정하는 경우

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)
);

• 예) CREATE TABLE에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT를 생략할 수 있다

CREATE TABLE job
(
    jid int NOT NULL,
    namevarchar(16),
    pid int,
    
    PRIMARY KEY (jid),
    FOREIGN KEY(pid) REFERENCES person(pid)
);

- CONSTRAINT 확인 문법

- 자동생성된 CONSTRAINT를 확인하는 방법

SHOW CREATE TABLE tablename;

primary key와는 달리, foreign key는 여러개 있을 수 있다.
= 한 테이블이 참조하는 테이블이 여러개일 수 있다.
생략하면 이름이 자동생성되는데, 이름을 알아야 drop등을 할 수 있어서 이름을 알아보기 위한 코드

- FOREIGN KEY 삭제 문법

ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;

여러개라서 이름을 줘야함

• 예)

ALTER TABLE tablename
DROP FOREIGN KEY FK_person;

-> 레퍼런스 관계는 깨지고 key값만 남아있게 됨

- FOREIGN KEY 생성 문법2

- TABLE이 생성된 이후에도 ALTER TABLE을 통해 FOREIGN KEY를 저장할 수 있다.

ALTER TABLE tablename
ADD FOREIGN KEY(column) REFERENCES REF_tablename(REF_column);

• 예)

ALTER TABLE orders
ADD FOREIGN KEY(pid) REFERENCES person(pid);

- FOREIGN KEY 예제

police_station과 crime_status 테이블 사이의 관계(foreign key)를 설정해봅시다.
AWS RDS(database-1) 의 zerobase에서 작업합니다.

• 분석
  : police_station.name과 crime_status.police_station을 매칭해 관계를 맺도록 함

select count(distinct name) from police_station;
select count(police_station) from crime_status;

• 분석2
  : 경찰서 이름이 각 테이블에서 표시되는 형식이 다름

select distinct name from police_station limit 3;
select distinct police_station from crime_status limit 3;

• 분석3
  : crime_status.police_station을 police_station.name과 같이 만들어서 비교

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;

primary key 설정

• police_station.name을 primary key로 설정

alter table police_station
add primary key(name);

• crime_status테이블에 foreign key로 사용할 칼럼 추가(매칭이 안돼서 새로 추가 - 풀주소로)

alter table crime_status
add column reference varchar(16);

• foreign key 생성

alter table crime_status
add foreign key(reference) 
reference police_station(name);

• foreign key값 update

UPDATE crime_status c, police_station p
SET c.reference = p.name
where p.name like concat('서울', c.police_station, '경찰서')
;

->
select distinct police_station, reference FROM crime_status;

• JOIN
  = FOREIGN KEY를 기준으로 두 테이블을 연관시켜 검색할 수 있다

select c.police_station, p.address
from crime_status c, police_station p
where c.reference = p.name 
group by c.police_station;

---

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

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)
    -> );
    
 desc study;
 
 show create table study;

문제2. 생성한 테이블의 primary key를 삭제하세요

alter table study
    -> drop primary key;

문제3. 생성한 테이블의 foreign key를 삭제하세요

 alter table study
    -> drop foreign key FK_study;

문제4. study 테이블의 patient_id를 person테이블의 pid와 연결된 foreign key로 등록하세요

alter table study
    -> add foreign key(patient_id) references person(pid);

문제5. study테이블의 study_id를 primary key로 등록하세요

 alter table study
    -> add primary key (study_id);