[SQL] Primary Key, Foregin Key

실습환경
localhost
zerobase 사용

use zerobase;

Primary Key(기본키)

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

Primary Key 생성 문법1

새로운 테이블을 생성하면서 primary key를 지정

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

예제 1-1

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

테이블에 key 값에 PRI가 있다면 그 값이 테이블의 primary key이다.

예제 1-2
여러개 컬럼을 기본키로 설정하는 경우

CREATE TABLE animal
(
    name varchar(16) NOT NULL,
    type varchar(16) NOT NULL,
    age int,
    PRIMARY KEY (name, type)
);

key에 PRI가 두 개 있다고 해서 key가 두 개라는 것이 아니라, name type이 하나의 primary key 라는 뜻

Primary 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

이미 만들어져 있는 테이블에 primary key 속성을 추가하는 것
ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...);

예제 2-1
하나의 컬럼을 기본키로 지정

ALTER TABLE person
ADD PRIMARY KEY (pid);

예제 2-2
여러개의 컬럼을 기본키로 지정

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

FOREIGN KEY(외래키)

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

foreign key 생성 문법1

테이블을 생성하면서 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
     FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
     #foreign key는 연결되는 다른 테이블이 있어야 함
);

예제 1-1

CREATE TABLE orders
(
    oid int NOT NULL,
    order_no varchar(16),
    pid int,
    PRIMARY KEY (oid), #oid 컬럼이 기본키
    CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person(pid) # FK_person라고 foreign키 이름을 주고, orders 테이블의 pid 컬럼을 foreign키로 지정하고, 
참조 테이블로 person 테이블을 지정
order 테이블의 pid는 person 테이블의 pid를 참조한다(연결됨)
)

foreign key는 key값에 MUL로 표시됨

CONSTRAINT 확인하는 방법

foreign key는 여러개 지정가능하고, constraint도 여러개 있을 수 있다.
constraint는 생략가능하지만, 자동 생성된다.
SHOW CREATE TABLE tablename;

FOREIGN KEY 삭제 문법

foreign key는 여러개 지정 가능하므로 이름을 명시해야함
ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;

예제
ALTER TABLE orders
DROP FOREIGN KEY FK_person;

description을 실행하면 삭제된 것을 알 수 없고,
show create table orders로 보면 foreign key가 삭제된 것을 확인할 수 있다.

foreing key 생성 문법2

테이블이 생성된 이후 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에서 작업

1.db 접속

import mysql.connector

conn = mysql.connector.connect(
    host = "",
    port = 3306,
    user = "admin",
    password = "1234",
    database = "zerobase"
)

2. police_station과 crime_status를 매칭

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

두 테이블 모두 경찰서 이름이 31개이다.

그런데 경찰서 이름이 각 테이블에서 다른 형식으로 나타난다.

self join을 활용하여 concat으로 경찰서 이름을 매칭해본다

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

3. primary key 지정

police_station 테이블의 name 컬럼을 primary key로 지정

alter table police_station
add primary key (name);

4. foreign key 지정

crime_status 테이블의 police_station 컬럼을 foreign key로 지정
foreign key로 지정하려고 하니 c.police_station과 p.name이 동일하지 않으므로 p.name과 동일한 값을 가지는 컬럼을 만들어주고, 이를 foreign key로 지정한다.

#reference 컬럼 추가(foreign key가 될 컬럼)
alter table crime_status
add column reference varchar(16);

#foreign key 지정
alter table crime_status
add foreign key (reference) references police_station(name);

5. reference 컬럼 값 채우기

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

확인

6. join으로 police_station과 address 매칭