MariaDB DDL 실습

🚀 SQL DDL & 제약조건 통합 실습

1. 부모 테이블 생성: 부서 정보 (tDepart)

가장 먼저 참조될 부모 테이블을 만듦. 기본키와 CHECK 제약조건을 포함함.

CREATE TABLE tDepart (
    depart VARCHAR(20) PRIMARY KEY,      -- 부서명 (기본키)
    area VARCHAR(10) NOT NULL,           -- 근무 지역
    floor INT CHECK (floor >= 1)         -- 층수 (1층 이상만 가능)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 초기 데이터 삽입
INSERT INTO tDepart VALUES ('인사과', '서울', 3);
INSERT INTO tDepart VALUES ('영업부', '부산', 5);
INSERT INTO tDepart VALUES ('개발부', '판교', 10);

2. 자식 테이블 생성: 직원 정보 (tEmployee)

외래키(FOREIGN KEY), 기본값(DEFAULT), 자동 증가(AUTO_INCREMENT) 등 모든 옵션을 적용함

CREATE TABLE tEmployee (
    empID INT AUTO_INCREMENT PRIMARY KEY, -- 사번 (자동증가, 기본키)
    name VARCHAR(20) NOT NULL,             -- 이름 (필수입력)
    gender CHAR(3) CHECK(gender IN ('남', '여')), -- 성별 (체크 제약조건)
    salary INT DEFAULT 200,                -- 급여 (미입력 시 200)
    depart VARCHAR(20),                    -- 부서명 (외래키 컬럼)
    email VARCHAR(100) COLLATE utf8mb4_bin UNIQUE, -- 이메일 (대소문자 구분, 중복불가)
    
    -- 외래키 설정: 부서가 삭제되면 해당 직원의 부서 정보는 NULL로 변경
    CONSTRAINT FK_dept FOREIGN KEY(depart) 
    REFERENCES tDepart(depart) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=100; -- 사번 100번부터 시작

3. 구조 수정 및 제약조건 관리 실습

테이블 생성 후 구조를 변경하는 다양한 패턴

-- 1. 새로운 컬럼 추가 (나이)
ALTER TABLE tEmployee ADD age INT AFTER name;

-- 2. 컬럼 속성 변경 (나이 컬럼에 CHECK 제약조건 추가)
ALTER TABLE tEmployee MODIFY age INT CHECK (age >= 19);

-- 3. 컬럼 이름 변경 (salary -> pay)
ALTER TABLE tEmployee CHANGE salary pay INT DEFAULT 200;

-- 4. 제약조건 삭제 (이메일 유니크 제약조건 삭제 예시)
-- 실제 삭제 시에는 SHOW CREATE TABLE tEmployee; 로 정확한 제약조건명을 확인해야 함
ALTER TABLE tEmployee DROP CONSTRAINT email; 

4. 데이터 무결성 테스트 (에러 케이스)

제약조건이 잘 작동하는지 확인하는 테스트 코드

-- [에러] 성별 체크 위반
INSERT INTO tEmployee(name, gender) VALUES ('홍길동', '중성'); 

-- [에러] 존재하지 않는 부서 참조 (참조 무결성 위반)
INSERT INTO tEmployee(name, depart) VALUES ('이몽룡', '우주정거장'); 

-- [정상] 기본값 사용 테스트 (pay를 안 넣으면 200 들어감)
INSERT INTO tEmployee(name, gender, depart) VALUES ('성춘향', '여', '인사과');

5. 테이블 삭제 및 초기화

-- 데이터만 전체 삭제 (구조 유지)
TRUNCATE TABLE tEmployee;

-- 테이블 완전 삭제 (자식 먼저 지워야 함)
DROP TABLE tEmployee;
DROP TABLE tDepart;