Node.js 숙련주차 3.3 SQL과 제약 조건(SQL 강의와 겹침)

1. SQL의 제약 조건을 알아봅니다.
2. SQL을 이용하여 제약 조건을 설정합니다.
3. 여러 테이블간의 연관 관계를 설정해봅니다.

01. SQL 제약조건

1) 제약 조건(Constraint)이란?

제약 조건(Constraint)은 각 컬럼들간의 제한사항을 관리하고, 조건을 위반하는 데이터를 방지하여 데이터베이스의 무결성(Integrity)을 보장하는 규칙입니다.
   무결성(Integrity) : 데이터가 결함없이 정확하고 완전한 상태를 의미합니다.

 

제약 조건은 데이터베이스 테이블을 분석할 때 설정된 컬럼의 속성을 바탕으로 해당 테이블이 어떤 역할을 하는지, 어떤 데이터를 저장하고 있는지 명확하게 인지할 수 있게 도와줍니다.
이런 이유로, 데이터베이스 설계시 제약 조건을 잘 설정하는 것이 아주 중요니다. 

 

2) 대표적인 제약 조건의 종류

- 고유(Unique) 제약 조건

- NULL 제약 조건
- 기본 키 (Primary Key) 제약 조건
- 외래 키 (Foreign Key) 제약 조건

고유(Unique) 제약 조건 (Unique)

테이블에 소속된 특정 컬럼이 중복된 키를 가질 수 없는 조건
   사용자 아이디, 이메일과 같은 고유한 정보를 저장할 때 사용합니다.

 

NULL 제약 조건 (Null)

특정 컬럼이 아무런 값을 입력받지 않도록 설정하거나, 무조건 값을 입력 받도록 설정하는 조건
데이터가 없다면, NULL을 저장하여, 데이터가 존재하지 않다는것을 표현합니다.

 

기본 키 (Primary Key) 제약 조건 (Primary Key)

테이블 내에서 각 행(row)을 고유하게 식별할 수 있도록 보장하는 조건

 

외래 키 (Foreign Key) 제약 조건 (Foreign Key)

데이터베이스를 설계할 때 가장 많은 고민을 하게 될 테이블 간의 관계를 설정하는 조건
한 테이블의 컬럼(Column)이 다른 테이블의 특정 행(Row)을 참조하도록 설정하는 조건

 

3) 음식 주문앱 DB 설계 예시

예를 들어, 우리가 음식 주문앱 DB를 설계한다고 가정

일단 "고객이 1개의 음식을 주문할 수 있다"라는 요구사항을 받았다고 해봅시다.

 

일단 각 주체의 테이블 설계가 필요 합니다.
고객 (User) 테이블

 

음식 (Food) 테이블

연관 관계 고민
고객이 음식 주문 시, 주문 정보는 어느 테이블에 들어가야 할까?
고객 테이블이나 음식 테이블에 주문정보를 넣으면 회원이나 주문 음식에 중복생김

 '주문'을 위한 테이블이 필요 → 주문(Order) 테이블 추가

회원 1명은 주문 N개를 할 수 있다.
회원 : 주문 = 1 : N 관계
음식 1개는 주문 N개에 포함될 수 있다.
음식 : 주문 = 1 : N 관계
결론적으로
회원 : 음식 = N : M 관계

 

ERD

 ERD의 테이블을 SQL로 만들기
아래의 SQL을 이용하면 ERD와 동일한 테이블을 생성할 수 있습니다.

AUTO_INCREMENT PRIMARY : 아무것도 넣지 않았을 때 기본적으로 숫자가 1 씩 증가한 값을 집어 넣습니다.

CREATE TABLE User
(
    userId    int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(255) NOT NULL UNIQUE
);

CREATE TABLE Food
(
    foodId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255),
    price  int(11)
);

