얌생

제약 조건 ( 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;

SQL

데이터베이스에서 사용하는 생성, 삽입, 조회 명령문을 SQL ( Structured Query Language )라고 한다.

SQL의 종류

• DDL ( Data Definition Language )
• DML ( Data Manipulation Language )
• DCL ( Data Control Language )
• TCL ( Transaction Control Language )

DDL ( Data Definition Language )

데이터를 정의할 때 사용하는 언어로 테이블이나 데이터베이스를 생성, 수정, 삭제와 같은 행위를 할 때 사용한다.

• CREATE ( DATABASE, TABLE, VIEW, INDEX 등을 생성할 때 사용한다. )

CREATE DATABASE 데이터베이스명;
CREATE TABLE 테이블명
{
 컬럼명 컬럼속성
}

• DROP ( DATABASE, TABLE, VIEW, INDEX 등을 삭제할 때 사용한다. )

DROP DATABASE 데이터베이스명;
DROP TABLE 테이블명;

• ALTER ( DATABASE, TABLE 등의 속성을 변경할 때 사용한다. )

ALTER DATABASE 데이터베이스명 변경조건;
ALTER TABLE 테이블명 ADD 변경조건; -- 테이블에 새로운 컬럼을 추가합니다.
ALTER TABLE 테이블명 DROP 변경조건; -- 테이블에서 컬럼을 삭제합니다.
ALTER TABLE 테이블명 MODIFY 변경조건; -- 테이블의 컬럼을 수정합니다.
ALTER TABLE 테이블명 RENAME 변경조건; -- 테이블의 이름을 변경합니다.
...

DML ( Data Manipulation Language )

데이터베이스에서 데이터를 조작할 때 사용하는 언어로 데이터의 저장, 삭제, 수정, 조회와 같은 행위를 한다.

• SELECT ( 일반적으로 TABLE에서 원하는 데이터들을 조회할 때 사용한다. )

SELECT 컬럼목록 FROM 테이블명 [WHERE 조건];

• INSERT ( TABLE에 새로운 데이터들을 삽입할 때 사용한다. ) 

INSERT INTO 테이블명 (컬럼목록) VALUES (값목록);

 ● DELETE

• TABLE에서 특정한 조건에 맞는 데이터들을 삭제할 때 사용한다.
• WHERE 조건이 없을 경우, 모든 데이터가 삭제 된다.

DELETE FROM 테이블명 [WHERE 조건];

 ● UPDATE

• TABLE에서 특정한 조건에 맞는 데이터들을 수정할 때 사용한다.
• WHERE 조건이 없을 경우, 모든 데이터가 수정된다

UPDATE 테이블명 SET 컬럼 = 값 [WHERE 조건];

DCL ( Data Control Language )

데이터베이스에 대한 권한과 관련된 문법으로 특정 유저가 DB에 접근할 수 있는 권한을 설정할 때 사용한다.

 ● GRANT

• 데이터베이스의 특정한 유저에게 사용 권한을 부여할 때 사용한다.
• 권한에는 SELECT, INSERT, UPDATE, DELETE 등 다양한 종류가 존재한다.

GRANT [권한] ON 객체명 TO 사용자;

 ● REVOKE

• 데이터베이스의 특정한 유저에게 사용 권한을 취소할 때 사용한다.

REVOKE [권한] ON 객체명 FROM 사용자;

TCL ( Transaction Control Language )

데이터베이스 내의 트랜잭션을 관리하는 문법으로, 트랜잭션의 시작과 종료, 그리고 롤백을 수행할 때 사용한다.

 ● COMMIT

• 데이터베이스의 작업이 정상적으로 완료되었음을 관리자에게 알려줄 때 사용한다.

START TRANSACTION;
...
COMMIT;

  ● ROLLBACK

• 데이터베이스의 작업이 비정상적으로 완료되었음을 관리자에게 알려줄 때 사용한다.
• 작업 중 오류가 발생한 경우 이를 취소하고, 이전 상태로 되돌릴 때 사용한다.

START TRANSACTION;
...
ROLLBACK;