MariaDB DDL

키(Key) 개념 정리

종류	설명	특징
슈퍼키 (Super Key)	레코드를 유일하게 식별할 수 있는 속성들의 집합	유일성 만족 (최소성은 만족 안 해도 됨)
후보키 (Candidate Key)	슈퍼키 중 최소성을 만족하는 키	기본키가 될 수 있는 후보들
기본키 (Primary Key)	후보키 중 설계자가 지정한 메인 키	NULL 불가, 중복 불가 (하나만 설정)
대체키 (Alternate Key)	후보키 중 기본키로 선택되지 않은 나머지	기본키 부재 시 식별자로 활용 가능
참조키 (Foreign Key)	다른 테이블의 기본키를 참조하는 키	참조 무결성 유지, NULL 가능

 

🏗️ SQL 테이블 생성 및 데이터 타입 정리

CREAT [TEMPORARY] TABLE [IF NOT EXISTS] 테이블명(
	컬럼명1 타입 [CONSTRAINT 제약조건이름] 컬럼제약조건,
	컬럼명2 타입,
	컬럼명3 타입,
	...
	[CONSTRAINT 제약조건이름] 테이블 제약 조건) ENGINE = 엔진명;

1. 테이블 생성 기본 문법 (DDL)

• 기본 구조: CREATE TABLE 뒤에 테이블명과 컬럼 정보를 나열함.
• 옵션:
  • TEMPORARY: 임시 테이블 생성 시 사용함.
  • IF NOT EXISTS: 동일 이름의 테이블이 없을 때만 생성하여 에러를 방지함.
  • ENGINE: 스토리지 엔진 설정 (MyISAM, InnoDB 등).
  • DEFAULT CHARSET: 인코딩 설정 (utf8mb4 등).

---

2. 주요 데이터 타입 (Data Type)

• 숫자형:
  • INT: 정수.
  • DECIMAL(전체, 소수): 고정 소수점, 정확한 수치 계산에 사용함.
  • FLOAT, DOUBLE: 부동 소수점.
• 문자형:
  • CHAR(n): 고정 길이. 공간이 남으면 공백으로 채움. 비교 시 공백 제거(trim)함.
  • VARCHAR(n): 가변 길이. 실제 데이터만큼만 저장해서 메모리 효율 좋음. (수정 시 Row Migration 발생 가능)
  • TEXT / LONGTEXT: 대용량 문자열 (최대 43억 자).
  • BLOB / LONGBLOB: 이미지, 파일 등 바이너리 데이터 저장용.
• 날짜 및 기타:
  • DATE / DATETIME: 날짜 및 시간.
  • TIMESTAMP: 정밀한 시간 기록용.
  • JSON, GEOMETRY(공간 정보) 등 특수 타입 지원함.

3. 스토리지 엔진 (ENGINE) 비교

엔진 종류	특징	장점
MyISAM	인덱스 지원 구조	조회(Select) 성능이 뛰어남
InnoDB	트랜잭션 지원, 외래키 지원	변경(Insert/Update) 작업에 유리함

 

4. 주요 설정 옵션

• AUTO_INCREMENT: 1부터 시작하는 시퀀스 번호 자동 생성 (초기값 설정 가능).
• COLLATE: 문자열 정렬 및 비교 규칙.
  • _ci: 대소문자 구분 안 함 (Case Insensitive).
  • _bin: 이진 데이터로 비교하여 대소문자 구분함 (Binary).
• CHARACTER SET:
  • utf8mb4: 이모지(😀)까지 저장 가능한 인코딩 방식.

🛠️ 실전 테이블 생성 및 수정 (DDL 실습)

1. 요구사항에 맞춘 테이블 생성 예시

• 테이블명: contact
• 요구사항 및 설정:
  • 일련번호: num, 기본키, 1부터 자동 증가 (AUTO_INCREMENT=1)
  • 이름: 변경 적음, 20자 (VARCHAR보다 고정형이 유리할 수 있으나 유연성 위해 지정)
  • 주소/이메일: 변경 잦음, 100자 (VARCHAR 사용)
  • 전화번호: 자릿수 고정, 20자 (CHAR 사용)
  • 특이사항: 이메일 대소문자 구분(utf8mb4_bin), 이모티콘 저장 가능(utf8mb4), 읽기 위주(MyISAM)

 

