⏹ FOREIGN KEY 예제 (1)
police_station.name과 crime_status.police_station 을 매칭하여 관계 맺도록 하기. (AWS RDS 사용)
◾ 매칭 관계 확인
#중복 제거 후 총 갯수 확인 (distinct)
mysql> select count(distinct name) from police_station;
+----------------------+
| count(distinct name) |
+----------------------+
| 31 |
+----------------------+
1 row in set (0.05 sec)
mysql> select count(distinct police_station) from crime_status;
+--------------------------------+
| count(distinct police_station) |
+--------------------------------+
| 31 |
+--------------------------------+
1 row in set (0.04 sec)◾ 그러나 경찰서 이름이 각 테이블에서 표시되는 형식이 다름.
mysql> select distinct name from police_station limit 3;
+--------------------------+
| name |
+--------------------------+
| 서울중부경찰서 |
| 서울종로경찰서 |
| 서울남대문경찰서 |
+--------------------------+
3 rows in set (0.04 sec)
mysql> select distinct police_station from crime_status limit 3;
+----------------+
| police_station |
+----------------+
| 중부 |
| 종로 |
| 남대문 |
+----------------+
3 rows in set (0.04 sec)◾ police_station이름을 같게 만들기
# 매칭 확인
mysql> 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;
+----------------+--------------------------+
| police_station | name |
+----------------+--------------------------+
| 중부 | 서울중부경찰서 |
| 종로 | 서울종로경찰서 |
| 남대문 | 서울남대문경찰서 |
| 서대문 | 서울서대문경찰서 |
| 혜화 | 서울혜화경찰서 |
...
| 은평 | 서울은평경찰서 |
| 도봉 | 서울도봉경찰서 |
| 수서 | 서울수서경찰서 |
+----------------+--------------------------+
31 rows in set (0.04 sec)◾ PRIMARY KEY 설정
police_station.name을 primary key로 설정
mysql> ALTER TABLE police_station
-> ADD PRIMARY KEY (name);
Query OK, 31 rows affected (0.12 sec)
Records: 31 Duplicates: 0 Warnings: 0
mysql> desc police_station;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| address | varchar(128) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.04 sec)◾ FOREIGN KEY 설정
crime_status테이블에 foreign key 사용할 컬럼 추가
mysql> ALTER TABLE crime_status ADD COLUMN REFERENCE varchar(16);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year | year | YES | | NULL | |
| police_station | varchar(8) | YES | | NULL | |
| crime_type | varchar(16) | YES | | NULL | |
| status_type | char(2) | YES | | NULL | |
| case_number | int | YES | | NULL | |
| REFERENCE | varchar(16) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.04 sec)
mysql> ALTER TABLE crime_status
-> ADD FOREIGN KEY (REFERENCE) REFERENCES police_station(name);
Query OK, 310 rows affected (0.12 sec)
Records: 310 Duplicates: 0 Warnings: 0
mysql> desc crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year | year | YES | | NULL | |
| police_station | varchar(8) | YES | | NULL | |
| crime_type | varchar(16) | YES | | NULL | |
| status_type | char(2) | YES | | NULL | |
| case_number | int | YES | | NULL | |
| REFERENCE | varchar(16) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.04 sec)◾ 비어있는 컬럼 REFERENCE에 FOREIGN KEY값 업데이트
mysql> UPDATE crime_status c, police_station p
-> SET c.REFERENCE = p.name
-> WHERE p.name LIKE concat('서울', c.police_station,'경찰서');
Query OK, 310 rows affected (0.07 sec)
Rows matched: 310 Changed: 310 Warnings: 0
mysql> SELECT distinct police_station, REFERENCE FROM crime_status;
+----------------+--------------------------+
| police_station | REFERENCE |
+----------------+--------------------------+
| 중부 | 서울중부경찰서 |
| 종로 | 서울종로경찰서 |
| 남대문 | 서울남대문경찰서 |
| 서대문 | 서울서대문경찰서 |
...
| 은평 | 서울은평경찰서 |
| 도봉 | 서울도봉경찰서 |
| 수서 | 서울수서경찰서 |
+----------------+--------------------------+◾ JOIN (🚩FOREIGN KEY 기준으로 두 테이블 연관시켜 검색할 수 있다.)
mysql> SELECT c.police_station, p.address FROM crime_status c, police_station p
-> WHERE c.REFERENCE = p.name
-> GROUP BY c.police_station;
+----------------+----------------------------------------------------------------------------------+
| police_station | address |
+----------------+----------------------------------------------------------------------------------+
| 강남 | 서울특별시 강남구 테헤란로 114길 11 |
| 강동 | 서울특별시 강동구 성내로 33 |
| 강북 | 서울특별시 강북구 오패산로 406 |
| 강서 | 서울특별시 양천구 화곡로 73 |
| 관악 | 서울특별시 관악구 관악로5길 33 |
....
| 은평 | 서울특별시 은평구 연서로 365 |
| 종로 | 서울특별시 종로구 율곡로 46 |
| 종암 | 서울특별시 성북구 종암로 135 |
| 중랑 | 서울특별시 중랑구 중랑역로 137 |
| 중부 | 서울특별시 중구 수표로 27 |
| 혜화 | 서울특별시 종로구 창경궁로 112-16 |
+----------------+----------------------------------------------------------------------------------+
31 rows in set (0.04 sec)⏹ FOREIGN KEY 예제 (2)
1. study_id가 primary key, patient_id가 person테이블 id와 연결된 foreign key로 지정된 study테이블 생성
mysql> 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 (id));
Query OK, 0 rows affected (0.03 sec)
mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)2. 생성한 테이블의 primary key 삭제
mysql> ALTER TABLE study DROP PRIMARY KEY;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)3. 생성한 테이블의 foreign key 삭제
mysql> alter table study drop foreign key FK_study;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)
# 아직 MUL로 되어있지만 연결 끊어진 상태임. -->확인 show create table study
mysql> show create table study;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| study | CREATE TABLE `study` (
`study_id` int NOT NULL,
`study_date` date DEFAULT NULL,
`study_time` time DEFAULT NULL,
`patient_id` int DEFAULT NULL,
KEY `FK_study` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)4. study테이블 patient_id를 person테이블 id와 연결된 foreign key로 등록
mysql> ALTER TABLE study ADD foreign key (patient_id) references person(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table study;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| study | CREATE TABLE `study` (
`study_id` int NOT NULL,
`study_date` date DEFAULT NULL,
`study_time` time DEFAULT NULL,
`patient_id` int DEFAULT NULL,
KEY `patient_id` (`patient_id`),
CONSTRAINT `study_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)❗ constraint 따로 지정하지 않아 자동생성 확인
5. study테이블 study_id를 primary key로 등록
mysql> alter table study add primary key (study_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc study;
+------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| study_id | int | NO | PRI | NULL | |
| study_date | date | YES | | NULL | |
| study_time | time | YES | | NULL | |
| patient_id | int | YES | MUL | NULL | |
+------------+------+------+-----+---------+-------+
4 rows in set (0.00 sec)
데이터분석 스터디노트🧐✍️