SQL DDL & 제약조건 실습 정리

DDL(Data Definition Language)

• DDL은 데이터 정의 언어로, 데이터베이스 객체(테이블, 뷰, 인덱스 등)를 생성/수정/삭제하는 명령어이다.
• 주요 구문
  • CREATE : 객체 생성
  • ALTER : 객체 수정
  • DROP : 객체 삭제
  • TRUNCATE : 데이터 전체 삭제 (롤백 불가)

CREATE TABLE DEPT (
    DEPTNO INT,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);

• 위의 코드는 부서 정보를 담는 테이블을 생성하는 코드이다.
• 이후에 ALTER TABLE을 통해 기본키 제약조건을 추가할 수 있다.

 

 

 

제약조건(Constraints)

제약조건은 데이터 무결성을 보장하기 위해 특정 컬럼 값에 제한을 두는 규칙이다.

종류

• PRIMARY KEY
  • 행을 고유하게 식별한다.
  • NOT NULL + UNIQUE 성질을 가진다.
  • PK 자체는 한 테이블에 하나만 존재해야 한다 (PK를 여러 컬럼으로 구성하는 건 가능하다).

  CONSTRAINT pk_member PRIMARY KEY (id)
  

   
  •  회원 번호, 주문 번호 같은 고유 값에 사용하면 된다.
• FOREIGN KEY
  • 다른 테이블의 PK/UNIQUE 값을 참조한다.
  • 참조 무결성을 보장한다.

  CONSTRAINT fk_orders_customer_id
  FOREIGN KEY (customer_id) REFERENCES member(id)
  

• NOT NULL
  • 반드시 값이 입력되어야 한다.

  user_pw VARCHAR(15) NOT NULL
  

• UNIQUE
  • 중복이 불가능하다.
  • 단, NULL은 허용한다.

  user_email VARCHAR(30) UNIQUE
  

• CHECK
  • 특정 조건 만족 시에만 입력이 허용된다.
  • MySQL 8.0.16 이후부터 지원되는 제약조건이다.

  age INT CHECK (age BETWEEN 18 AND 65)
  

• DEFAULT
  • 값 입력이 없을 때 기본값을 자동으로 설정할 수 있다.

  status VARCHAR(5) DEFAULT 'N'
  

 

 

 

테이블 생성 실습

(1) 회원 테이블

CREATE TABLE member_test (
    user_no INT AUTO_INCREMENT COMMENT '회원번호',
    user_id VARCHAR(30) NOT NULL COMMENT '회원아이디',
    user_pwd VARCHAR(30) NOT NULL COMMENT '회원비밀번호',
    pno VARCHAR(20) NOT NULL COMMENT '주민등록번호',
    gender VARCHAR(3) COMMENT '성별',
    phone VARCHAR(15) COMMENT '연락처',
    address VARCHAR(50) COMMENT '주소',
    status VARCHAR(5) NOT NULL COMMENT '탈퇴여부',

    CONSTRAINT pk_member_test_id PRIMARY KEY (user_no),
    CONSTRAINT uq_member_test_user_id UNIQUE (user_id),
    CONSTRAINT uq_member_test_pno UNIQUE (pno),
    CONSTRAINT ck_member_test_gender CHECK (gender IN ('남','여')),
    CONSTRAINT ck_member_test_status CHECK (status IN ('Y','N'))
);

• 여기서 제약조건 이름을 직접 지정할 수 있다. → 유지보수 용이
  • pk_member_test_id, uq_member_test_user_id 등이 제약조건 이름에 해당한다.

(2) 회원-게시판-댓글 관계

CREATE TABLE member (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10)
);

CREATE TABLE board (
    no INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50),
    writer INT,
    CONSTRAINT fk_board_member FOREIGN KEY (writer) REFERENCES member(id)
);

CREATE TABLE reply (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    oriid INT,
    CONSTRAINT fk_reply_board FOREIGN KEY (oriid) REFERENCES board(no)
);

• 게시판 구조 예시
  • member : 작성자 정보
  • board : 글 정보, 작성자(writer)가 member.id를 참조
  • reply : 댓글, 원글(oriid)이 board.no를 참조

 

 

 

구조 복사

• 데이터 제외 구조 복사
  • 컬럼 구조만 복사된다 (단, PK, AUTO_INCREMENT, DEFAULT는 복사되지 않음).

CREATE TABLE member2 AS SELECT * FROM member WHERE 1=0;

• 데이터 포함 복사

CREATE TABLE member3 AS SELECT * FROM member;

• LIKE 문법
  • NOT NULL, PK, AUTO_INCREMENT까지 같이 복사된다 (단, FK 제외).

CREATE TABLE emp2 LIKE emp;

 

 

 

ALTER TABLE 활용

• 컬럼 추가

ALTER TABLE test22 ADD addr VARCHAR(100);

• 컬럼 수정

ALTER TABLE test22 MODIFY addr VARCHAR(20);

• 컬럼 이름 변경

ALTER TABLE test22 RENAME COLUMN addr TO addr2;

• 제약조건 추가

ALTER TABLE test22 ADD CONSTRAINT pk_test22_id PRIMARY KEY (id);

• 제약조건 삭제

ALTER TABLE test22 DROP PRIMARY KEY; 
ALTER TABLE test22 DROP FOREIGN KEY fk_test22_test11;

 

 

 

ON DELETE / ON UPDATE CASCADE

• 부모 테이블 값이 변경될 때 자식 테이블이 자동으로 연쇄 반영된다.

CREATE TABLE board (
    board_id INT AUTO_INCREMENT PRIMARY KEY,
    writer_id INT,
    CONSTRAINT fk_writer FOREIGN KEY (writer_id)
    REFERENCES member(member_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

• 이 코드의 경우, 회원이 삭제되면 해당 회원이 작성한 글도 자동 삭제된다.

 

 

 

INSERT IGNORE

• UNIQUE 제약조건에 위배되면 에러 대신 무시한다.
• 에러를 내지 않고 경고를 발생시킨 튀 해당 행은 건너뛰는 것이다.

INSERT IGNORE INTO TB_EMP (name, position) VALUES ('제니', 'Manager');

 

 

 

정리하자면…

• DDL은 데이터 구조를 다루는 언어이다 (CREATE, ALTER, DROP, TRUNCATE).
• 제약조건으로 데이터 무결성을 보장할 수 있다 (PK, FK, NOT NULL, UNIQUE, CHECK, DEFAULT).
• CREATE ... LIKE, CREATE ... AS SELECT을 통해 테이블 구조/데이터를 복사할 수 있다.
• ALTER TABLE → 컬럼/제약조건 추가, 수정, 삭제할 수 있다.
• ON DELETE CASCADE, ON UPDATE CASCADE → 부모-자식 테이블이 연쇄 동작한다.
• (MySQL 기준으로) TRUNCATE는 롤백 불가, DELETE는 롤백 가능.