[SQL] : FOREIGN KEY (외래키), CONSTRAINT

김대현·2024년 6월 13일

(1) FOREIGN KEY (외래키)*
(2) FOREIGN KEY (외래키) 생성문법
(3) FOREIGN KEY (외래키) 예제
(4) CONSTRAINT 확인방법
(5) FOREIGN KEY (외래키) 삭제 문법 및 예제
(6) 테이블에 존재하는 column의 FOREIGN KEY (외래키) 생성 문법 및 예제

(1) FOREIGN KEY（외래키）

Primary key 는 테이블 내의 각 행을 고유하게 식별하기 위해 사용되는 하나 이상의 열(column)을 말한다. Primary Key는 데이터의 무결성과 고유성을 보장한다. 테이블에서 특정 행 또는 레코드를 읽거나 변경하려면 레코드끼리 구분할 수 있는 고유의 키가 필요하다. 이러한 고유의 키는 그 행에 값이 있어야 하고 구분을 위해 Primary key 처럼 고유값을 가져야 한다.\
FOREIGN KEY（외래키）: 다른 테이블의 기본 키를 참조하는 열을 말하는 것이다. 참조한다는 것은 현재 보고있는 자료를 이전이나 다른 자료를 참고로 해서 공통된 부분을 비교하거나 대조하는 것을 말한다. 쉽게 말해 한 테이블을 다른 테이블과 연결해주는 역할이며, 참조되는 테이블의 열은 그 테이블의 기본키여야 한다.

🖍️ FOREIGN KEY (외래키) 예시

1. 학생 테이블

학생 ID (기본 키)
이름
나이

2.수업 테이블

수업 ID (기본 키)
수업명
학생 ID (외래 키)

여기서 "학생 ID"는 수업 테이블에서 외래 키로 사용된다. 이 외래 키는 학생 테이블의 기본 키인 "학생 ID"를 참조하는 것이다. 이렇게 함으로써 수업 테이블의 각 행은 특정 학생과 연결되는 것이다.

(2) FOREIGN KEY (외래키) 생성 문법

CREATE TABLE tablename
(
  column datatype NOT NULL,
  ...
  CONSTRAINT constraint_name
  	PRIMARY KEY (column1, column2, ...)
  CONSTRAINT constraint_name
  	FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);

CREATE TABLE tablename: tablename 이라는 이름의 테이블을 생성한다.
column datatype NOT NULL: NOT NULL 제약 조건은 이 컬럼에 NULL 값을 허용하지 않는 것을 의미한다. 해당 column은 primary key 가 되어야 하는 컬럼이기 때문에 NULL 값을 허용하지 않아야 하는 것이다.
CONSTRAINT constraint_name: 여기에서 constraint_name은 제약 조건의 이름을 지정하는 것이다. 이는 데이터베이스 내에서 제약 조건을 고유하게 식별하는 데 사용된다. 이름을 지정하면 나중에 제약 조건을 참조하거나 수정할 때 유용하다. Primary key 기본 키를 줄 때 CONSTRAINT를 생략해도 무관하다.
PRIMARY KEY (column): 기본 키(Primary Key)는 테이블 내의 각 레코드를 고유하게 식별하는 데 사용되며 PRIMARY KEY () 괄호 내에 행을 가장 잘 대표할 수 있는 고유의 키가 되는 column 을 넣으면 된다.
FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column):
특정 열의 외래 키를 지정하는 것이다.REFERENCES REF_tablename(REF_column) 은 다른 테이블의 같은 이름을 가지고 있고 기본 키를 가지는 열을 지정하고 참조해서 테이블 간의 관계를 설정하는 것이다.

(3) FOREIGN KEY (외래키) 생성 예제

CREATE TABLE orders
(
  oid int not null,,
  order_no varchar(16),
  pid in,
  sex char,
  PRIMARY KEY (oid)
  CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person(pid)
);

oid int NOT NULL: oid는 order 테이블의 컬럼의 이름이고 int는 데이터 타입으로 정수를 저장한다는 것이다. NOT NULL 제약 조건은 이 컬럼이 NULL 값을 가질 수 없음을 의미하여 해당 열을 Primary key로 지정하려 한다는 것을 알 수 있다.

- PRIMARY KEY (oid): oid 컬럼을 고유한 기본 키(Primary Key)로 설정하는 것이다. oid 컬럼이 기본 키이므로, 각 oid 행의 값은 테이블 내에서 고유해야 하고 NULL 값을 가질 수 없다.

CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person(pid) :
CONSTRAINT FK_person FOREIGN KEY (pid) 는 pid 컬럼을 FOREIGN KEY (외래키)로 설정하는 것으로 FK_perosn은 pid 외래키가 person 테이블과의 관계를 나타내고 있음을 의미하는 것이다. 이때 constraint 를 생략할 수도 있다.
이후 Foreign key (pid) 를 작성해 pid 열을 외래키로 설정한다. REFERENCES person(pid) 는 orders 테이블이 참조할 다른 테이블과 그 테이블의 열을 지정하는 것으로 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)

위 테이블처럼 pid 열이 Foreign key로 지정됐을 때 key 값이 mul로 지정된 것을 확인할 수 있다. 이것은 Multiple Occurences Column의 약어로 다중 사용 칼럼이라는 의미이다. 즉 두 개 이상의 테이블에서 사용하는 칼럼이라는 의미로 한 테이블에서는 기본키, 현재 테이블에서는 외래키로 사용되는 칼럼이라는 것을 의미한다.

(4) CONSTRAINT 확인방법

Foreign key는 여러 개가 있을 수 있다. 참조하는 테이블이 여러 개 생성될 수 있다. Foreign key를 생성할 때 Constraint constraint_name 를 넣어서 어떤 테이블과 현재 연결이 된 것인지 알 수 있는데 Constraint를 생략해서 자동생성하면 어떤 테이블과 연결된 것인지 확인하기가 어려울 때도 있고 여러 foreign key 중 특정 외래키를 삭제하는 것이 어렵다.
이럴 경우에는 연결된 테이블과 drop으로 테이블 연결을 제거하거나 Foreign key 조건을 변경하거나 삭제를 할 수가 없다.

# Constraint 확인 문법 -> SHOW CREATE TABLE tablename;
mysql> SHOW CREATE TABLE job;

SHOW CREATE TABLE tablename; 을 확인하면 위 처럼 Constraint의 이름을 확인할 수 가 있다.

(5) FOREIGN KEY (외래키) 삭제 문법 및 예제

mysql> ALTER TABLE tablename;
	   DROP FOREIGN KEY FK_constraint;

DROP FOREIGN KEY FK_constraint; : DROP 으로 Foreign key를 삭제하는 명령어로 Fk_constraint 라는 constriant 이름을 알아야 지정하여 외래키를 삭제할 수 있다.

🖍️ FOREIGN KEY (외래키) 삭제 예제

mysql> ALTER TABLE orders;
	   DROP FOREIGN KEY FK_person;

order 테이블 내의 foreign key의 constraint 이름으로 지정된 FK_person 을 삭제하면 Foreign key 가 삭제되는 것을 확인할 수 있다.

쿼리 실행 결과

그런데 key 값이 여전히 mul로 지정된 것을 확인할 수 있는데 테이블을 조회만 해서는 다른 테이블과의 refernce 즉, 연결이 되어 있는지는 직접 확인할 수는 없다. 따라서 앞서 SHOW CREATE TABLE tablename 처럼 foreign key의 조건 및 연결상태를 보여주는 명령문을 사용하면 constraint 이름이 삭제된 것을 확인할 수 있으며 다른 테이블과의 reference가 삭제된 것을 확인할 수 있다.

(6) 테이블에 존재하는 column의 FOREIGN KEY (외래키) 생성 문법 및 예제

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

ADD FOREIGN KEY (column): 특정 열의 외래 키를 지정하는 것이다.
REFERENCES REF_tablename(REF_column) : 다른 테이블의 같은 이름을 가지고 있고 기본 키를 가지는 열을 지정하고 참조해서 테이블 간의 관계를 설정하고 연결해서 참조하려는 것이다.

🖍️ 테이블에 존재하는 column의 FOREIGN KEY (외래키) 생성 예제

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

ADD FOREIGN KEY (pid) : orders 테이블 내의 pid 열을 외래 키로 지정하는 것이다. ADD 앞에 Constraint가 생략된 것을 알아야 하며 Constraint 이름이 자동으로 생성될 것이다.
REFERENCES person(pid) : person 테이블의 기본 키를 가지는 열을 지정하고 참조해서 orders 테이블 간의 관계를 설정하고 연결해서 참조하려는 것이다. Foreign key를 가지는 orders 테이블의pid가 person에서 Primary key 기본 키를 가지는 pid 와 연결된 것이다.