TIL #15) SQL 외래키 제약조건

Today I Learned

외래키(Foreign Key) 제약조건은 테이블과 다른 테이블간의 관계를 맺을 때 사용하는 제약 조건이다.

연관관계는 대표적으로 3가지의 형태로 표현가능하다.

1:N을 가지는 모습

• 1:1 - 1명의 사용자는 1개의 사용자정보를 가질 수 있다.
• 1:N - 1명의 사용자는 여러개의 주문을 할 수 있다.
• N:M - 여러명의 학생은 여러개의 학원을 등록할 수 있다.

CREATE TABLE 테이블명
  FOREIGN KEY (컬럼명) REFERENCES 참조_테이블명 (참조_컬럼명)
    ON DELETE [연계 참조 제약 조건]
    ON UPDATE [연계 참조 제약 조건]
);

기본적으로 외래 키 제약조건은 create table 문을 이용해 테이블을 생성함과 동시에 정의한다.

---

연계 참조 무결성 제약 조건의 종류

• cascade - 참조하고 있는 개체가 변경/삭제 될 경우 함께 변경/삭제 된다.
• no action - 참조하고 있는 개체가 변경/삭제 될 경우 아무런 행위를 하지않는다.(에러 발생해서 변경/삭제 불가!)
• set null - 참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 null로 변경한다.
• set default - 참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 기본 값으로 변경한다.

---

SELECT JOIN 연산자

여러 테이블 간의 외래키로 설정된 컬럼들을 연결하여 조회하는 SELECT 연산자의 활용법 중 하나이다.

SELECT (출력할 컬럼들)
FROM (테이블)
JOIN (테이블)
ON (두 테이블의 관계를 정의)

---

제약조건 예시 더보기

 

1:N 테이블 구현하기

CREATE TABLE Publisher
(
    publisherId      int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    publisherName    varchar(255) NOT NULL,
    publisherAddress varchar(255) NOT NULL
);

CREATE TABLE Books
(
    bookId      int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    publisherId int(11)      NOT NULL,
    bookName    varchar(255) NOT NULL,
    FOREIGN KEY (publisherId) REFERENCES Publisher (publisherId)
);

=> 출판사는 여러개의 책을 가질 수 있다.(외래키 등록)

 

1:1 테이블 구현하기

CREATE TABLE Star
(
    starId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE StarInfo
(
    starInfoId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    starId     int(11)      NOT NULL UNIQUE,
    name       varchar(255) NOT NULL,
    magnitude  varchar(255) NOT NULL,
    distance   varchar(255) NOT NULL,
    FOREIGN KEY (starId) REFERENCES Star(starId)
);

=> 별은 1개의 별 세부정보를 가질 수 있다.(외래키 등록, unique 설정)

 

N:M 테이블 구현하기

CREATE TABLE Idol
(
    idolId int(11)        NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255)   NOT NULL UNIQUE,
    age    int(11)        NULL,
    gender enum ('M','F') NOT NULL
);

CREATE TABLE Production
(
    productionId   int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    productionName varchar(255) NOT NULL UNIQUE,
    address        varchar(255) NOT NULL UNIQUE
);

CREATE TABLE ProductionMember
(
    productionMemberId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    productionId       int(11) NOT NULL,
    idolId             int(11) NOT NULL,
    debutDate          date    NOT NULL,
    FOREIGN KEY (productionId) REFERENCES Production (productionId),
    FOREIGN KEY (idolId) REFERENCES Idol (idolId)
);

=> 아이돌은 소속사를 가지지 않거나, 여러개를 가질 수 있다.

     소속사는 아이돌을 가지지 않거나, 여러명을 소속 시킬 수 있다.(소속사 멤버 테이블 만들어 서로에게 외래키 등록)