PRIMARY KEY(기본 키)
• 테이블의 각 레코드를 식별
• UNIQUE | 중복되지 않은 고유값을 포함
• NOT NULL | NULL 값을 포함할 수 없음
• 테이블 당 하나의 기본키를 가짐
PRIMARY KEY 생성
🧷 테이블 생성 시
🧷 테이블 수정 시
⛔ constraint는 생략가능하다.
1. 테이블 생성 시 단일 PRIMARY KEY 생성
mysql> create table person
-> (
-> pid int not null,
-> name varchar(16),
-> age int,
-> sex char,
-> primary key (pid)
-> );
Query OK, 0 rows affected (0.06 sec)
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)2. 테이블 생성 시 다중 PRIMARY KEY 생성
mysql> create table animal
-> (
-> name varchar(16) not null,
-> type varchar(16) not null,
-> age int,
-> primary key (name, type)
-> );
Query OK, 0 rows affected (0.03 sec)
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() 의 괄호안에 해당하는 컬럼이름을 기재한다.
3. 테이블 수정 시 단일 PRIMARY KEY 추가
mysql> ALTER TABLE person
-> ADD primary key (pid);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
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)4. 테이블 수정 시 다중 PRIMARY KEY 추가
mysql> ALTER TABLE animal
-> ADD primary key (name, type);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 삭제
🧷 PRIMARY KEY 삭제문법
#단일 키 삭제
mysql> ALTER TABLE person
-> DROP primary key;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
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)
# 다중키 삭제
mysql> ALTER TABLE animal
-> DROP primary key;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc animal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(16) | NO | | NULL | |
| type | varchar(16) | NO | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)Constraint
참고링크
제약조건(constraint)이란 데이터의 무결성을 지키기 위해,
데이터를 입력받을 때 실행되는 검사 규칙을 의미한다.
어떠한 조건을 만족했을 경우에만 데이터가 삽입되도록 제약을 할 수 있는 것
1. NOT NULL
NULL값을 가질 수 없다.
2. UNIQUE
중복된 값을 저장할 수 없다.
3. PRIMARY KEY
not null, unique
4. FOREIGN KEY
한 테이블을 다른 테이블과 연결해주는 역할, FOREIGN KEY를 설정할 때 참조되는 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY가 설정되어 있어야 한다.
5. DEFAULT
해당 필드의 기본값을 설정할 수 있게 해준다
아래 쿼리문 처럼 기본키를 지정할 때,
ADD CONSTRAINT와 제약조건명을 사용하는 이유는
제약조건 삭제 시에
ALTER TABLE [테이블 명] DROP CONSTRAINT [제약조건 이름];
ALTER TABLE [테이블 명] DROP FOREIGN KEY [제약조건 이름];
두가지가 동일 한 효과를 내기 때문이다.
mysql> ALTER TABLE animal
-> ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
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(외래키)
한 테이블을 다른 테이블과 연결해주는 역할이며,
참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)
(Null 가능)
Foreign KEY 생성
🧷 테이블 생성 시 Foreign KEY 생성문법
⛔ constraint는 생략가능하고 자동으로 생성된다.
🧷 테이블 수정 시 Foreign KEY 생성문법
1. 테이블 생성 시 Foreign KEY 생성 (constraint 사용)
mysql> CREATE TABLE orders
-> (
-> oid int not null,
-> pid int,
-> PRIMARY KEY (oid),
-> CONSTRAINT FK_person FOREIGN KEY (pid) references person(pid)
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> desc orders;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| pid | int | YES | MUL | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)😅 헷갈려서 해석하자면
orders 테이블의 고유키인 oid 컬럼이 있고,
다른 테이블과 연결한 pid 컬럼이 있다.
pid 컬럼은 FK_person이라는 제약조건명으로
person 테이블의 pid와 연결되어있다.
2. 테이블 생성 시 Foreign KEY 생성 (constraint 생략)
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.09 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.00 sec)📌 constraint 생략 시 제약조건명 확인방법
🧷
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)CONSTRAINT job_ibfk_1 확인
3. 테이블 수정 시 Foreign KEY 생성
mysql> ALTER TABLE orders
-> ADD FOREIGN KEY (pid) REFERENCES person(pid);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table orders;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
`oid` int NOT 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.00 sec)Foreign KEY 삭제
🧷 Foreign KEY 삭제문법
⛔ primary key와 달리 foreign key는 여러개가 삭제 가능하기 때문에 제약조건이름 (constraint) 넣어줘야한다.
mysql> ALTER TABLE orders
-> DROP FOREIGN KEY FK_person;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc orders;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| oid | int | NO | PRI | NULL | |
| pid | int | YES | MUL | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table orders;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
`oid` int NOT 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)desc 로는 아직 MUL이 남아있지만,
show create table로 확인 시 연결이 끊어진것을 확인 할 수 있음.
예제
- police_station 테이블에는 '서울 OO 경찰서' 라는 데이터가 담긴 name 컬럼과 그 경찰서의 주소 데이터가 담긴 address 컬럼이 있다.
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.15 sec)
mysql> select * from police_station limit 5;
+-----------------------+--------------------------------------------------+
| name | address |
+-----------------------+--------------------------------------------------+
| 서울강남경찰서 | 서울특별시 강남구 테헤란로 114길 11 |
| 서울강동경찰서 | 서울특별시 강동구 성내로 33 |
| 서울강북경찰서 | 서울특별시 강북구 오패산로 406 |
| 서울강서경찰서 | 서울특별시 양천구 화곡로 73 |
| 서울관악경찰서 | 서울특별시 관악구 관악로5길 33 |
+-----------------------+--------------------------------------------------+
5 rows in set (0.15 sec)
mysql> select count(distinct name) from police_station;
+----------------------+
| count(distinct name) |
+----------------------+
| 31 |
+----------------------+
1 row in set (0.16 sec)- crime_status 테이블에는 경찰서 정보가 구 이름만 담겨있는 police_station 컬럼이 있다.
mysql> select count( distinct police_station) from crime_status;
+---------------------------------+
| count( distinct police_station) |
+---------------------------------+
| 31 |
+---------------------------------+즉 police_station의 name컬럼에는 경찰서 이름이,
crime_status 테이블의 police_station 컬럼에는 경찰서의 구 이름이 담겨있음.
지금부터
1. police_station의 name 컬럼을 primary key로 설정하고,
2. crime_status 테이블에 reference 컬럼을 추가하여 (police_station을 참조하는) foreign key로 설정한 후
3. reference컬럼에 name컬럼 데이터를 넣어줄것임
1. police_station의 name 컬럼을 primary key로 설정
mysql> 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. crime_status 테이블에 reference 컬럼을 추가하여 (police_station을 참조하는) foreign key로 설정
- reference 컬럼추가**
mysql> ALTER TABLE crime_status
-> ADD COLUMN reference varchar(16);
Query OK, 0 rows affected (0.16 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.15 sec)- (police_station을 참조하는) foreign key로 설정
mysql> ALTER TABLE crime_status
-> ADD FOREIGN KEY (reference) REFERENCES police_station(name);
Query OK, 310 rows affected (0.20 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 | |
+----------------+-------------+------+-----+---------+-------+3. reference컬럼에 name컬럼 데이터를 넣어줌
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.18 sec)문제풀이
1번문제
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)
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.12 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번문제
mysql> ALTER TABLE study
-> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.16 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번문제
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.00 sec)
mysql> ALTER TABLE study
-> DROP FOREIGN KEY study_ibfk_1;
Query OK, 0 rows affected (0.02 sec)4,5번문제
mysql> ALTER TABLE study
-> ADD FOREIGN KEY (patient_id) REFERENCES person (pid);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE study
-> ADD PRIMARY KEY (study_id);
Query OK, 0 rows affected (0.14 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)Daily Study Note
Study Log