❇️ 요약
- PRIMARY KEY
- FOREIGN KEY
- FOREIGN KEY - AWS RDS 예제
📖 PRIMARY KEY(기본 키)
이론
🔆 PRIMARY KEY(기본 키)
- 테이블의 각 레코드를 식별
- 중복되지 않은 고유값을 포함
- NULL값을 포함할 수 없음
- 테이블 당 하나의 기본키를 가짐
🔆 PRIMARY KEY 생성 문법 1
- CREATE TABLE 할때 생성 가능
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column1 datatype NOT NULL,
...
CONSTRAINT constraint_name -- 생략 가능 / 제약조건 지정
PRIMARY KEY (column1, column2, ...) -- 기본키로 지정할 컬럼 작성 / 1개도 되고 여러개여도 됨
)🔆 PRIMARY KEY 삭제 문법
- 테이블당 하나의 PRIMARY KEY만 존재 하기 때문에 PRIMARY KEY 이름을 별도로 줄 필요 없다
ALTER TABLE tablename
DROP PRIMARY KEY;🔆 PRIMARY KEY 생성 문법 2
- 이미 만들어진 테이블에 PRIMARY KEY 추가
ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...)실습
☁️ PRIMARY KEY 생성 예제 1 - 1
- 하나의 칼럼을 기본키로 설정하는 경우
CREATE TABLE person
(
pid int NOT NULL,
name varchar(16),
age int,
sex char,
PRIMARY KEY (pid)
);- 결과
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)☁️ PRIMARY KEY 생성 예제 1 - 2
- 여러개의 칼럼을 기본키로 설정하는 경우
CREATE TABLE animal
(
name varchar(16) NOT NULL,
type varchar(16) NOT NULL,
age int,
PRIMARY KEY (name, type)
);- 결과
- 테이블당 PRIMARY KEY 하나만 설정 가능
- 2개의 PRI 가 명시되어 있다고 2개의 PRIMARY KEY를 갖는 것이 아닌, 두 컬럼이 하나의 PRIMARY KEY라는 뜻
mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| type | varchar(16) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)☁️ PRIMARY KEY 삭제 예제 1
- 하나의 칼럼을 기본키로 설정하는 경우
ALTER TABLE person
DROP PRIMARY KEY;- 결과
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)☁️ PRIMARY KEY 삭제 예제 2
- 여러개의 칼럼을 기본키로 설정하는 경우
ALTER TABLE animal
DROP PRIMARY KEY;- 결과
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)☁️ PRIMARY KEY 생성 예제 1 - 1
- 하나의 칼럼을 기본키로 설정하는 경우
ALTER TABLE person
ADD PRIMARY KEY (pid);- 결과
mysql> desc person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)☁️ PRIMARY KEY 생성 예제 1 - 2
- 여러개의 칼럼을 기본키로 설정하는 경우
- CONSTRAINT 지정해서 PRIMARY KEY 설정 가능, 생략 가능
- CONSTRAINT 생략 시, 키 명 자동생성
ALTER TABLE animal
ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);- 결과
mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| type | varchar(16) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)📖 FOREIGN KEY(외래키)
이론
🔆 FOREIGN KEY(외래키)
- 한 테이블을 다른 테이블과 연결해주는 역할
- 참조되는 테이블의 컬럼은 그 테이블의 기본키(혹은 단일 값)
🔆 FOREIGN KEY 생성 문법 1
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
column3 datatype,
column4 datatype,
...
CONSTRAINT PK_constraint_name
PRIMARY KEY (column1, column2, ...),
CONSTRAINT FK_constraint_name
FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);🔆 CONSTRAINT 확인 문법
- 하나만 존재하는 PRIMARY KEY와 다르게 FOREIGN KEY는 여러개 존재 가능
- 자동 생성된 CONSTRAINT 이름 확인하는 방법
SHOW CREATE TABLE tablename;🔆 FOREIGN KEY 삭제 문법
ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint_name;🔆 FOREIGN KEY 생성 문법 2
- Table이 생성된 이후에도 ALTER TABLE을 통해 FOREIGN KEY를 지정 가능
ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);실습
☁️ 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)
);- 결과
mysql> DESC orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)☁️ FOREIGN KEY 생성 1 - 2
- CONSTRAINT 생략 - CREATE TABLE에서 FOREIGN KEY를 지정하는 경우
CREATE TABLE job
(
jid int NOT NULL,
name varchar(16),
pid int,
PRIMARY KEY (jid),
FOREIGN KEY (pid) REFERENCES person(pid)
);- 결과
mysql> desc job;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| jid | int | NO | PRI | NULL | |
| name | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)☁️ CONSTRAINT 확인 예제
SHOW CREATE TABLE job;- 결과
mysql> SHOW CREATE TABLE job;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| job | CREATE TABLE `job` (
`jid` int NOT NULL,
`name` varchar(16) DEFAULT NULL,
`pid` int DEFAULT NULL,
PRIMARY KEY (`jid`),
KEY `pid` (`pid`),
CONSTRAINT `job_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)☁️ FOREIGN KEY 삭제
ALTER TABLE orders
DROP FOREIGN KEY FK_person;- 결과
- 원래 FORIEN KEY의 설정은 삭제 된것을 알 수 있다.
- KEY 속성만 남아 있음
mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE orders;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
`oid` int NOT NULL,
`order_no` varchar(16) DEFAULT NULL,
`pid` int DEFAULT NULL,
PRIMARY KEY (`oid`),
KEY `FK_person` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)☁️ FOREIGN KEY 생성 예제 2
ALTER TABLE orders
ADD FOREIGN KEY (pid) REFERENCES person(pid);- 결과
mysql> desc orders;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| order_no | varchar(16) | YES | | NULL | |
| pid | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE orders;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
`oid` int NOT NULL,
`order_no` varchar(16) DEFAULT NULL,
`pid` int DEFAULT NULL,
PRIMARY KEY (`oid`),
KEY `pid` (`pid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+📖 FOREIGN KEY 예제 - AWS RDS
- police_station과 crime_status 테이블 사이에 관계(FOREIGN KEY)를 설정
☁️ AWS RDS(database-1)의 zerobase에서 작업
(da_study) C:\sql_ws>mysql -h myhostname -P 3306 -u admin -p zerobase
Enter password: *********
....
mysql>☁️ 분석
- police_station.name과 crime_status.police_station을 매칭하여 관계를 맺도록 한다.
mysql> SELECT COUNT(DISTINCT name)
-> FROM police_station;
+----------------------+
| COUNT(DISTINCT name) |
+----------------------+
| 31 |
+----------------------+
1 row in set (0.01 sec)
mysql> SELECT COUNT(DISTINCT police_station)
-> FROM crime_status;
+--------------------------------+
| COUNT(DISTINCT police_station) |
+--------------------------------+
| 31 |
+--------------------------------+
1 row in set (0.01 sec)- 경찰서 이름이 각 테이블에서 표시되는 형식이 다름
mysql> SELECT DISTINCT name
-> FROM police_station
-> LIMIT 3;
+--------------------------+
| name |
+--------------------------+
| 서울중부경찰서 |
| 서울종로경찰서 |
| 서울남대문경찰서 |
+--------------------------+
3 rows in set (0.01 sec)
mysql> SELECT DISTINCT police_station
-> FROM crime_status
-> LIMIT 3;
+----------------+
| police_station |
+----------------+
| 중부 |
| 종로 |
| 남대문 |
+----------------+
3 rows in set (0.01 sec)- crime_status.police_station을 police_station.name과 같이 만들어서 비교
- JOIN 결과 31개가 잘 매칭 된 것 확인 가능
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로 설정
ALTER TABLE police_station
ADD PRIMARY KEY (name);
mysql> DESC police_station;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | PRI | NULL | |
| adress | varchar(16) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)☁️ FOREIGN KEY 설정
- crime_status 테이블에 FORIEGN KEY로 사용할 Column 추가
ALTER TABLE crime_status
ADD COLUMN reference VARCHAR(16);
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.01 sec)- FORIEGN KEY 생성
ALTER TABLE crime_status
ADD FOREIGN KEY (reference) REFERENCES police_station(name);
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.01 sec)- FOREIGN KEY 값 UPDATE
UPDATE crime_status c, police_station p
SET c.reference = p.name
WHERE p.name LIKE concat('서울', c.police_station, '경찰서');
mysql> SELECT DISTINCT police_station, reference
-> FROM crime_status;
+----------------+--------------------------+
| police_station | reference |
+----------------+--------------------------+
| 중부 | 서울중부경찰서 |
| 종로 | 서울종로경찰서 |
| 남대문 | 서울남대문경찰서 |
| 서대문 | 서울서대문경찰서 |
| 혜화 | 서울혜화경찰서 |
| 용산 | 서울용산경찰서 |
| 성북 | 서울성북경찰서 |
| 동대문 | 서울동대문경찰서 |
| 마포 | 서울마포경찰서 |
| 영등포 | 서울영등포경찰서 |
| 성동 | 서울성동경찰서 |
| 동작 | 서울동작경찰서 |
| 광진 | 서울광진경찰서 |
| 서부 | 서울서부경찰서 |
| 강북 | 서울강북경찰서 |
| 금천 | 서울금천경찰서 |
| 중랑 | 서울중랑경찰서 |
| 강남 | 서울강남경찰서 |
| 관악 | 서울관악경찰서 |
| 강서 | 서울강서경찰서 |
| 강동 | 서울강동경찰서 |
| 종암 | 서울종암경찰서 |
| 구로 | 서울구로경찰서 |
| 서초 | 서울서초경찰서 |
| 양천 | 서울양천경찰서 |
| 송파 | 서울송파경찰서 |
| 노원 | 서울노원경찰서 |
| 방배 | 서울방배경찰서 |
| 은평 | 서울은평경찰서 |
| 도봉 | 서울도봉경찰서 |
| 수서 | 서울수서경찰서 |
+----------------+--------------------------+
31 rows in set (0.01 sec)☁️ 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;
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 |
+----------------+----------------------------------------------+
| 강남 | 서울특별시 강남구 테헤란로 1 |
| 강동 | 서울특별시 강동구 성내로 33 |
| 강북 | 서울특별시 강북구 오패산로 4 |
| 강서 | 서울특별시 양천구 화곡로 73 |
| 관악 | 서울특별시 관악구 관악로5길 |
| 광진 | 서울특별시 광진구 광나루로 4 |
| 구로 | 서울특별시 구로구 가마산로 2 |
| 금천 | 서울특별시 관악구 남부순환로 |
| 남대문 | 서울특별시 중구 한강대로 41 |
| 노원 | 서울특별시 노원구 노원로 28 |
| 도봉 | 서울특별시 도봉구 노해로 40 |
| 동대문 | 서울특별시 동대문구 약령시로 |
| 동작 | 서울특별시 동작구 노량진로 1 |
| 마포 | 서울특별시 마포구 마포대로 1 |
| 방배 | 서울특별시 서초구 방배천로 5 |
| 서대문 | 서울특별시 서대문구 통일로 1 |
| 서부 | 서울특별시 은평구 은평로9길 |
| 서초 | 서울특별시 서초구 반포대로 1 |
| 성동 | 서울특별시 성동구 왕십리광장로 |
| 성북 | 서울특별시 성북구 보문로 17 |
| 송파 | 서울특별시 송파구 중대로 22 |
| 수서 | 서울특별시 강남구 개포로 61 |
| 양천 | 서울특별시 양천구 목동동로 9 |
| 영등포 | 서울특별시 영등포구 국회대로 |
| 용산 | 서울특별시 용산구 원효로89길 |
| 은평 | 서울특별시 은평구 연서로 36 |
| 종로 | 서울특별시 종로구 율곡로 46 |
| 종암 | 서울특별시 성북구 종암로 13 |
| 중랑 | 서울특별시 중랑구 중랑역로 1 |
| 중부 | 서울특별시 중구 수표로 27 |
| 혜화 | 서울특별시 종로구 창경궁로 1 |
+----------------+----------------------------------------------+
31 rows in set (0.02 sec)
제로베이스 DA7 김예빈입니다.