[SQL] PRIMARY KEY, FOREIGN KEY

허재훈·2023년 4월 28일
sql
0

SQL

목록 보기
13/17
post-thumbnail

1. 실습환경 만들기

실습할 데이터베이스로 이동

  • zerobase 사용 (이동)
USE zerobase;

2. PRIMARY KEY (기본키)

• 테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL 값을 포함할 수 없음
• 테이블 당 하나의 기본키를 가짐
• 기본키는 컬럼 하나로 지정할 수도 있고,
여러 개의 컬럼을 하나의 기본키로 지정할 수도 있다.

PRIMARY KEY 생성 문법 1

CREATE TABLE tablename
(
    column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    ...
    CONSTRAINT constraint_name # 생략가능
     PRIMARY KEY (column1, column2, ...)
);

PRIMARY KEY 생성 예제

  • 예제 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)
);

PRIMARY KEY 삭제 문법

# KEY 는 하나밖에 없기 때문에 따로 이름을 써서 지정하지 않는다.
ALTER TABLE tablename
DROP PRIMARY KEY;

PRIMARY KEY 삭제 예제

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

ALTER TABLE person
DROP PRIMARY KEY;

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

ALTER TABLE animal
DROP PRIMARY KEY;

PRIMARY KEY 생성 문법 2

  • 이미 만들어져있는 TABLE에 PRIMARY KEY 생성하는 방법

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

PRIMARY KEY 생성 예제

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

ALTER TABLE person
ADD PRIMARY KEY (pid);  # id가 아니라 pid 인듯

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

# CONSTRAINT PK_animal 생략가능
ALTER TABLE animal
ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);

3. FOREIGN KEY (외래키)

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

FOREIGN KEY생성 문법 1

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
     FOREIGN KEY (column3, column4, ...) 
     REFERENCES REF_tablename(REF_column)
);
# FOREIGN KEY 는 연결되는 다른 참조 테이블이 필요하다 
# 그게 REFERENCES
#
# 결국 제일 위 tablename 과 REF_tablename이 연결되고,
# FOREIGN KEY 로 지정된 컬럼과 REF_column이 연결된다

FOREIGN KEY 생성 예제

  • 예제 1. CREATE TABLE 에서 FOREIGN KEY를 지정하는 경우

  • orders 테이블의 pid 는 person 테이블의 pid 를 참조한다
    (즉 두 pid 가 연결된다)

  • FOREIGN KEY 값으로 MUL 로 표시된다

  • 어떤 한 테이블이 참조하는 테이블이 여러 테이블일 수 있다.

  • 그래서 FOREIGN KEY 는 여러개 생성될 수 있는데,

  • DROP 등을 할때에는 COSNTRAINT 명을 알아야 한다.

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

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

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

COSNTRAINT 확인 문법

  • 자동 생성된 CONSTRAINT 를 확인하는 방법
  • 어떤 한 테이블이 참조하는 테이블이 여러 테이블일 수 있다.
  • 그래서 FOREIGN KEY 는 여러개 생성될 수 있는데,
  • DROP 등을 할때에는 COSNTRAINT 명을 알아야 한다.

SHOW CREATE TABLE tablename;
  • 예제.

SHOW CREATE TABLE job;

FOREIGN KEY 삭제 문법

  • COSNTRAINT 이름이 FOREIGN KEY 이구나?
ALTER TABLE tablename
DROP FOREIGN KEY;
  • 예제

ALTER TABLE orders
DROP FOREIGN KEY FK_person;
  • 아래를 보면 desc orders; 했을 때 MUL 이 그대로 남아있는데,
  • show create table orders; 해서 보면
  • FK_person 이 FOREIGN KEY 가 아니라 그냥 KEY 이다.
  • 즉 FK_person 는 FOREIGN KEY 속성은 지워지고 KEY 속성만 남아있는 것이다(FOREIGN KEY 속성은 삭제됨)

FOREIGN KEY 생성 문법 2

  • 이미 생성된 TABLE에 FOREIGN KEY 추가하기

  • Table 이 생성된 이후에도 ALTER TABLE 을 통해 FOREIGN KEY 를 지정할 수 있다

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

FOREIGN KEY 생성 예제 2

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

4. FOREIGN KEY 예제

  • police_station 과 crime_status 테이블 사이에 관계 (Foreign Key)를 설정해 봅시다.

  • AWS RDS(database-1) 의 zerobase 에서 작업합니다.

1. 분석

  • police_station.name 과 crime_status.police_station 을 매칭하여 관계를 맺도록 하겠습니다.

# police_station 에서 distinct name count
select count(distinct name) from police_station;
>> 31

# crime_status 에서 distinct police_station count
select count(distinct police_station) from crime_status;
>> 31

  • 경찰서 이름이 각 테이블에서 표시되는 형식이 다릅니다.

select distinct name from police_station limit 3;

select distinct police_station from crime_status limit 3;
  • crime_status.police_station 을 police_station.name 과 똑같이 만들어서 비교하도록 합니다.
  • p.name 의 이름과
  • concat('서울', c.police_station, '경 찰 서') 을 비교해서
  • 매칭되는 걸 가져온다
  • group by 로 중복되지 않게 나옴
  • 왼쪽 컬럼은 c.police_station
  • 오른쪽 컬럼은 p.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;

2. Primary Key 설정

  • police_station.name 을 Primary Key 로 설정합니다.

ALTER TABLE police_station
ADD PRIMARY KEY (name);

3. Foreign Key 설정

  • crime_status 테이블에 Foreign Key 로 사용할 Column 추가

ALTER TABLE crime_status
ADD COLUMN reference VARCHAR(16);

  • Foreign Key 생성
  • reference 컬럼을 Foreign Key 지정

ALTER TABLE crime_status
ADD FOREIGN KEY (reference) REFERENCES police_station(name);

  • Foreign Key 값 Update
  • c.reference 를 p.name 으로 업데이트
  • 조건 : p.name 이 concat('서울', c.police_station, '경찰서')과 같으면 업데이트

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;

4. JOIN

  • Foreign Key 를 기준으로 두 테이블을 연관시켜 검색할 수 있다.

  • crime_status 가 police_station 을 참조하고 있다.

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 테이블을 생성하세요.

  1. 생성한 테이블의 PRIMARY KEY 를 삭제하세요.
  2. 생성한 테이블의 FOREIGN KEY 를 삭제하세요.
  3. study 테이블의 patient_id 를 person 테이블의 pid 와 연결된 FOREIGN KEY 로 등록하세요.
  4. study 테이블의 study_id 를 PRIMARY KEY로 등록하세요.
  • 문제 1. 다음과 같이 study_id 가 PRIMARY KEY, patient_id 가 person 테이블의 pid 와 연결된 FOREIGN KEY 로 지정된 study 테이블을 생성하세요.

(1) 기본 세팅

cmd 창에서
cd Documents/sql_ws
mysql -u root -p
use zerobase
show tables;

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

  • 문제 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);

위 글은 제로베이스 데이터 취업 스쿨의 강의자료를 참고하여 작성되었습니다.

profile
허재훈
허재
이전 포스트

[SQL] Python with MySQL

다음 포스트

[SQL] Aggregate Functions (집계함수)

0개의 댓글