얌생

제약 조건 ( Constraint )은 각 컬럼들간의 제안사항을 관리하고,

조건을 위반하는 데이터를 방지해 데이터베이스의 무결성 ( Integrity )을 보장하는 규칙이다.

무결성은 데이터가 결함없이 정확하고 완전한 상태를 의미한다.

제약 조건의 종류

 ● 고유 제약 조건 ( Unique )

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

 ●  NULL 제약 조건 ( Null )

• 특정 컬럼이 아무런 값을 입력받지 않도록 설정하거나 무조건 값을 입력 받도록 설정하는 조건
• 데이터가 없으면 Null을 저장해 데이터가 존재하지 않다는것을 표현한다.

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

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

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

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

음식 주문앱 DB 설계

• 고객 ( User ) 테이블

• 음식 ( Food ) 테이블

연관 관계 설계

• 고객이 음식 주문 시, 주문 정보를 어느 테이블에 넣어야 할까?
• 고객 테이블 또는 음식 테이블

"고객 테이블"에 주문 정보를 넣어보자

• 문제점 : 회원 중복

"음식 테이블"에 주문 정보를 넣어보자

• 그럴듯 해 보이지만.. 먹깨비 ( userID = 2 )가 '후라이드치킨'을 하나 더 시킬 경우.. 문제가 생김
• 문제점 : 음식 중복

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

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

ERD ( 개체 관계도 )

• SQL 문

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 Order
(
    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
);

기본 키 ( Primary Key ) 제약 조건

위 CREATE TABLE SQL문을 보면 PRIMARY KEY라는 조건으로 기본 키가 선언되어 있는 것을 확인 할 수 있다.

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

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

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

사용자 ( User ) 테이블

• userId 컬럼을 기본키로 설정한다. ( AUTO_INCREMENT 속성은 데이터가 입력될때마다 해당 속성의 값이 1씩 자동으로 증가시켜주는 속성 )
• 이름 ( name ) 컬럼을 가진다.

NULL 제약 조건

테이블에서 컬럼을 생성할 때 추가 조건을 작성하지 않으면 기본적으로 해당 컬럼은 NULL값을 허용하는 상태가 된다.

NULL 값을 허용하게 되면, 필수적으로 입력되어야 하는 데이터가 누락될 수 있다는 점을 기억하고 있어야한다.

NOT NULL 제약 조건을 추가해 특정 컬럼에서 NULL 값을 허용하지 않게 해줄 수 있다.

NULL 제약 조건 요구사항!

사용자 ( User ) 테이블

• UserId 컬럼을 기본키로 설정한다.
• 이름 ( name ) 컬럼을 가진다.

이외 요구사항

• 사용자 테이블의 이름 컬럼은 NULL 값을 허용하지 않는다.

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

고유 제약 조건 ( Unique )

고유 제약 조건은 중복된 값을 허용하지 않도록 설정할 수 있다.

UNIQUE 제약 조건 요구사항!

사용자 ( User ) 테이블

• userId 컬럼을 기본키로 설정한다.
• 이름 ( name ) 컬럼을 가진다.

이외 요구사항

• 사용자 테이블의 이름 컬럼은 NULL을 허용하지 않는다.
• 사용자 테이블의 이름 컬럼은 중복된 값을 허용하지 않는다.

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

만약, 고유 제약 조건을 할당한 컬럼에 중복된 데이터를 삽입하면 어떻게 될까?

INSERT INTO Users (name) VALUES ('정성원');
INSERT INTO Users (name) VALUES ('정성원');

Error: ER_DUP_ENTRY: Duplicate entry '정성원' for key 'Users.name'

EP_DUP_ENTRY 에러메세지가 출력되고, 데이터가 삽입되지 않은 것을 확인할 수 있다.

기본키를 적용한 컬럼은 고유 제약 조건이 자동으로 적용된다.

외래 키 제약 조건 ( Foreign Key )

외래 키 ( Foreign Key )는 테이블간의 연관 관계 ( Relation Ship )를 표현할 때 사용한다.

연관 관계는 대표적으로 3가지의 형태로 표현할 수 있다.

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

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

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

외래키 제약 조건 요구사항!

정원 ( Garden ) 테이블

• 이름 ( name ) 컬럼을 가진다.
• 주소 ( address ) 컬럼을 가진다.

정원 식물 ( GardenPlants ) 테이블

• 식물 이름 ( name ) 컬럼을 가진다.

이외 요구사항

• 정원 식물 ( GardenPlants )은 특정 정원 ( Garden )에 소속되어 있다.
• 하나의 정원은 여러개의 정원 식물을 가질 수 있다.

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,
    name           VARCHAR(255) NOT NULL,
    FOREIGN KEY (gardenId) REFERENCES Garden (gardenId)
);

위 예제에서 gardenId는 정원 식물 ( GardenPlants ) 테이블에서 정원 ( Garden ) 테이블을 참조하는 외래키다.

이것은 GardenPlants 테이블의 각 행이 실제로 존재하는 Garden 테이블의 행을 참조하도록 보장한다.

이처럼 외래키를 사용하여 특정 GardenPlants 테이블의 행이 어떤 Garden에 속해 있는지 알 수 있게 된다.

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

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

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

CASCADE

• 참조하고 있는 개체가 변경 / 삭제 될 경우 함게 변경 / 삭제된다.
• 예) 사용자가 삭제된다면, 그 사용자의 모든 주문 내역도 삭제 된다.

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

NO ACTION

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

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

SET NULL

• 참조하고 있는 개체가 변경 / 삭제 될 경우 현재 데이터를 NULL로 변경한다.
• 예) 사용자가 삭제되면, 사용자의 주문 내역의 '사용자 ID'는 NULL로 변경된다.

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

SET DEFAULT

• 참조하고 있는 개체가 변경 / 삭제 될 경우 현재 데이터를 기본 값으로 변경한다.
• 예) 사용자가 삭제되면, 사용자의 주문 내역의 '사용자 ID'는 기본 값으로 변경된다.

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