Primary Key
PRIMARY KEY(기본 키)란?
- 테이블의 각 레코드를 식별
- 중복되지 않은 고유값을 포함
- Null값을 포함할 수 없음
- 테이블당 하나의 기본 키를 가짐
- 기본 키는 컬럼 하나로 지정할 수도 있고, 여러 컬럼을 하나의 기본 키로 설정할 수도 있음.
1) 테이블 생성 시 PRIMARY KEY 지정하는 문법
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
...
CONSTRAINT constraint_name #생략도 가능함
PRIMARY KEY (column1, column2, ...)
);하나의 컬럼을 기본 키로 설정하는 경우
CREATE TABLE person
(
pid int NOT NULL,
name varchar(16),
age int,
sex char,
PRIMARY KEY (pid)
);
...
# pid의 Key에 PRI로 Primary Key 지정된 것 확인할 수 있음.
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.01 sec)여러 개의 컬럼을 기본 키로 설정하는 경우
CREATE TABLE animal
(
name varchar(16) NOT NULL,
type varchar(16) NOT NULL,
age int,
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.07 sec)2) PRIMARY KEY 삭제하는 문법
테이블당 Primary key는 하나이기 때문에 별도로 컬럼명을 쓰지 않아도 tablename의 Primary key가 삭제됨
ALTER TABLE tablename
DROP PRIMARY KEY;3) 이미 만들어진 테이블에 PRIMARY KEY 추가하는 문법
ALTER TABLE tablename
ADD PRIMARY KEY (col1, col2, ...);하나의 컬럼을 기본 키로 설정하는 경우
ALTER TABLE person
ADD PRIMARY KEY (pid);여러 컬럼을 기본 키로 설정하는 경우
ALTER TABLE animal
ADD CONSTRAINT PK_animal #(생략 가능)animal 테이블에 PK(primary key) 지정하겠다는 뜻
ADD PRIMARY KEY (name, type);Foreign Key (외래키)
Foreign Key (외래키)란?
- 한 테이블을 다른 테이블과 연결해주는 역할이며, 참조되는 테이블의 항목은 그 테이블의 기본키(Primary Key) 혹은 단일값이어야 함.
- Foreign Key는 두 테이블을 JOIN 시킬 때 활용
1) Create Table에서 Foreign Key를 지정하는 경우 문법
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
...
CONSTRAINT constraint_name (생략 가능)
PRIMARY KEY (column1, column2, ...),
CONSTRAINT constraint_name (생략 가능)
FOREIGN KEY (column1, column2, ...) REFERENCES REF_tablename(REF_column)
// REFERENCES: 어떤 테이블의 어떤 컬럼을 참조할 것인지
);- Create Table에서 Foreign Key를 지정하는 경우, REFERENCES에서 어떤 테이블의 컬럼을 참조할지 지정
- Foreign Key는 다른 테이블의 항목을 참조하기 때문에 여러개가 존재할 수 있음.
1-1) 실습 예제
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.02 sec)
// oid: Primary Key로 지정
// pid: Foreign Key로 지정1-2) CONSTRAINT 생략하여 Foreign Key 생성
show create table tablename;: Constaraint 이름 알 수 있음
=> 자동 생성된 CONSTRAINT인job_ibfk_1을 확인할 수 있음.
mysql> CREATE TABLE job
-> (
-> jid int not null,
-> name varchar(16),
-> pid int,
-> primary key (jid),
-> FOREIGN KEY (pid) REFERENCES person(pid)
-> );
Query OK, 0 rows affected (0.05 sec)
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.01 sec)
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.02 sec)2) Foreign Key 삭제 문법
alter table tablename / drop foreign key FK_constraint_name;
=> desc tablename하면, Key 컬럼에 MUL이라고 FK 설정이 남아있는 것처럼 보이지만..
=> show create table tablename을 하면, CONSTRAINT 없이 KEY 속성만 남아있는 것을 볼 수 있음.
mysql> alter table orders
-> drop foreign key FK_person;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
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.02 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.03 sec)3) 이미 생성된 테이블에 Foreign Key 추가
ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);- table이 생성된 이후 ALTER TABLE로 FK를 추가할 수 있음.
<실습 예제>
mysql> alter table orders
-> add foreign key (pid) references person(pid);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
// 자동으로 FK orders_ibfk_1가 지정되어 생성된 것을 확인할 수 있음. 실습 예제) police_station과 crime_status 테이블 사이에 관계(Foreign Key)를 설정해보기
// police_station.name과 crime_status.police_station 을 매칭시켜 관계를 맺도록 할 예정!
// 1. 각 컬럼의 고유값 개수가 일치하는지 확인해보자.
mysql> select count(distinct name) from police_station;
+----------------------+
| count(distinct name) |
+----------------------+
| 31 |
+----------------------+
1 row in set (0.02 sec)
mysql> select count(distinct police_station) from crime_status;
+--------------------------------+
| count(distinct police_station) |
+--------------------------------+
| 31 |
+--------------------------------+
1 row in set (0.01 sec)
// 2. 경찰서 이름이 각 테이블에서 표시되는 형식이 다르다.
mysql> select distinct name from police_station limit 3;
+--------------------------+
| name |
+--------------------------+
| 서울중부경찰서 |
| 서울종로경찰서 |
| 서울남대문경찰서 |
+--------------------------+
3 rows in set (0.02 sec)
mysql> select distinct police_station from crime_status limit 3;
+----------------+
| police_station |
+----------------+
| 중부 |
| 종로 |
| 남대문 |
+----------------+
3 rows in set (0.02 sec)
// 3. '서울'+crime_status.police_station+'경찰서' 의 값이 police_station.name과 일치할지 비교해보자. => 31개로 모두 일치
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;
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.03 sec)
//4. 참조할 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 | |
| address | varchar(128) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
// 5. crime_status 테이블에 Foreign Key로 사용할 reference 컬럼 추가 => 비어있는 값으로 생성됨
ALTER TABLE crime_status
ADD COLUMN reference VARCHAR(16);
// 6. reference 컬럼으로 Foreign 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_stype | 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.02 sec)
// 7. reference 컬럼에 값 채워주기
UPDATE crime_status c, police_station p
SET c.reference = p.name
WHERE p.name LIKE concat('서울', c.police_station, '경찰서');
mysql> select distinct reference from crime_status limit 5;
+-----------------------+
| reference |
+-----------------------+
| 서울강남경찰서 |
| 서울강동경찰서 |
| 서울강북경찰서 |
| 서울강서경찰서 |
| 서울관악경찰서 |
+-----------------------+
5 rows in set (0.01 sec)
// 8. 이제 Foreign Key인 reference 컬럼을 기준으로 두 테이블을 연관시켜 검색할 수 있다.
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 |
+----------------+----------------------------------------------------------------------------------+
| 중부 | 서울특별시 중구 수표로 27 |
| 종로 | 서울특별시 종로구 율곡로 46 |
| 남대문 | 서울특별시 중구 한강대로 410 |
| 서대문 | 서울특별시 서대문구 통일로 113 |
| 혜화 | 서울특별시 종로구 창경궁로 112-16 |
| 용산 | 서울특별시 용산구 원효로89길 24 |
| 성북 | 서울특별시 성북구 보문로 170 |
| 동대문 | 서울특별시 동대문구 약령시로 21길 29 |
| 마포 | 서울특별시 마포구 마포대로 183 |
| 영등포 | 서울특별시 영등포구 국회대로 608 |
| 성동 | 서울특별시 성동구 왕십리광장로 9 |
| 동작 | 서울특별시 동작구 노량진로 148 |
| 광진 | 서울특별시 광진구 광나루로 447 광진소방서 임시청사 (능동) |
| 서부 | 서울특별시 은평구 은평로9길 15 |
| 강북 | 서울특별시 강북구 오패산로 406 |
| 금천 | 서울특별시 관악구 남부순환로 1435 |
| 중랑 | 서울특별시 중랑구 중랑역로 137 |
| 강남 | 서울특별시 강남구 테헤란로 114길 11 |
| 관악 | 서울특별시 관악구 관악로5길 33 |
| 강서 | 서울특별시 양천구 화곡로 73 |
| 강동 | 서울특별시 강동구 성내로 33 |
| 종암 | 서울특별시 성북구 종암로 135 |
| 구로 | 서울특별시 구로구 가마산로 235 |
| 서초 | 서울특별시 서초구 반포대로 179 |
| 양천 | 서울특별시 양천구 목동동로 99 |
| 송파 | 서울특별시 송파구 중대로 221 |
| 노원 | 서울특별시 노원구 노원로 283 |
| 방배 | 서울특별시 서초구 방배천로 54 |
| 은평 | 서울특별시 은평구 연서로 365 |
| 도봉 | 서울특별시 도봉구 노해로 403 |
| 수서 | 서울특별시 강남구 개포로 617 |
+----------------+----------------------------------------------------------------------------------+
31 rows in set (0.01 sec)혼자서 해보기 실습:
// 1. study 테이블 생성 (PK: study_id, FK: patient_id (person의 pid 참조)
mysql> CREATE TABLE study(
-> study_id int not null,
-> study_date date,
-> study_time time,
-> patient_id int,
-> PRIMARY KEY (study_id),
-> FOREIGN KEY (patient_id) REFERENCES person(pid)
-> );
Query OK, 0 rows affected (0.04 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.01 sec)
// 2. primary key, foreign key 삭제
mysql> alter table study
-> drop primary key;
Query OK, 0 rows affected (0.08 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` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table study
-> drop foreign key study_ibfk_1;
Query OK, 0 rows affected (0.02 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.01 sec)
// 3. FK: patient_id, PK: study_id 등록하기
mysql> alter table study
-> add foreign key (patient_id) references person(pid);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table study
-> add primary key (study_id);
Query OK, 0 rows affected (0.06 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,
PRIMARY KEY (`study_id`),
KEY `patient_id` (`patient_id`),
CONSTRAINT `study_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `person` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 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.01 sec)