MySQL PRIMARY KEY, FOREIGN KEY

1. Primary Key(기본 키)

Primary Key 속성:

• 테이블의 각 레코드를 식별
• 중복되지 않은 고유값을 포함
• NULL 값을 포함할 수 없음
• 테이블 당 하나의 기본키를 가짐 (column 하나로 지정할 수 있고, 여러개의 column을 하나의 기본키로 지정 가능)

1.1 Primary Key 생성

Primary Key 생성 문법

CREATE TABLE tablename
(
	column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    ...
    CONSTRAINT constraint_name #생략 가능
     PRIMARY KEY (column1, column2, ...)
);

Example 1: 하나의 column을 기본키로 설정하는 경우

CREATE TABLE person
(
	pid int NOT NULL, #pid는 person ID의 줄임말+
    name varchar(16),
    age int,
    sex char,
    PRIMARY KEY (pid)
);

DESC person;

Example 2: 여러개의 column을 기본키로 설정하는 경우

CREATE TABLE animal
(
	name varchar(16) NOT NULL,
    type varchar(16) NOT NULL,
    age int,
    PRIMARY KEY (name, type)
);

DESC animal;

1.2 Primary Key 삭제

Primary Key 삭제 문법

ALTER TABLE tablename
DROP PRIMARY KEY;

Example 1: 하나의 column이 기본키로 설정된 경우

ALTER TABLE person
DROP PRIMARY KEY;

DESC person;

pid(person ID)의 key 속성에 primary key가 사라진걸 확인할 수 있다.

Example 2: 여러개의 column이 기본키로 설정된 경우

하나의 column이 기본키로 설정된 경우와 삭제하는 방법은 동일하다.

ALTER TABLE animal
DROP PRIMARY KEY;

DESC animal;

name과 type의 key 속성에 primary key가 사라진걸 확인할 수 있다.

1.3 테이블 생성 완료 후 Key 속성에 Primary Key 추가

ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...);

Example 1: 하나의 column을 기본키로 지정하는 경우

ALTER TABLE person
ADD PRIMARY KEY (pid);

DESC person;

Example 2: 여러개의 column을 기본키로 지정하는 경우

ALTER TABLE animal
ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);
#constraint PK_animal 생략 가능 (생략 시 자동 생성)

DESC animal;

constraint PK_animal을 생략해도 위와 같이 똑같은 결과값이 나온다.

2. Foreign Key(외래키)

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

2.1 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)
);
#foreign key는 참조를 하는 연결되는 reference가 필요함

Example 1: CREATE TABLE에서 Foreign Key를 지정하는 경우

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)
); #oid = order ID

DESC orders;

Foreign Key로 지정이 되는 경우 Key 속성에서 MUL로 표시가 되는걸 확인할 수 있다.

Example 2: CREATE TABLE에서 CONSTRAINT(제약조건) 생략하여 FOREIGN KEY를 지정하는 경우

CREATE TABLE job
(
	jid int not null,
    name varchar(16),
    pid int,
    PRIMARY KEY (jid),
    FOREIGN KEY (pid) REFERENCES person(pid)
);

DESC job;

2.2 Constraint 확인

Constraint 확인 문법

자동 생성된 Constraint 확인하는 방법

SHOW CREATE TABLE tablename;

Example: job 테이블의 constraint 확인

SHOW CREATE TABLE job;

CONSTRAINT 'job_ibfk_1'로 생성되어 있는걸 확인할 수 있다. 해당 constraint 정보는 나중에 foreign key를 설정할 때 필요하다.

2.3 Foreign Key 삭제

Foreign Key 삭제 문법

ALTER TABLE tablename
DROP FOREIGN KEY FK_constraint;

Example 1: Foreign Key 삭제

ALTER TABLE orders
DROP FOREIGN KEY FK_person;

DESC orders;

pid의 key 속성에 MUL이 남아있음을 알 수 있다.

show create table orders;

Key 'FK_person' 은 남아있되 reference는 깨져있는걸 확인할 수 있다. 따라서, foreign key가 삭제되었다는 것을 알 수 있다.

2.4 테이블 생성 완료 후 Key 속성에 Foreign Key 추가

Foreign Key 생성 문법

ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);

Example 1:

ALTER TABLE orders
ADD FOREIGN KEY (pid) REFERENCES person(pid);

DESC orders;

SHOW CREATE TABLE orders;

자동으로 constraint 'orders_ibfk_1' 생성된 걸 확인할 수 있으며 reference로 person 테이블에서 pid를 가져왔다고 알 수 있다.