CH14)Primary key, Foreign Key

1.primary key
•테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL 값을 포함할 수 없음
• 테이블 당 하나의 기본키를 가짐

mysql> 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.01 sec)

--2개지정 
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.02 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 삭제
mysql> alter table person
    -> drop primary key;
Query OK, 0 rows affected (0.04 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 person
    -> add primary key(pid);

--여러개의 칼럼을 기본키로 지정하는 경우
mysql> alter table animal
    -> add constraint PK_animal
    -> primary key(name, type);

2.foreign key(외래키)
한 테이블을 다른 테이블과 연결해주는 역할이며,
참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)

mysql> create table orders
    -> (
    -> oid int not null,
    -> order_no varchar(16),
    -> pid int,
    -> primary key(oid),
    -> constraint FK_pereson 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)

##CREATE TABLE 에서 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.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.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 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

##foreign key 삭제 
mysql> alter table orders
    -> drop foreign key FK_pereson;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

레퍼런스가 삭제된거 확인
KEY `FK_pereson` (`pid`)

##존재하는 테이블에 foreign key 추가  
mysql> alter table orders
    -> add foreign key (pid) references person(pid);

예제
1.다음과 같이 study_id 가 PRIMARY KEY, patient_id 가 person 테이블의 pid 와 연결된 FOREIGN KEY 로 지정된 study 테이블을 생성하세요.
2. 생성한 테이블의 PRIMARY KEY 를 삭제하세요.
3. 생성한 테이블의 FOREIGN KEY 를 삭제하세요.
4. study 테이블의 patient_id 를 person 테이블의 pid 와 연결된 FOREIGN KEY 로 등록하세요.
5. study 테이블의 study_id 를 PRIMARY KEY로 등록하세요.

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 (pid)
    -> );
Query OK, 0 rows affected (0.06 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)

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 `FK_study` (`patient_id`),
  CONSTRAINT `FK_study` 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 primary key;
Query OK, 0 rows affected (0.05 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)

mysql> alter table study
    -> drop foreign key FK_study;
Query OK, 0 rows affected (0.01 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 `FK_study` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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)

mysql> alter table study
    -> add foreign key (patient_id) references person(pid);
Query OK, 0 rows affected (0.05 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.00 sec)

mysql> alter table study
    -> add primary key(study_id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>