[MySQL] PRIMARY KEY, FOREIGN KEY

Bpius·2023년 11월 13일

foreign key mysql primary key

MySQL

목록 보기

12/15

PRIMARY KEY(기본키)

PRIMARY KEY

table의 각 레코드를 식별
중복되지 않은 고유값
null 값을 포함할 수 없음
table 당 하나의 키(컬럼)만 가능(그룹으로 컬럼을 묶을 때에는 컬럼들이 키)

table 생성 시

문법

create table table_name
(
column1 datatype not null,
column2 datatype not null,
...
constraint constraint_name (생략 가능)
primary key (column1, column2,...) 2개 이상도 그룹으로 묶어서 기본키로 설정 가능
)

ex) person1 table primary key 생성1

# table 생성 시
mysql> create table person1
    -> (
    -> pid int not null,
    -> name varchar(16),
    -> age int,
    -> sex char,
    -> primary key(pid)
    -> );
Query OK, 0 rows affected (0.05 sec)

# table 확인
mysql> desc person1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int         | NO   | PRI | NULL    |       | <- KEY컬럼에 PRI가 생성 되었고, NULL이 NO로 지정되었다.
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

ex) person2 table primary key 생성2
primary key로 설정하려는 컬럼(null값 허용 안됨)에 연속으로 primary key를 붙여서 생성도 가능

# table 생성 시
mysql> create table person2
    -> (
    -> pid int not null primary key,
    -> name varchar(16),
    -> age int,
    -> sex char
    -> );
Query OK, 0 rows affected (0.03 sec)

# table 확인
mysql> desc person2;
+-------+-------------+------+-----+---------+-------+
| 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)

ex) person3 table primary key를 복수로 생성
복수라고 하여 기본키가 2개라는 뜻이 아닌, 2개가 하나의 기본키라는 뜻이다.

# table primary 복수 생성
mysql> create table person3
    -> (
    -> pid int not null,
    -> name varchar(16),
    -> age int not null,
    -> sex char,
    -> primary key(pid, age)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc person3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int         | NO   | PRI | NULL    |       | <- 기본기 설정
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | NO   | PRI | NULL    |       | <- 기본기 설정
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

기존 table에서 생성

primary key가 없는 table에 primary key 생성
primary key를 생성할 때, not null로 지정이 되어 있어야 생성이 가능하다.

문법

alter table table_name
add primary key (column1, column2, ...)

기존 table

mysql> desc person1;
+-------+-------------+------+-----+---------+-------+
| 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)

primary key가 없고 'pid' 컬럼이 null값 허용이 아니다.
'pid' 컬럼을 primary key로 설정해보자.

# primary key 설정
mysql> alter table person1
    -> add primary key (pid);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

# table 확인
mysql> desc person1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int         | NO   | PRI | NULL    |       | <- primary key 생성
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

PRIMARY KEY 삭제

table당 하나의 primary key만 존재하기에 이름을 별도로 지정하지 않아도 된다.

문법

alter table table_name
drop primary key

# primary key 삭제
mysql> alter table person1
    -> drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 삭제가 되었는지 확인
mysql> desc person1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int         | NO   |     | NULL    |       | <- primary key 삭제 확인
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

FOREIGN KEY(외래키)

한 table을 다른 table과 연결해주는 역할이며,
참조되는 table의 항목은 참조되는 table의 기본키
foreign key를 설정할 때에는 참조할 다른 table의 primary key를 참조키로 설정해야 한다.

FOREIGN KEY 생성

table이 다른 여러 table를 참조할 수 있기때문에 foreign key는 여러 개 생성할 수 있다.

문법(constraint은 생략 가능 : 생략 시 자동 생성)

create table table_name
(
column1 datatype not null,
column2 datatype,
...
constraint constraint_name # 생략 가능
primary key (column1, ...)
constraint constraint_name # 생략 가능
foreign key (column3, ...) references ref_table_name(ref_column) # 연결(참조)할 테이블과 해당 테이블의 primary key
);

ex) order1 table를 생성할 때 foreing key를 person1과 연결시켜보자.

person1 table

mysql> desc person1;
+-------+-------------+------+-----+---------+-------+
| 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)

order1 table 생성 및 확인

# order1 table 생성
mysql> create table order1
    -> (
    ->  oid int not null primary key, # primary key 설정
    ->  order_no varchar(16),
    ->  pid int,
    ->  foreign key(pid) references person1(pid) # person1의 primary key(pid)를 참조키로 설정하여 foreign key 생성
    -> );
Query OK, 0 rows affected (0.03 sec)

# order1 table 확인
mysql> desc order1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| oid      | int         | NO   | PRI | NULL    |       | <- primary key
| order_no | varchar(16) | YES  |     | NULL    |       |
| pid      | int         | YES  | MUL | NULL    |       | <- foreign key 설정
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

constraint 확인

foreign key는 여러 개 생성 가능하기에 몇 개의 참조를 하는지 확인

문법

show create table table_name

'order1_ibfk_1'라는 이름으로 foreign key가 생성되었음을 볼 수 있다.

mysql> show create table order1;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| order1 | CREATE TABLE `order1` (
  `oid` int NOT NULL,
  `order_no` varchar(16) DEFAULT NULL,
  `pid` int DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `pid` (`pid`),
  CONSTRAINT `order1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person1` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

FOREIGN KEY 삭제

foreign key는 여러 개 생성 가능하기 때문에 foreign key를 삭제하려면 constraint 이름으로 삭제할 수 있다.

문법

alter table table_name
drop foreign key constraint_name;

위에서 생성한 order1 table의 foreign key를 order1_ibfk_1라는 이름으로 생성한 것을 삭제해보자.

mysql> alter table order1
    -> drop foreign key order1_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

삭제가 되었는지 확인해보자.
CONSTRAINT 부분에 있었던 foreign key 정보가 삭제된 것을 볼 수 있다.

mysql> show create table order1;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| order1 | CREATE TABLE `order1` (
  `oid` int NOT NULL,
  `order_no` varchar(16) DEFAULT NULL,
  `pid` int DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

기존 TABLE에 FOREIGN KEY 생성

문법

alter table table_name
add foreign key (column) references ref_table_name(ref_column);

위에서 삭제하여 foreign key가 없는 order1 table에 다시 foreign key를 생성해보자.

# foreign key 생성
mysql> alter table order1
    -> add foreign key (pid) references person1 (pid);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

foreign key가 생성이 되었는지 확인해보자.

mysql> show create table order1;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| order1 | CREATE TABLE `order1` (
  `oid` int NOT NULL,
  `order_no` varchar(16) DEFAULT NULL,
  `pid` int DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `pid` (`pid`),
  CONSTRAINT `order1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `person1` (`pid`) <- foreign key 생성 확인
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)