MySQL TABLE DEFAULT값 — 인라인 기본값 포함 테이블 설계 완전 정리

 

 

MySQL 실습 정리

TABLE DEFAULT 값

CREATE TABLE 단계에서 DEFAULT를 포함해 기본값을 설계한다.

1 핵심 개념

CREATE TABLE 단계에서 DEFAULT를 포함해 기본값을 설계한다. 테이블 제약조건은 단순 문법이 아니라 잘못된 데이터가 들어오는 것을 막는 규칙이다.

ALTER TABLE은 이미 만들어진 테이블에 규칙을 추가하거나 바꾸는 명령이고, CREATE TABLE 안에 직접 제약조건을 넣을 수도 있다. 어떤 방식이든 기존 데이터와 참조 순서를 함께 봐야 한다.

제약조건을 추가하기 전에는 반드시 현재 데이터가 그 규칙을 만족하는지 확인한다. 만족하지 않으면 ALTER TABLE이 실패한다.

2 원본 예제 코드

DROP TABLE IF EXISTS usertbl; CREATE TABLE usertbl ( userID CHAR(8) NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL, birthYear INT NOT NULL DEFAULT -1, addr CHAR(2) NOT NULL DEFAULT '서울', mobile1 CHAR(3) NULL, mobile2 CHAR(8) NULL, height SMALLINT NULL DEFAULT 170, mDate DATE NULL );

3 용어 카드

ALTER TABLE이미 있는 테이블 구조나 제약조건을 변경한다.

CONSTRAINT제약조건에 이름을 붙여 관리할 수 있게 한다.

PRIMARY KEY행을 고유하게 식별하는 기본키다.

FOREIGN KEY부모 테이블의 키를 참조하는 외래키다.

CHECK입력 가능한 값의 범위를 제한한다.

DEFAULT값을 생략했을 때 자동으로 들어갈 기본값이다.

UNIQUE중복 값을 허용하지 않는 제약조건이다.

CASCADE부모 키 변경을 자식 테이블에 전파하는 옵션이다.

 

반응형

 

4 실행 흐름

1

테이블 구조 확인DESC와 SHOW CREATE TABLE로 현재 컬럼과 제약조건을 확인한다.

2

데이터 상태 확인기존 데이터가 새 제약조건을 위반하지 않는지 SELECT로 점검한다.

3

제약조건 적용ALTER TABLE 또는 CREATE TABLE 안에서 제약조건을 선언한다.

4

실패 케이스 테스트중복, NULL, 참조 누락처럼 실패해야 하는 값을 넣어 본다.

5

인덱스와 구조 검산SHOW INDEX 결과로 PK, UNIQUE, FK 관련 인덱스를 확인한다.

6

정리 기준 기록왜 해당 제약조건이 필요한지 주석이나 문서로 남긴다.

5 제약조건 비교

제약조건	막는 문제	대표 예시
PRIMARY KEY	행 식별 불가, 중복 키	userID PRIMARY KEY
FOREIGN KEY	존재하지 않는 부모 참조	buytbl.userID → usertbl.userID
CHECK	범위를 벗어난 값 입력	birthYear BETWEEN 1900 AND 2023
DEFAULT	생략값 처리 불명확	addr DEFAULT '서울'
UNIQUE	중복되면 안 되는 값 중복	email UNIQUE
NOT NULL	필수값 누락	name VARCHAR(10) NOT NULL

6 참조 순서

작업	권장 순서	이유
테이블 생성	부모 테이블 먼저, 자식 테이블 나중	외래키가 참조할 대상이 필요하다.
테이블 삭제	자식 테이블 먼저, 부모 테이블 나중	참조 중인 부모 테이블은 바로 삭제하기 어렵다.
제약 추가	기존 데이터 정리 후 ALTER	위반 데이터가 있으면 추가가 실패한다.
키 변경	CASCADE 정책 확인 후 변경	자식 데이터 변경 여부가 달라진다.

7 INSERT 관점

제약조건은 INSERT 순간에 가장 확실히 드러난다. 기본키 중복, 외래키 참조 실패, CHECK 범위 위반, NOT NULL 누락은 모두 입력 단계에서 막힌다.

입력값	통과 조건	실패 예시
userID	기본키라면 중복이 없어야 한다	이미 있는 userID 재입력
buytbl.userID	usertbl.userID에 존재해야 한다	회원 없는 BBK 구매 입력
birthYear	CHECK 범위 안이어야 한다	1871처럼 범위 밖 연도
email	UNIQUE면 중복이 없어야 한다	같은 이메일 재사용
addr, height	생략 시 DEFAULT가 적용될 수 있다	기본값을 기대했지만 직접 NULL 입력

8 검산 쿼리

DESC usertbl; DESC buytbl; SHOW INDEX FROM buytbl; SHOW CREATE TABLE usertbl; SELECT * FROM usertbl; SELECT * FROM buytbl;

검산 쿼리는 제약조건이 실제로 들어갔는지, 데이터가 예상대로 들어갔는지 확인하는 최소 도구다. 오류가 날 때도 이 결과를 기준으로 원인을 좁힌다.

9 자주 하는 실수