CREATE TABLE Orders
(
    orderId   int(11)  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId    int(11)  NOT NULL,
    foodId    int(11)  NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (foodId) REFERENCES Food (foodId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
    FOREIGN KEY (userId) REFERENCES User (userId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);

 

테이블의 컬럼 조회

DESC Users;

 

User

 

Food

 

Orders

4) 기본 키(Primary Key) 제약 조건

기본 키(Primary Key)는 테이블에 있는 데이터를 고유하게 구분할 수 있는 정보를 나타내기 위해서 사용합니다.
이전 예시에서 테이블을 생성할 때 PRIMARY KEY라는 조건으로 기본 키가 선언되어 있던 것을 확인할 수 있었습니다. 

 

만약, 기본 키를 설정하지 않은 테이블을 생성하려한다면, 특정 데이터를 찾기위한 고유한 정보가 존재하지 않기 때문에 아래와 같은 에러메시지가 출력되면서 테이블이 생성되지 않게됩니다.

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

기본 키 제약 조건 테이블 구현하기

기본 키 제약 조건 요구사항

사용자(Users) 테이블
   1. userId 컬럼을 가지고, 기본키로 설정합니다.
   2.이름(name) 컬럼을 가집니다.

CREATE TABLE Users
(
    userId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(255)
);

​
 AUTO_INCREMENT 는 데이터를 삽입할 때 아무런 데이터를 입력하지 않더라도 고유한 값을 유지할 수 있도록 도와주는 속성입니다.
위에서 확인하였던 예시에서 기본 키를 설정하는 부분에서는 AUTO_INCREMENT라는 속성이 작성되어있는데요.
해당 속성은 DB 내에서 데이터가 입력될 때 마다 숫자를 1씩 증가시켜 기본 키의 고유한 값을 유지시켜주기 때문에 데이터를 삽입할 때마다 매번 어떤 값을 입력해야하는지 고민하지 않고 효율적인 개발을 가능하게 해줍니다. 

5) NULL 제약 조건

NULL 속성은 특정 컬럼에서 NULL값을 허용하거나, 허용하지 않도록 설정하는 조건입니다. NULL은 ‘값이 없음’을 나타내는 속성입니다.
테이블에서 컬럼을 생성할 때 추가 조건을 작성하지 않으면 기본적으로 해당 컬럼은 NULL값을 허용하는 상태가 됩니다. 즉, 데이터가 입력되지 않아도 된다는 것이죠.
하지만 NULL값을 허용하는 것은, 필수적으로 입력되어야 하는 데이터가 누락될 수 있는 위험이 발생할 수 있습니다.
예를 들어, 어플리케이션에서 사용자가 이메일이나, 패스워드를 통해 로그인하는 경우, 해당하는 2개의 정보는 필수로 존재해야하는 컬럼값일 것입니다. 그러나, 이메일이나 패스워드가 NULL 값이라면, 즉 데이터가 입력되지 않았다면, 사용자 인증 과정에서 문제가 발생하게 되겠죠. ​
이처럼 특정 컬럼에서 NULL 값을 허용하지 않아야 할 때는 NOT NULL 제약 조건을 추가하여 해당 컬럼에 NULL 값이 입력되는 것을 방지해야 합니다.

 

NULL 제약 조건 테이블 구현하기

NULL 제약 조건 요구사항!
사용자(Users) 테이블
   userId 컬럼을 가지고, 기본키로 설정합니다.
   이름(name) 컬럼을 가집니다.
이외 요구사항
   사용자 테이블의 이름 컬럼은 NULL 값을 허용하지 않습니다.

CREATE TABLE Users
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255) NOT NULL
);

6) 고유(Unique) 제약 조건

UNIQUE 제약 조건은 특정 컬럼에서 중복된 값이 허용되지 않도록 설정하는 조건입니다.

UNIQUE 제약 조건 테이블 구현하기

UNIQUE 제약 조건 요구사항!
 사용자(Users) 테이블
   userId 컬럼을 가지고, 기본키로 설정합니다.
   이름(name) 컬럼을 가집니다.
이외 요구사항
   사용자 테이블의 이름 컬럼은 NULL을 허용하지 않습니다.
   사용자 테이블의 이름 컬럼은 중복된 값을 허용하지 않습니다.
   UNIQUE 제약 조건 테이블 구현하기

