1. Constraints(제약사항)

데이터의 무결성을 지키기 위해서 사용되는 제한 조건이에요. 쉽게 말하면 무결성을 보장해주는 장치죠!

몇 가지 제약사항의 종류가 있어요.

1.1 PRIMARY KEY 제약조건

• 중복을 허용하지 않아요!
• NULL을 허용하지 않아요!

Primary Key를 설정하면 자동으로 Index가 설정돼요!

DROP TABLE IF EXISTS testUserTBL;

CREATE TABLE testUserTBL (
    userID CHAR(8) PRIMARY KEY NOT NULL,
    name VARCHAR(10) NOT NULL
);

DROP TABLE IF EXISTS testUserTBL;

CREATE TABLE testUserTBL (
    userID CHAR(8) NOT NULL,
    name VARCHAR(10) NOT NULL,
    CONSTRAINT PRIMARY KEY pk_testUserTBL_userID (userID)
);

DROP TABLE IF EXISTS testUserTBL;

CREATE TABLE testUserTBL (
    userID CHAR(8) NOT NULL,
    name VARCHAR(10) NOT NULL
);

ALTER TABLE testUserTBL
    ADD CONSTRAINT pk_testUserTBL_userID
    PRIMARY KEY (userID);

1.2 FOREIGN KEY 제약조건

다른 테이블의 Primary Key로 되어 있는 column을 reference하는 컬럼을 하나 만들 수 있는데 이 컬럼에 Foreign Key를 지정하면 제약조건이 생기게 돼요!

• Foreign Key로 설정된 컬럼에 데이터가 입력될 때 반드시 기준 테이블의 Primary Key 컬럼에 해당 데이터가 포함되어 있어야 해요!

DROP TABLE IF EXISTS testUserTBL;
DROP TABLE IF EXISTS testBuyTBL;

CREATE TABLE testUserTBL (
    userID CHAR(8) PRIMARY KEY NOT NULL,
    name VARCHAR(10) NOT NULL
);

CREATE TABLE testBuyTBL (
    num INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    userID CHAR(8) NOT NULL,
    prudName CHAR(10) NOT NULL,
    FOREIGN KEY(userID) REFERENCES testUserTBL(userID)
    ON DELETE CASCADE
);

1.3 UNIQUE 제약조건

중복되지 않은 값을 사용해야 한다는 제약조건이에요. 컬럼에 설정하면 해당 컬럼 안에는 중복된 값이 나올 수 없어요!

NULL이 나올 수 있어요! 게다가 여러 개의 NULL 값이 사용될 수 있어요!

많이 적용되는 컬럼은 회원 테이블의 EMAIL 같은 것들이 UNIQUE 제약조건을 가지고 있어요. 데이터를 유일하게 식별할 수 있는 기능이에요. ⇒ Index가 자동으로 생성돼요!

CREATE TABLE testUserEBL(
    userId VARCHAR(10) PRIMARY KEY NOT NULL,  -- index가 설정
    name CHAR(8) NOT NULL,
    email CHAR(30) UNIQUE -- index가 설정
);

1.4 CHECK 제약조건

DROP TABLE IF EXISTS testUserTBL;

CREATE TABLE testUserTBL(
    userId VARCHAR(10) PRIMARY KEY NOT NULL,  -- index가 설정
    name CHAR(8) NOT NULL,
    email CHAR(30) UNIQUE, -- index가 설정
    birthYear INT CHECK(birthYear > 2000 AND birthYear <= 2024)    
);

INSERT INTO testUserTBL VALUES ('hong', '홍길동', NULL, 2010);
INSERT INTO testUserTBL VALUES ('kim', '김길동', NULL, 1999); -- ERROR

1.5 DEFAULT 선언

USE sqldb;

DROP TABLE IF EXISTS testUserTBL;

CREATE TABLE testUserTBL(
    userId VARCHAR(10) PRIMARY KEY NOT NULL,  -- index가 설정
    name CHAR(8) NOT NULL,
    email CHAR(30) UNIQUE, -- index가 설정
    birthYear INT DEFAULT -1
);