CREATE TABLE contact(
    num INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    address VARCHAR(100), -- 변경 잦은 데이터는 VARCHAR가 효율적
    tel CHAR(20),         -- 자릿수 고정 데이터는 CHAR
    email VARCHAR(100) COLLATE utf8mb4_bin, -- 대소문자 구분 설정
    birthday DATE	
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

-- 구조 확인 명령어
DESC contact;

---

2. 테이블 구조 수정 (ALTER)

• 컬럼 추가: ADD 사용. 위치 지정(FIRST, AFTER) 안 하면 맨 뒤에 추가됨.
  • ALTER TABLE contact ADD age INT AFTER name;
• 컬럼 삭제: DROP 사용.
  • ALTER TABLE contact DROP age;
• 컬럼 이름 및 타입 변경: CHANGE는 이름까지 변경, MODIFY는 타입/제약조건만 변경.
  • ALTER TABLE contact CHANGE tel phone VARCHAR(20);
• 컬럼 순서 조정: MODIFY COLUMN으로 위치 재설정 가능.
  • ALTER TABLE contact MODIFY COLUMN phone VARCHAR(20) FIRST;

---

3. 테이블 관리 및 삭제

• 테이블 이름 변경: RENAME
  • ALTER TABLE contact RENAME my_contact;
• 테이블 삭제: DROP (구조까지 완전히 삭제)
  • DROP TABLE contact;
• 데이터 전체 삭제: TRUNCATE (구조는 남기고 데이터만 즉시 삭제, DELETE보다 빠름)
  • TRUNCATE TABLE contact;
• 테이블 확인: SHOW TABLES;

---

4. 기타 유용한 설정

• 테이블 압축: 용량 최적화가 필요할 때 사용 (작업 시간은 늘어남).
  • CREATE TABLE ... ROW_FORMAT=COMPRESSED;
• 주석 설정: 테이블이나 컬럼에 설명을 달아둘 때 사용.
  • ALTER TABLE contact COMMENT = '연락처 관리 테이블';

🛡️ 데이터 무결성 제약조건 (Integrity Constraint)

1. 무결성이란?

• 데이터의 정확성과 일관성을 유지하고 보증하는 것.
• 잘못된 데이터가 들어오지 못하게 막는 방어막 역할을 함.

2. 무결성의 종류 (적용 범위)

• 개체 무결성(Entity Integrity): PRIMARY KEY는 NULL이거나 중복될 수 없음. (각 행을 유일하게 식별)
• 참조 무결성(Referential Integrity): FOREIGN KEY는 참조하는 테이블에 실제 존재하는 값이거나 NULL이어야 함.
• 도메인 무결성(Domain Integrity): 컬럼 값은 지정된 타입, 범위, 기본값 등의 규칙을 만족해야 함 (원자값 유지).

---

3. 주요 제약조건 실습 정리

① NOT NULL (필수 입력)

• 데이터를 넣을 때 반드시 값이 있어야 함.
• 설정 및 수정 시 주의: 제약조건 수정이 아니라 MODIFY를 통한 자료형 변경으로 처리함.

CREATE TABLE tNullAble(
    name CHAR(10) NOT NULL, -- 이름 필수
    age INT
);
-- name 없이 age만 넣으려 하면 에러 발생!

② CHECK (값의 범위 및 종류 제한)

• 특정 컬럼이 가질 수 있는 값의 범위를 설정함.
• OR, IN, BETWEEN, LIKE 등을 활용해 정교하게 설정 가능함.

 

CREATE TABLE tCheckTest(
    gender CHAR(3) CHECK(gender IN('남','여')),         -- 남/여만 가능
    origin CHAR(3) CHECK(origin IN('동','서','남','북')), -- 방위만 가능
    grade INT CHECK(grade BETWEEN 1 AND 5),           -- 1~5 숫자만 가능
    name VARCHAR(10) CHECK(name LIKE '박%')            -- '박'씨만 가능
);

③ UNIQUE (중복 배제)

• 값의 중복을 허용하지 않음 (단, NULL은 허용 가능).
• 단일 컬럼: 개별 컬럼마다 중복 체크.
• 복합 컬럼(테이블 제약 조건): 여러 컬럼을 묶어서 중복 체크.

 

-- 1. 각각 중복 체크 (area도 유일, popu도 유일해야 함)
CREATE TABLE tUniqueTest(
    area INT UNIQUE,
    popu INT UNIQUE
);

-- 2. 조합 중복 체크 (area+popu 합친 세트가 유일하면 됨)
CREATE TABLE tUniqueTest(
    area INT,
    popu INT,
    CONSTRAINT UK_area_popu UNIQUE(area, popu)
);

 

🔑 PK(기본키)와 FK(외래키) 완벽 정리

1. PRIMARY KEY (기본키)

• 특징: 테이블당 딱 하나만 설정 가능함. NOT NULL과 UNIQUE 성격을 동시에 가짐.
• 설정 방식:
  • 단일 컬럼: 컬럼 정의 시 옆에 바로 적거나 테이블 제약조건으로 설정함.
  • 복합 키: 2개 이상의 컬럼을 묶어서 PK로 쓸 때는 반드시 하단에 CONSTRAINT 문법을 사용해야 함.

---

2. FOREIGN KEY (외래키) & 참조 무결성

• 목적: 테이블 간의 관계를 맺고, 데이터의 일관성을 유지함.
• 규칙: 참조하는 테이블(자식)의 외래키 값은 참조되는 테이블(부모)의 기본키에 반드시 존재하거나 NULL이어야 함.
  • 예: 직원 테이블(tEmployee)에 없는 '카리나'는 프로젝트 테이블(tProject)에 등록될 수 없음.

-- 부모 테이블: 직원
CREATE TABLE tEmployee(
    name CHAR(10) PRIMARY KEY,
    salary INT NOT NULL,
    addr VARCHAR(30) NOT NULL
);

-- 자식 테이블: 프로젝트 (직원의 이름을 참조)
CREATE TABLE tProject(
    projectID INT PRIMARY KEY,
    name CHAR(10),
    project VARCHAR(30) NOT NULL,
    cost INT,
    CONSTRAINT FK_emp FOREIGN KEY(name) REFERENCES tEmployee(name)
);

---

3. 삭제/수정 시 데이터 보호 법칙

• 참조 중인 데이터 보호: 프로젝트를 수행 중인 직원은 마음대로 삭제할 수 없음 (DELETE 에러).
• 테이블 삭제: 외래키로 참조당하고 있는 부모 테이블은 자식 테이블보다 먼저 삭제할 수 없음.

---

4. 외래키 옵션 (ON DELETE / ON UPDATE)

데이터가 삭제되거나 수정될 때 자식 데이터를 어떻게 처리할지 결정함.

옵션	설명
NO ACTION	기본값. 참조 중이면 부모 데이터 삭제/수정 불가
CASCADE	부모를 삭제하면 참조하던 자식 데이터도 연쇄 삭제됨
SET NULL	부모 삭제 시 자식의 외래키 값을 NULL로 바꿈
SET DEFAULT	부모 삭제 시 자식의 외래키 값을 기본값으로 바꿈

실무 팁: ON DELETE CASCADE는 편리하지만, 부주의하게 사용하면 연쇄적으로 소중한 데이터가 날아갈 수 있으니 주의해서 사용해야 함!

 

🛠️ 제약 조건 관리 및 기본값 설정

1. 제약 조건 확인 및 수정

• 확인하기: 내가 만든 제약 조건들이 잘 들어갔는지 시스템 뷰에서 조회 가능함.
  • SELECT * FROM information_schema.table_constraints;
• 수정하기 (MODIFY): 기존 컬럼의 제약 조건을 바꿀 때 사용함.
  • ALTER TABLE 테이블명 MODIFY 컬럼명 자료형 제약조건;
• 추가하기 (ADD): 새로운 제약 조건을 추가함.
  • ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 제약조건(컬럼명);
• 삭제하기 (DROP): 설정된 제약 조건을 이름을 지정해서 지움.
  • ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름;

---

2. 기본값 설정 (DEFAULT)

• 개념: 데이터를 넣을 때 해당 컬럼 값을 생략하면 자동으로 미리 정해둔 값이 들어감.
• 설정 방식: 자료형 뒤에 DEFAULT 값을 명시함.
• 효과: 필수 입력은 아니지만 값이 없을 때 '0'이나 '미정' 등의 초기값을 주고 싶을 때 유용함.

CREATE TABLE tEmployee(
    name CHAR(10) PRIMARY KEY,
    salary INT DEFAULT 0,       -- 급여를 안 넣으면 0으로 저장됨
    addr VARCHAR(30) NOT NULL
);

-- 1. 값을 모두 넣는 경우
INSERT INTO tEmployee VALUES('아이린', 200, '대구'); 

-- 2. 특정 컬럼(salary)을 생략하는 경우 -> DEFAULT 0 작동!
INSERT INTO tEmployee(name, addr) VALUES('카리나', '서울');