다음과 같이 study_id가 프라이머리 키, patient_id가 person 테이블의 pid와 연결된 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 (pid)
-> );
Query OK, 0 rows affected (0.03 sec)
| 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 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ (편집됨)
0111danye
오후 2:29
2. 생성한 테이블의 프라이머리 키를 삭제 하시오
mysql> alter table study
-> drop primary key;
Query OK, 0 rows affected (0.07 sec) (편집됨)
2:30
3. 생성한 테이블의 foreign key를 삭제하시오
mysql> alter table study
-> drop foreign key FK_study;
Query OK, 0 rows affected (0.01 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,
KEY FK_study (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ (편집됨)
2:30
4. study 테이블의 patient_id 를 person 테이블의 pid 와 연결된 foreign key로 등록하세요
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.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 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ (편집됨)
2:31
5. study 테이블의study_id를 프라이머리 키로 등록하시오
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 primary key (study_id);
Query OK, 0 rows affected (0.06 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) (편집됨)
