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;

• 2. 생성한 테이블의 PRIMARY KEY를 삭제하세요.

alter table study
drop primary key;

desc study;

• 3. 생성한 테이블의 FOREIGN KEY를 삭제하세요.

alter table study
drop foreign key FK_study;

desc study;
show create study;

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

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

show create study;

• 5. study 테이블의 study_id를 PRIMARY KEY로 등록하세요.

 alter table study
add primary key (study_id);

desc study;