Foreign Key
한 테이블을 다른 테이블과 연결해주는 역할.
참조되는 테이블의 항목은 그 테이블의 기본키 혹은 단일값
Create Table 테이블 이름
(
column1 데이터타입 NOT NULL,
column2 데이터타입 NOT NULL,
column3, 데이터타입,
column1, 데이터타입,
...
CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, ...),
CONSTRAINT constraint_name
Foreign key (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);
예제1
Create table orders
(
oid int not null,
order_no varchar(16),
pid int,
PRIMARY KEY (oid),
CONSTRAINT FK_person FORIEGN 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)
);
자동생성된 constraint 확인 문법
Show create table 테이블 이름;
Foreign key 삭제 문법
Alter table 테이블 이름
Drop foreign key FK_constraint;
DESC로 하면 MUL로 뜨지만 레퍼런스 관계는 깨져 있음.
이미 생성된 이후 Alter table을 통해 Foreign key 지정
Alter table 테이블 이름
ADD FOREIGN KEY (column) References REF_tablename(REF_column);
예제
pid를 foreign key로 지정, person 테이블의 pid에서 가져옴.
Alter table orders
ADD FOREIGN KEY (pid) REFERENCES person(pid);
팁!
select count(distinct name) from 테이블;
몇 개나 있나 확인
예제
AWS RDS의 police_station과 Crime_status 테이블에서의 경찰서 이름이 달리 표기
ex) police_station에서는 서울중부경찰서, crime_status에서는 중부, 종로...
둘을 비교
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);
FOREIGN KEY 설정
REFERENCE 컬럼 하나 만들고 시작
ALTER TABLE crime_status
ADD COLUMN reference VARCHAR(16);
KEY 설정
ALTER table crime_status
ADD Foreign key (reference) References police_station(name);
지금 reference 는 비어있는 상태! 채워주자 (Foreign key 값 업데이트)
Update crime_status c, police_station p
SET c.reference = p.name
WHERE p.name LIKE concat ('서울', p.police_station, '경찰서');
확인!
Select distinct police_station, reference From crime_status;
핵심!!
Crime_status가 police_station을 참조! 하고 있는 것!
JOIN한다하면! FOREIGN KEY 기준으로 조인!
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;
