[ 멋쟁이사자처럼부트캠프 그로스마케팅 부트캠프 ] SQL 심화: 제약 조건, Table 연결

 

1. 주요 제약조건

1) 고유한 ID : PRIMARY KEY

• 주로 테이블의 각 행을 고유하게 식별하기 위해 사용
• 해당 컬럼의 값이 중복되지 않도록 보장
• NULL 값 불가, 이로 인해 모든 데이터가 고유하고 명확한 식별자를 가지게 됨
• 설정하면 데이터베이스가 자동으로 인덱스를 생성, 검색 속도가 매우 빠르게 최적화됨
• 따라서 데이터베이스가 고윳값으로 인식하는 칼럼은 한 테이블에서 하나만 존재할 수 있음

CREATE TABLE students (
    student_id INT PRIMARY KEY,   -- student_id 컬럼을 식별자로 지정
    name VARCHAR(50)
);

 

2) 유일성 보장 : UNIQUE

• PRIMARY KEY와 같이 해당 컬럼의 값이 중복되지 않도록 보장
• PRIMARY KEY와 달리 데이터베이스에서 고유한 식별자로 인식하지 않음
• 따라서 한 테이블에서 여러 컬럼에 동시 설정 가능

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,   -- 식별자 지정
    email VARCHAR(100) UNIQUE      -- 유일성 보장(같은 이메일이 세상에 2개 존재할 수는 없다)
    phone_num VARCHAR(50) UNIQUE   -- 유일성 보장(전화번호 또한 마찬가지)
    name VARCHAR(50)               -- 같은 이름이 존재할 수 있기 때문에 UNIQUE 제약을 걸지 않는다
);

 

3) 빈칸 금지 : NOT NULL

• 해당 컬럼에 NULL 값을 저장할 수 없도록 함

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL    -- NOT NULL로 인해 해당 칼럼의 값을 비워둘 수 없다
);

INSERT INTO products (product_id, name)
VALUES
(112345, );    -- name 컬럼의 값 위치를 비웠기 때문에 에러 발생

 

4) 기본값 설정 : DEFAULT

• 데이터 삽입 시 값이 지정되지 않으면 설정한 기본값을 자동으로 지정

CREATE TABLE me (
    name VARCHAR(20) PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'Hungry'   -- status(상태) 칼럼의 기본 값 'Hungry'
);

INSERT INTO me (name, status)
VALUES
('Daren', );    -- 값을 비워두면 기본 값인 'Hungry' 부여

 

5) 조건 부 저장 : CHECK

• 컬럼에 입력되는 데이터가 특정 조건을 만족해야만 저장됨

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2) CHECK (balance >= 0)  -- balance가 0 이상인 경우에만 저장
);

 

6) 외래 키 : FOREIGN KEY

• 한 테이블의 컬럼이 다른 테이블의 고유 키를 참조하도록 설정
• A 테이블의 a 컬럼이 고유값(PRIMARY KEY) 컬럼이고 B 테이블의 b 컬럼이 이를 참조한다면 b 컬럼의 값은 a 컬럼의 값 안에서 저장할 수 있음
• 이해를 돕기 위해 비유하자면 단어와 문장 간의 관계라고 볼 수 있음. 문장은 단어를 참조함. 우리가 쓰지 않는 단어는 또한 우리가 쓰는 문장에 존재하지 않음. 만약 아무도 모르는 단어를 혼자 문장에 사용한다면 그 문장은 이해할 수 없기에 유용하지 않음. 외래키도 마찬가지로 고윳값 칼럼을 참조하여 참조된 칼럼의 유용성과 신뢰성을 확보함.
• 아래 옵션을 설정하지 않는다면 기본적으로 참조된 컬럼(a)은 참조(b)가 사라지기 전까지 삭제와 수정 불가
• ON DELETE CASCADE : 참조된 데이터가 삭제되면 같이 삭제됨
• ON UPDATE CASCADE : 참조된 데이터가 수정되면 같이 수정됨

-- 전체 학생 목록 TABLE
CREATE TABLE student_all (         -- id, 이름 두 column으로 구성 
    student_id INT PRIMARY KEY,    -- 학생 id 고유값
    name VARCHAR(50)
    );

-- 1반 학생 목록 TABLE
CREATE TABLE student_class1 (      -- id, 이름, 출석여부 세 column으로 구성 
    student_id INT,
    name VARCHAR(50),
    status VARCHAR(1),
    FOREIGN KEY (student_id) REFERENCES student_all(student_id) ON DELETE CASCADE  -- student_all Table의 student_id 컬럼을 참조, 참조된 데이터가 삭제되면 같이 삭제됨
    );

 

7)  자동 증가 : AUTO_INCREMENT

• 새로운 행이 추가될 때마다 자동으로 숫자를 증가시켜 고유한 값을 부여
• 주로 PRIMARY KEY와 함께 사용 

CREATE TABLE tickets (
    ticket_id INT PRIMARY KEY AUTO_INCREMENT,  -- 행이 추가될 때마다 정수값 자동 증가
    event_name VARCHAR(100)
);

 

2. 여러 테이블 다루기

1) 조건 부 연결 : JOIN

• 두 개 이상의 테이블을 연결해서 데이터를 조회
• 여러 테이블에 나눠 저장된 정보를 하나의 결과로 통합할 수 있음
• 조건을 설정하여 두 테이블 간 데이터를 연결

SELECT 컬럼1, 컬럼2, 컬럼3 등 -- 결과값에 넣을 컬럼 설정
FROM 표1  -- 합칠 재료가 될 표 1
JOIN 표2   -- 합칠 재료가 될 표2
ON 조건  -- 조건에 부합하는 행만 반환

• 샘플 테이블 설정

students 테이블

enrollments 테이블

• INNER JOIN (교집합, 공통 데이터만 조회)

SELECT students.name, enrollments.course_name  -- 컬럼 설정
FROM students  -- 연결할 표 1
INNER JOIN enrollments   -- 연결할 표 2
ON students.student_id = enrollments.student_id;  -- 조건 : 공통된 student_id가 있는 행만 반환(student_id 값 반환하는거 아님!!)

결과값 (SELECT에서 설정한 컬럼에 해당하는 데이터만 반환)

• LEFT JOIN (왼쪽 테이블(표1)의 모든 데이터 + 오른쪽 테이블(표2)에서 일치하는 데이터)

SELECT students.name, enrollments.course_name  -- 결과값을 만들 칼럼들
FROM students   -- 표1(왼쪽 테이블)
LEFT JOIN enrollments   -- 표2(오른쪽 테이블)
ON students.student_id = enrollments.student_id;   -- 조건

결과값 (표1의 모든 행 포함 + 표2의 조건에 맞는 행 포함)