[제로베이스 데이터 취업 스쿨] 9기 10주차 – SQL 심화 (4): Primary Key, Foreign Key

Inhee Kim·2023년 1월 4일
0
post-thumbnail
post-custom-banner

1. PRIMARY KEY

  • 테이블의 각 레코드를 식별
  • 중복되지 않은 고유값을 포함
  • NULL 값을 포함할 수 없음
  • 테이블 당 하나의 기본키를 가짐

실습환경

mysql -V
mysql -u root -p

use zerobase;

(1) PRIMARY KEY 생성 문법 1

create table tablename
(
	column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    ...
    CONSTRAINT constraint_name
     PRIMARY KEY (column1, column2, ...)
);

(2) PRIMARY KEY 생성 예제 1

1. 하나의 칼럼을 기본키로 설정하는 경우

create table person
(
	pid int not null,
    name varchar(16),
    age int,
    sex char,
    PRIMARY KEY (pid)
);

desc person;

2. 여러 개의 칼럼을 기본키로 설정하는 경우

create table animal
(
    name varchar(16) not null,
    type varchar(16) not null,
    age int,
    PRIMARY KEY (name, type)
);

desc animal;

(3) PRIMARY KEY 삭제 문법

ALTER TABLE tablename
DROP PRIMARY KEY;

(4) PRIMARY KEY 삭제 예제

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

alter table person
drop primary key;

desc person;

2. 여러 개의 칼럼이 기본키로 설정된 경우 (방법 동일)

alter table animal
drop primary key;

desc animal;

(5) PRIMARY KEY 생성 문법 2

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

(6) PRIMARY KEY 생성 예제 2

1. 하나의 칼럼을 기본키로 설정하는 경우

alter table person
add primary key (pid);

desc person;

2. 여러 개의 칼럼을 기본키로 설정하는 경우

alter table animal
add constraint PK_animal primary key (name, type);

desc animal;


2. FOREIGN KEY

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

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

(2) FOREIGN KEY 생성 예제 1

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

desc orders;

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

desc job;

(3) COSNTRAINT 확인 문법

SHOW CREATE TABLE tablename;

(4) COSNTRAINT 확인 예제

show create table job;

(5) FOREIGN KEY 삭제 문법

ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;

(6) FOREIGN KEY 삭제 예제

alter table orders
drop foreign key FK_person;

desc orders;
show create table orders;

(7) FOREIGN KEY 생성 문법 2

  • Table이 생성된 이후에도 ALTER TABLE을 통해 FOREIGN KEY를 지정할 수 있다.
ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);

(8) FOREIGN KEY 생성 예제 2

alter table orders
add foreign key (pid) references person(pid);

desc orders;
show create table orders;

3. 문제풀이(실습)

(1) 문제 1

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(distinct police_station) from crime_status;

  • 경찰서 이름이 각 테이블에서 표시되는 형식이 다른 것을 알 수 있다.
select distinct name from police_station limit 3;
select distinct police_station from crime_status limit 3;

  • crime_status.police_station을 police_station.name 과 같이 만들어서 비교하기
select c.police_station, p.name
from crime_status as c, police_station as p
where p.name like concat('서울', c.police_station, '경찰서')
group by c.police_station, p.name;

  • police_station.name을 Primary Key로 설정하기
alter table police_station
add primary key (name);

desc police_station;

  • crime_status 테이블에 Foreign Key로 사용할 Column 추가하기
alter table crime_status
add column reference varchar(16);

desc crime_status;

  • Foreign Key 생성하기
alter table crime_status
add foreign key (reference) references police_station(name);

desc crime_status;

  • Foreign Key 값 Update
update crime_status as c, police_station as p
set c.reference = p.name
where p.name like concat('서울', c.police_station, '경찰서');

select distinct police_station, reference from crime_status;

  • Foreign Key 를 기준으로 두 테이블을 연관시켜 검색하기(JOIN)
select c.police_station, p.address
from crime_status as c, police_station as p
where c.reference = p.name
group by c.police_station;

(2) 문제 2

    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;

    1. 생성한 테이블의 PRIMARY KEY를 삭제하세요.
alter table study
drop primary key;

desc study;

    1. 생성한 테이블의 FOREIGN KEY를 삭제하세요.
alter table study
drop foreign key FK_study;

desc study;
show create study;

    1. study 테이블의 patient_id를 person 테이블의 pid와 연결된 FOREIGN KEY로 등록하세요.
alter table study
add foreign key (patient_id) references person (pid);

show create study;

    1. study 테이블의 study_id를 PRIMARY KEY로 등록하세요.
 alter table study
add primary key (study_id);

desc study;

profile
Date Scientist & Data Analyst
post-custom-banner

0개의 댓글