CREATE TABLE Users
(
    userId int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name   varchar(255) NOT NULL UNIQUE
);

​그렇다면, 고유 제약 조건을 할당한 컬럼에 중복된 데이터를 삽입하면 어떤 에러가 발생하는지 확인해볼까요?

// UNIQUE 제약 조건 데이터 삽입하기
INSERT INTO Users (name) VALUES ('이용우');
INSERT INTO Users (name) VALUES ('이용우');

 

결과

Error: ER_DUP_ENTRY: Duplicate entry '이용우' for key 'Users.name'

ER_DUP_ENTRY 에러메시지가 출력되고, 데이터가 삽입되지 않은 것을 확인할 수 있습니다. 이처럼 고유 제약 조건은 중복된 데이터를 삽입시키지 않도록 설정할 수 있습니다.
그리고 추가적으로 설명드리자면 기본 키를 적용한 컬럼은 고유 제약 조건이 자동으로 적용됩니다.

 

7) 외래 키(Foreign Key) 제약 조건

외래 키(Foreign Key)는 테이블간의 연관 관계(Relation Ship)를 표현할 때 사용합니다. 
연관 관계는 대표적으로 3가지의 형태로 표현할 수 있습니다.
   1:1 - 1명의 사용자(User)는 1개의 사용자 정보(UserInfo)를 가질 수 있다.
   1:N - 1명의 사용자(User)는 여러개의 주문(Order)을 할 수 있다.
   N:M - 여러명의 학생(Student)은 여러개의 학원(School)을 등록할 수 있다.

기본적으로, 외래 키 제약 조건은 CREATE TABLE 문을 사용하여 테이블을 생성함과 동시에 정의합니다. 

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

외래키 제약 조건 테이블 구현

외래키 제약 조건 요구사항

   정원(Garden) 테이블
     1. 이름(name) 컬럼을 가집니다.
     2.  주소(address) 컬럼을 가집니다.
   정원 식물(GardenPlants) 테이블
     1. 식물 이름(name) 컬럼을 가집니다.
이외 요구사항
    1. 정원 식물(GardenPlants)은 특정 정원(Garden)에 소속되어 있습니다.
    2. 하나의 정원은 여러개의 정원 식물을 가질 수 있습니다.

CREATE TABLE Garden
(
    gardenId INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(255) NOT NULL,
    address  VARCHAR(255) NOT NULL
);

CREATE TABLE GardenPlants
(
    gardenPlantsId INT(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    gardenId       INT(11)      NOT NULL, -- Garden의 gardenId와 동일한 데이터 타입을 줍니다.
    name           VARCHAR(255) NOT NULL,
    FOREIGN KEY (gardenId) REFERENCES Garden (gardenId)
);

위 gardenId는 정원 식물(GardenPlants) 테이블에서 정원(Garden) 테이블을 참조하는 외래키입니다. 이것은 GardenPlants 테이블의 각 행이 실제로 존재하는 Garden 테이블의 행을 참조하도록 보장합니다.
이렇게 외래키를 사용하여 특정 GardenPlants 테이블의 행이 어떤 Garden에 속해 있는지 알 수 있게 됩니다.

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

8) 외래 키(Foreign Key) 제약 조건 심화

외래 키의 경우 다른 테이블과 관계를 맺고 있는 참조 데이터가 삭제(DELETE)또는 수정(UPDATE)될 때 어떤 행위를 해야하는지 설정할 수 있습니다. 이런 행위를 수행하는 조건을 연계 참조 무결성 제약 조건이라고 정의합니다.

연계 참조 무결성 제약 조건의 종류
CASCADE
참조하고 있는 개체가 변경/삭제 될 경우 함께 변경/삭제됩니다.
ex) 사용자(userId)가 삭제된다면, 그 사용자의 모든 주문 내역도 삭제됩니다.

FOREIGN KEY (userId) REFERENCES Users(userId)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

NO ACTION
참조하고 있는 개체가 변경/삭제 될 경우 아무런 행위를 하지 않고 에러가 발생하게됩니다.
ex) 사용자를 삭제할 때, 사용자의 주문 내역이 아직 존재한다면, 삭제를 막습니다.