실수	증상	해결
부모 테이블 없이 FK 생성	외래키 생성 실패	부모 테이블과 키를 먼저 만든다.
기존 데이터 정리 없이 CHECK 추가	ALTER TABLE 실패	위반 데이터를 수정하거나 삭제한다.
DEFAULT와 NULL 혼동	기본값이 적용되지 않음	값을 생략할 때만 기본값 적용 여부를 확인한다.
UNIQUE와 PRIMARY KEY 혼동	식별자 설계가 흐려짐	대표 식별자는 PK, 보조 중복 방지는 UNIQUE로 둔다.
CASCADE 무심코 사용	부모 키 변경이 자식에 전파됨	업무 규칙상 전파가 맞는지 확인한다.
대소문자/컬럼명 불일치	알 수 없는 컬럼 오류	DESC 결과와 SQL의 컬럼명을 맞춘다.

10 설계 메모

제약조건은 나중에 애플리케이션 코드로만 막으려 하면 누락되기 쉽다. 데이터베이스 단계에서 막아야 하는 규칙은 테이블 설계에 포함하는 편이 안정적이다.

다만 제약조건을 너무 늦게 추가하면 기존 데이터 때문에 실패할 수 있다. 가능하면 테이블 생성 시점에 기본키, 외래키, NOT NULL, DEFAULT를 함께 결정한다.

11 오류 메시지 읽기

오류 유형	가능한 원인	먼저 볼 것
Duplicate entry	PK 또는 UNIQUE 중복	기존 값 조회
Cannot add foreign key	부모 키 없음, 타입 불일치	부모 테이블 구조
Check constraint is violated	CHECK 조건 위반	입력값 범위
Column cannot be null	NOT NULL 컬럼 누락	INSERT 컬럼 목록
Unknown column	컬럼명 오타	DESC 결과

12 실전 적용 순서

1

업무 규칙 적기무엇이 중복되면 안 되고 무엇이 필수인지 문장으로 정리한다.

2

테이블 관계 그리기부모와 자식 테이블, 참조 컬럼을 먼저 확정한다.

3

CREATE TABLE 작성가능한 제약조건은 생성 시점에 함께 넣는다.

4

샘플 INSERT성공해야 하는 값과 실패해야 하는 값을 모두 넣어 본다.

5

구조 검산SHOW CREATE TABLE 결과로 최종 구조를 확인한다.

13 한 줄 복습

상황	바로 떠올릴 문법	핵심
기본키 추가	ADD CONSTRAINT ... PRIMARY KEY	중복 불가 식별자
외래키 추가	FOREIGN KEY ... REFERENCES	부모 데이터 참조
값 범위 제한	CHECK (...)	허용 범위 강제
기본값 지정	DEFAULT 값	값 생략 시 자동 입력
중복 방지	UNIQUE	보조키 성격
압축 저장	ROW_FORMAT=COMPRESSED	저장 공간 절감 확인

14 실습 데이터 점검

점검 대상	확인 쿼리	판단 기준
회원 기본키 중복	SELECT userID, COUNT(*) FROM usertbl GROUP BY userID;	COUNT가 1보다 크면 PK 추가 전 정리 필요
구매의 고아 데이터	LEFT JOIN 후 usertbl.userID IS NULL 검색	부모 없는 구매 행은 FK 추가 실패 원인
출생연도 범위	WHERE birthYear NOT BETWEEN 1900 AND 2023	CHECK 조건 위반 데이터 확인
이메일 중복	GROUP BY email HAVING COUNT(*) > 1	UNIQUE 추가 전 중복 제거
기본값 적용	INSERT 후 SELECT로 생략 컬럼 조회	DEFAULT가 기대값으로 들어갔는지 확인

15 변경 전후 비교

변경 전	변경 후	효과
키 없는 usertbl	PRIMARY KEY 추가	회원 식별이 안정적이다.
관계 없는 buytbl	FOREIGN KEY 추가	없는 회원의 구매 입력을 막는다.
범위 없는 birthYear	CHECK 추가	비정상 연도 입력을 막는다.
기본값 없는 addr	DEFAULT 추가	생략 입력의 의미가 명확해진다.
중복 가능한 email	UNIQUE 추가	같은 이메일 중복 가입을 막는다.

16 마무리 기준

테이블 제약조건 정리는 구조 변경만으로 끝나지 않는다. 성공해야 하는 INSERT와 실패해야 하는 INSERT를 모두 실행해 봐야 규칙이 실제로 동작하는지 확인할 수 있다.

ALTER TABLE 실습은 실행 전 데이터 점검, 실행 후 구조 점검, 샘플 입력 테스트까지 한 묶음으로 보는 것이 좋다.

핵심 요약

주제TABLE DEFAULT 값

핵심CREATE TABLE 단계에서 DEFAULT를 포함해 기본값을 설계한다.

검산DESC, SHOW INDEX, SHOW CREATE TABLE로 구조를 확인한다.

순서부모 테이블 생성 후 자식 테이블, 삭제는 반대로 진행한다.

주의기존 데이터가 제약조건을 위반하면 ALTER TABLE은 실패한다.

Tags

#MySQL #ALTER TABLE #CONSTRAINT #PRIMARY KEY #FOREIGN KEY #CHECK #DEFAULT #UNIQUE

티스토리 태그 입력용

MySQL, ALTER TABLE, CONSTRAINT, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, UNIQUE, SQL, 데이터베이스