INSERT INTO testUserTBL(userId, name, email) VALUES ('hong', '홍길동', NULL);

2. VIEW

실제로 데이터를 가지고 있지 않은 Table 대신 사용할 수 있는 객체예요.

CREATE OR REPLACE VIEW v_userTBL AS
    SELECT userId, name
    FROM userTBL;

SELECT *
FROM v_userTBL;

그냥 Table을 직접 이용하면 되는 걸 왜 View라는 걸 만들어서 사용하는 걸까요?

1. 보안적인 측면이에요.
2. 복잡한 쿼리를 단순화 시킬 수 있어요!

그러면 VIEW를 통한 INSERT, UPDATE, DELETE 같은 작업도 가능한가요? 할 수 있어요! VIEW 안에는 반드시 SELECT만 와야 하는 건 아니에요! 하지만 일반적으로 VIEW를 통한 수정 작업은 하지 않아요. ⇒ READ ONLY 형태로 사용하는 게 일반적이에요!

3. Index

INDEX는 Table 안의 데이터를 조금 더 빠르게 찾을 수 있도록 도와주는 도구라고 생각하면 돼요!

물론 단점도 가지고 있어요! Index를 사용하면 데이터베이스 전체 크기가 증가해요. 약 10% 정도 증가해요!

Index를 잘못 설정하면 전체 DBMS의 성능을 낮출 수 있어요!

Index의 종류는 다음과 같아요.

• Clustered Index(클러스터형 인덱스)
• Secondary Index(보조 인덱스), NonClustered Index라고 하기도 해요!

Clustered Index는 사전 같은 개념이에요. 클러스터형 Index는 테이블에 무조건 1개만 존재할 수 있어요! PRIMARY KEY로 지정하면 자동적으로 해당 컬럼에 Clustered Index가 설정돼요!

테이블에는 클러스터형 인덱스가 많아야 1개만 존재하고 일반 인덱스(Secondary Index)는 여러 개 존재할 수 있어요!

CREATE TABLE tbl1(
    a INT PRIMARY KEY,
    b INT,
    c INT
);

SHOW INDEX FROM tbl1;

DROP TABLE IF EXISTS tbl1;

CREATE TABLE tbl1(
    a INT PRIMARY KEY,
    b INT UNIQUE,
    c INT UNIQUE,
    d INT
);

DROP TABLE IF EXISTS tbl1;

CREATE TABLE tbl1(
    a INT UNIQUE NOT NULL,
    b INT UNIQUE,
    c INT UNIQUE,
    d INT
);

Clustered Index와 Secondary Index의 가장 큰 차이는 무엇인가요?

DROP TABLE IF EXISTS userTestTBL1;

CREATE TABLE userTestTBL1(
    userID CHAR(8) NOT NULL,
    name VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    addr CHAR(2) NOT NULL
);

INSERT INTO userTestTBL1 VALUES('LSG', '이승기', 1987, '서울');
INSERT INTO userTestTBL1 VALUES('KBS', '김범수', 1979, '경남');
INSERT INTO userTestTBL1 VALUES('KKH', '김경호', 1971, '전남');
INSERT INTO userTestTBL1 VALUES('JYP', '조용필', 1950, '경기');
INSERT INTO userTestTBL1 VALUES('SSK', '성시경', 1979, '서울');

SELECT *
FROM userTestTBL1;

ALTER TABLE userTestTBL1
    ADD CONSTRAINT pk_userTestTBL1 PRIMARY KEY (userID);

ALTER TABLE userTestTBL1  
    DROP PRIMARY KEY;

ALTER TABLE userTestTBL1
    ADD CONSTRAINT pk_userTestTBL1 PRIMARY KEY (name);

SELECT *
FROM userTestTBL1;

4. Index의 내부구조

Index를 설정하면 내부적으로 B-TREE가 생성돼요!

B-TREE의 기본 구조를 알아봐요!