Key
PRIMARY KEY
- 테이블의 각 레코드를 식별
- 중복되지 않은 고유값을 포함
- NULL 값을 포함할 수 없음
- 테이블 당 하나의 기본키를 가짐
PRIMARY KEY 생성(테이블 생성)
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL
...
CONSTARAINT constraint_name
PRIMARY KEY (column1, column2, ...)
);
mysql> create table person
-> (
-> pid int not null,
-> name varchar(16),
-> age int,
-> sex char,
-> primary key (pid)
-> );
Query OK, 0 rows affected (0.04 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.03 sec)
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 삭제
ALTER TABLE tablename
DROP 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 animal
-> drop primary key;
Query OK, 0 rows affected (0.03 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)
PRIMARY KEY 추가(이미 생성된 테이블)
ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...);
mysql> alter table person
-> add primary key (pid);
Query OK, 0 rows affected (0.03 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)
mysql> alter table animal
-> add constraint PK_animal
-> primary key (name, type);
Query OK, 0 rows affected (0.03 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
- 한 테이블을 다른 테이블과 연결해주는 역할이며, 참조되는 테이블의 항목은 그 테이블의 기본키
FOREIGN KEY 생성(테이블 생성)
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
column3 datatype,
column4 datatype,
...
CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, ...),
CONSTRAINT constraint_name
FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);
mysql> 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)
-> );
Query OK, 0 rows affected (0.04 sec)
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> 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)
FOREIGN KEY 이름 및 정보 확인
SHOW CREATE TABLE tablename
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 삭제
- 삭제 완료 후 MUL 표시는 남아있지만 내부적으로 관계가 절단됨
ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;
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.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 추가(이미 생성된 테이블)
ALTER TALBE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);
mysql> alter table orders
-> add foreign key (pid) references person(pid);
Query OK, 0 rows affected (0.05 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.00 sec)
예제. crime_status와 police_station 연결하기
1) 데이터 확인
- police_station의 name과 crime_status의 police_station을 확인
mysql> select count(distinct name) from police_station;
+
| count(distinct name) |
+
| 31 |
+
1 row in set (0.09 sec)
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 | |
+
5 rows in set (0.10 sec)
mysql> select count(distinct police_station) from crime_status;
+
| count(distinct police_station) |
+
| 31 |
+
1 row in set (0.10 sec)
mysql> select distinct name from police_station limit 3;
+
| name |
+
| 서울중부경찰서 |
| 서울종로경찰서 |
| 서울남대문경찰서 |
+
3 rows in set (0.06 sec)
mysql> select distinct police_station from crime_status limit 3;
+
| police_station |
+
| 중부 |
| 종로 |
| 남대문 |
+
3 rows in set (0.09 sec)
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.12 sec)
2) Key 연결
- police_station의 name를 PRI로 설정
- crime_status에 reference 컬럼을 만들어서 FOR로 설정
- police_station의 name과 동등한 데이터를 referenece에 추가
mysql> alter table police_station
-> add primary key (name);
Query OK, 31 rows affected (0.41 sec)
Records: 31 Duplicates: 0 Warnings: 0
mysql> alter table crime_status
-> add column reference varchar(16);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table crime_status
-> add foreign key (reference) references police_station(name);
Query OK, 310 rows affected (0.12 sec)
Records: 310 Duplicates: 0 Warnings: 0
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.11 sec)
Rows matched: 310 Changed: 310 Warnings: 0
mysql> select distinct police_station, reference from crime_status;
+
| police_station | reference |
+
| 중부 | 서울중부경찰서 |
| 종로 | 서울종로경찰서 |
| 남대문 | 서울남대문경찰서 |
...............중략......................
| 은평 | 서울은평경찰서 |
| 도봉 | 서울도봉경찰서 |
| 수서 | 서울수서경찰서 |
+
31 rows in set (0.05 sec)
3) 데이터 조회
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 |
+
| 강남 | 서울특별시 강남구 테헤란로 114길 11 |
| 강동 | 서울특별시 강동구 성내로 33 |
| 강북 | 서울특별시 강북구 오패산로 406 |
| 강서 | 서울특별시 양천구 화곡로 73 |
| 관악 | 서울특별시 관악구 관악로5길 33 |
| 광진 | 서울특별시 광진구 광나루로 447 광진소방서 임시청사 (능동) |
...............중략.......................................................
| 중랑 | 서울특별시 중랑구 중랑역로 137 |
| 중부 | 서울특별시 중구 수표로 27 |
| 혜화 | 서울특별시 종로구 창경궁로 112-16 |
+
31 rows in set (0.07 sec)
