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