FOREIGN KEY (userId) REFERENCES Users(userId)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

​
SET NULL
참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 NULL로 변경합니다.
ex) 사용자가 삭제되면, 사용자의 주문 내역의 ‘사용자 ID’는 NULL로 변경됩니다.

FOREIGN KEY (userId) REFERENCES Users(userId)
    ON DELETE SET NULL
    ON UPDATE SET NULL;

​
SET DEFAULT
참조하고 있는 개체가 변경/삭제 될 경우 현재 데이터를 기본 값으로 변경합니다.
ex) 사용자가 삭제되면, 사용자의 주문 내역의 ‘사용자 ID’는 기본 값으로 변경됩니다.

FOREIGN KEY (userId) REFERENCES Users(userId)
    ON DELETE SET DEFAULT
    ON UPDATE SET DEFAULT;

​

9) SELECT JOIN 연산자

JOIN 연산자는 두 테이블 사이의 공통된 데이터를 기준으로 테이블을 연결하여 하나의 테이블처럼 조회 할 수 있게 해주는 연산자입니다.
JOIN 연산자는 SQL의 제약 조건은 아닙니다. 여러 테이블 간의 외래 키(Foreign Key)로 설정된 컬럼들을 연결하여 조회하는 SELECT 연산자의 활용법 중 하나입니다. 
JOIN 연산자는 데이터를 조회할 때, 여러 테이블들의 연관 관계(Relationship)를 포함하여 데이터를 조회합니다. 

위와 같은 ERD는 사용자(Users) 테이블과 게시글(Posts) 테이블에서 1명의 사용자가 여러개의 게시글을 작성 할 수 있으므로 1:N 관계를 갖게 됩니다. 

 ERD는 Entity Relationship Diagram(엔티티 관계 다이어그램)의 약자 니다.
여기서 게시글 목록을 조회할 때 작성한 사용자의 이메일을 표시하고싶더라도 게시글 테이블에는 email 컬럼이 존재하지 않기 때문에, 이메일을 표시해줄 수 없게됩니다.
하지만, 게시글 테이블에서는 userId 컬럼을 이용해 어떤 사용자가 게시글을 작성했는지 확인할 수 있으므로, JOIN을 이용해 외래키가 설정된 userId를 기준으로 해당 사용자의 이메일을 함께 출력하도록 설정할 수 있습니다.
사용자, 게시글 생성 및 삽입 SQL

CREATE TABLE Users
(
    userId   int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email    varchar(255) NOT NULL,
    password varchar(255) NOT NULL
);

CREATE TABLE Posts
(
    postId  int(11)      NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId  int(11)      NOT NULL,
    title   varchar(255) NOT NULL,
    content varchar(255) NOT NULL,
    FOREIGN KEY (userId) REFERENCES Users (userId)
);


INSERT INTO Users (userId, email, password)
VALUES (1, 'AAAA', '1234'),
       (2, 'BBBB', '1234');

INSERT INTO Posts (userId, title, content)
VALUES (1, 'AAAA Title1', 'content'),
       (1, 'AAAA Title2', 'content'),
       (2, 'BBBB Title1', 'content'),
       (2, 'BBBB Title2', 'content');

[코드스니펫] 사용자, 게시글 테이블 JOIN SQL

SELECT p.postId, p.title, p.content, u.email
FROM Posts as p
JOIN Users as u
ON p.userId = u.userId;

컬럼명 별칭

컬럼명 as 별칭
별칭(alias)은 여러개의 테이블을 참조할 때, 특정 테이블을 간단하게 나타내기 위해 사용합니다.
별칭을 사용하지 않을 경우 모든 컬럼에 테이블명을 있는 그대로 작성해야합니다.
만약 테이블의 이름이 길게 정의되어 있을 경우 가독성이 나쁜 코드로 구현될 것 입니다.

JOIN ... ON은 두 개 이상의 테이블을 결합할 때, 어떤 조건으로 테이블을 결합할 것인지를 정의할 때 사용합니다.
ON 뒤에는 결합 조건이 위치하며, 이는 두 테이블 간의 관계를 정의합니다.

테이블A  a JOIN 테이블B b ON a.동일컬럼 = b.동일컬럼

이번 예시는 사용자(Users) 테이블에 있는 userId가 게시글(Posts) 테이블에 있는 userId로 외래 키가 설정되어 있었고, 해당 컬럼을 기준으로 게시글의 작성자를 확인할 수 있었습니다.

10) 연습 퀴즈 - 제약 조건

1. 아래 요구사항에 맞는 테이블을 구현하는 SQL을 작성해주세요!
1:N 테이블 요구사항

출판사(Publisher) 테이블
1. publisherId 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 출판사 명(publisherName) 컬럼을 가집니다. 문자열 타입을 가집니다.
3. 출판사 주소(publisherAddress) 컬럼을 가집니다. 문자열 타입을 가집니다.
책(Books) 테이블
1. bookId 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 책 명(bookName) 컬럼을 가집니다. 문자열 타입을 가집니다.
이외 요구사항
-출판사는 여러개의 책을 가질 수 있습니다.
-출판사 명, 출판사 주소, 책 명은 NULL 값을 가질 수 없습니다.
[답안] 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)
);

2. 아래 요구사항에 맞는 테이블을 구현하는 SQL을 작성해주세요!
1:1 테이블 요구사항

별(Star) 테이블
1. starId 컬럼을 가집니다. 기본 키 조건을 설정합니다.
별 세부정보(StarInfo) 테이블
1. starInfoId 컬럼을 가집니다. 기본 키 조건을 설정합니다.
2. 이름(name) 컬럼을 가집니다. 문자열 타입을 가집니다.
3. 밝기(magnitude) 컬럼을 가집니다. 문자열 타입을 가집니다.
4. 거리(distance) 컬럼을 가집니다. 문자열 타입을 가집니다.
이외 요구사항
- 별은 1개의 별 세부정보를 가질 수 있습니다.
- 이름, 밝기, 거리 컬럼은 NULL 값을 가질 수 없습니다.
 [답안] 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)
);

3. 아래 요구사항에 맞는 테이블을 구현하는 SQL을 작성해주세요
 N:M 테이블 요구사항
아이돌(Idol) 테이블
1. 이름(name) 컬럼을 가집니다.
2. 나이(age) 컬럼을 가집니다.
3. 성별(gender) 컬럼을 가집니다.
 소속사(production) 테이블
1. 회사명(productionName) 컬럼을 가집니다.
2. 주소(address) 컬럼을 가집니다.
소속사 멤버(ProductionMember) 테이블
1. 아이돌과 소속사를 연결해줍니다.
2. 아이돌의 소속사 데뷔 날짜(debutDate) 컬럼을 가집니다.
이외 요구사항
-아이돌은 소속사를 가지지 않거나, 여러개를 가질 수 있습니다.
-아이돌의 이름은 중복될 수 없습니다.
-소속사는 아이돌을 가지지 않거나, 여러명을 소속시킬 수 있습니다.
-소속사의 회사명은 중복될 수 없습니다.
-소속사의 주소는 중복될 수 없습니다.
 [답안] 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)
);

11) 다음 챕터에 앞서

여러분들은 다음 Prisma 챕터에 들어가기 전 DB는 어떻게 설계하고, SQL은 어떻게 사용할 수 있는지 이해하고 넘어간다면 더욱 효율적인 학습이 가능 할 것입니다.
mongoose, Prisma와 같은 ODM/ORM은 SQL을 직접 작성하지 않고, 프로그래밍 언어로 데이터베이스와 객체 간의 관계를 매핑하여 사용하는 도구입니다. 이런 도구들이 SQL의 복잡성을 감소시키지만, SQL을 이해하는 것은 여전히 중요합니다. 
때문에, SQL을 알고 ODM/ORM의 코드가 어떻게 SQL로 변환되는지 이해한다면, 데이터베이스 관련 문제를 더욱 명확하게 디버깅할 수 있게 될 것입니다.