테이블 생성과 제약 조건

prefer·2025년 1월 1일
Databasesql

SQL 기초

목록 보기
8/15

데이터베이스 개체의 생성/수정/삭제

데이터베이스를 생성, 수정, 삭제하기 위해서는 대표적으로 CREATE, ALTER, DROP을 사용하여서 데이터베이스를 조작할 수 있다.

CREATE 개체 종류

데이터베이스 개체 생성 쿼리문이다.

ALTER 개체 종류

데이터베이스 개체 수정 쿼리문이다.

DROP 개체 종류

데이터베이스 개체 삭제 쿼리문이다.

테이블 생성

테이블은 행(row, record)과 열(column, field)로 구성되어 있다.

CREATE DATABASE naver_db;
CREATE TABLE 테이블 이름(열 이름 데이터 형식);

CREATE TABLE은 테이블 생성 명령어로 테이블 이름(열 이름 데이터 형식1, 열 이름 데이터 형식2, …)와 같이 테이블 이름과 테이블 내부에 들어갈 열들의 이름과 데이터 형식을 지정해줘야 한다.

간단하게 테이블을 정의해보도록 하자.

USE naver_db;
DROP TABLE IF EXISTS member;
CREATE TABLE member
(
	mem_id CHAR(8),
	mem_name VARCHAR(10),
	mem_number TINYINT,
	addr CHAR(2),
	phone1 CHAR(3),
	phone2 CHAR(8),
	height TINYINT UNSIGNED,
	debut_date DATE
);

이 예시는 열의 이름과 데이터 형식만 지정한 예시이다. 위와 같이 순서는 열의 이름 → 데이터 형식의 순서로 지정한다.

이번에는 각각의 열에 NULL값과 관련된 옵션을 추가해보도록 하자.

USE naver_db;
DROP TABLE IF EXISTS member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL,
	mem_name VARCHAR(10) NOT NULL,
	mem_number TINYINT NOT NULL,
	addr CHAR(2) NOT NULL,
	phone1 CHAR(3) NULL,
	phone2 CHAR(8) NULL,
	height TINYINT UNSIGNED NULL,
	debut_date DATE NULL
);

만약 NULL로 지정할 경우 해당 열의 값에 NULL을 허용하고, NOT NULL로 지정할 경우 해당 열의 값에 NULL 허용하지 않는다. 따라서 NULL값을 삽입할 경우 에러가 발생한다.

만약 NOT NULL이나 NULL 옵션을 아무것도 지정하지 않을 시 기본값으로 NULL 허용이 적용된다.

이번에는 테이블에 기본 키와 관련된 옵션을 추가해보도록 하자.

USE naver_db;
DROP TABLE IF EXISTS member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	mem_number TINYINT NOT NULL,
	addr CHAR(2) NOT NULL,
	phone1 CHAR(3) NULL,
	phone2 CHAR(8) NULL,
	height TINYINT UNSIGNED NULL,
	debut_date DATE NULL
);

PRIMARY KEY 옵션을 붙여서 mem_id를 기본 키로 설정했다.

이번에는 기본 키에 값을 삽입하지 않아도 자동으로 값이 증가하여 입력되는 옵션과 외래 키 옵션을 추가해보도록 하자.

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(
	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	mem_id CHAR(8) NOT NULL,
	prod_name CHAR(6) NOT NULL,
	group_name CHAR(4) NULL,
	price INT UNSIGNED NOT NULL,
	amount SMALLINT UNSIGNED NOT NULL,
	FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

AUTO_INCREMENT로 지정한 열은 PRIMARY KEY나 UNIQUE로 지정한다. UNIQUE 옵션을 해당 옵션이 지정된 열은 다른 행들과 구분될 수 있는 고유한 값을 가져야 한다. 즉 다른 행과 중복된 값을 가지면 안된다.

외래 키 지정은 FOREIGN KEY(외래 키가 되는 열 이름) REFERENCES <참조하는 테이블 이름>(외래키가 참조하는 열 이름)으로 지정할 수 있다.

제약 조건

제약 조건이란 테이블의 데이터 무결성을 유지하기 위해 설정하는 규칙이다. 이 규칙들로 테이블에 데이터를 삽입, 수정, 삭제할 데이터를 제한하거나 행위 자체를 제한할 수 있다.

데이터의 무결성이란 데이터의 결함이 없다는 것이고 데이터의 결함이란 예를 들어 회원의 아이디가 중복되는 것과 같이 데이터에 문제가 생기는 것이다.

MySQL에서 제공하는 대표적인 제약조건은 PRIMARY KEY 제약조건, FOREIGN KEY 제약조건, UNIQUE 제약조건, CHECK 제약조건, DEFAULT 정의, NULL 값 허용이 있다.

기본 키와 외래 키는 대표적인 제약조건으로 이메일, 휴대폰과 같이 중복되지 않는 열에는 고유 키(UNIQUE) 사용하여 제약 조건을 지정할 수 있다.

특정 값을 입력하는 것을 방지하는 제약조건으로 체크(CHECK)를 사용할 수 있고, 반복되는 특정값을 일반화하기 위해 기본값(DEFAULT)을 설정할 수 있다.

만약 값을 꼭 입력해야 하는 경우 NOT NULL 제약조건을 지정하여 NULL값이 입력되는 것을 방지해야 한다.

기본 키 제약 조건

기본 키란 테이블의 많은 행 데이터를 구분할 수 있는 식별자로 기본 키에 입력되는 값은 중복이 불가하고, NULL 값도 허용하지 않는다.

기본 키가 없어도 테이블 구성이 가능하나 기본 키를 설정해야 중복 데이터 입력이 방지될 수 있고, 기본 키로 지정된 열에 자동으로 클러스형 인덱스 생성된다. 이 클러스터형 인덱스는 데이터를 검색하는데 매우 유용하게 사용된다.

참고로 테이블에는 하나의 기본 키만 생성할 수 있다. 하나의 기본 키에는 여러 개의 열이 들어갈 수도 있고 그런 개념이 존재하나 여기서 다루기엔 좀 긴 개념이기 때문에 추후에 공부하도록 해보자.

<실행>

USE naver_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	height TINYINT UNSIGNED NULL
);
USE naver_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL,
	mem_name VARCHAR(10) NOT NULL,
	height TINYINT UNSIGNED NULL,
	PRIMARY KEY (mem_id)
);
DROP TABLE IF EXISTS member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL,
	mem_name VARCHAR(10) NOT NULL,
	height TINYINT UNSIGNED NULL,
	CONSTRAINT PRIMARY KEY PK_member_mem_id (mem_id)
);

DESCRIBE member;
DROP TABLE IF EXISTS member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL,
	mem_name VARCHAR(10) NOT NULL,
	height TINYINT UNSIGNED NULL
);

ALTER TABLE member
	ADD CONSTRAINT
	PRIMARY KEY (mem_id);

<결과>

위 3개의 쿼리문은 모두 동일 테이블을 생성하는 쿼리문이지만 기본 키 설정 방식을 모두 달리해본 쿼리문이다.

우선 기본키는 열을 정의할 때 PRIMARY KEY 옵션을 사용하여 기본 키 설정할 수 있고, 앞에서 PRIMARY KEY 옵션을 추가하지 않아도 마지막 행에 PRIMARY KEY (기본 키로 사용할 열 이름)으로 기본 키를 설정할 수 있다.

또는 기본 키 제약 조건을 추가하는 것이기 때문에 CONSTRAINT PRIMARY KEY <제약 조건 이름> (기본 키로 사용할 열 이름)으로 기본 키 설정을 추가할 수 있다.

만약 테이블을 생성할 때 기본 키를 지정하지 않았을 경우 ALTER TABLE을 사용하여 기본 키를 설정할 수 있는데, ALTER TABLE은 이미 만들어진 테이블을 수정하는 구문이다.

ADD CONSTRAINT PRIMARY KEY (기본 키로 사용할 열 이름)으로 이미 생성한 테이블에서 기본 키를 설정할 수 있다.

외래 키 제약 조건

외래 키 는 제약 조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해준다. 기본 키-외래 키 관계에서 기준 테이블이란 기본 키가 있는 테이블이고, 참조 테이블이란 외래 키가 있는 테이블을 말한다. 두 테이블을 삭제하려면 반드시 참조 테이블 삭제 → 기준 테이블 삭제 순서로 해야 하며 반대로 진행 시 외래 키 제약조건에 위배되어 테이블을 삭제할 수 없다.

참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나 고유 키로 설정해야 하고, 그렇지 않을 경우 외래 키 관계를 설정할 수 없다.

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	height TINYINT UNSIGNED NULL
);

CREATE TABLE buy
(
	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	mem_id CHAR(8) NOT NULL,
	prod_name CHAR(6) NOT NULL,
	FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

외래 키를 지정하는 구문은 FOREIGN KEY (외래키로 지정할 열 이름) REFERENCES 기준 테이블(기준 테이블의 참고하고자 하는 열 이름)로 외래 키를 지정할 수 있다.

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(
	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	mem_id CHAR(8) NOT NULL,
	prod_name CHAR(6) NOT NULL
);

ALTER TABLE buy
	ADD CONSTRAINT
	FOREIGN KEY(mem_id) REFERENCES member(mem_id);

테이블을 생성할 때 외래 키를 지정하지 않았다면 기본키를 지정할때와 같이 `ALTER TABLE (테이블명) ADD CONSTRAINT FOREIGN KEY (외래키로 지정할 열 이름) REFERENCES 기준 테이블(기준 테이블의 참고하고자 하는 열 이름)`을 이용하여 외래 키 제약 조건을 추가할 수 있다.

INSERT INTO member VALUES('BLK', '블랙핑크', 163);
INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');

SELECT M.mem_id, M.mem_name, B.prod_name
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;

UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';
DELETE FROM member WHERE mem_id = 'BLK'

기본 키-외래 키로 맺어진 후에는 기준 테이블에서 참조되는 열의 이름은 변경 및 삭제가 불가하다. 따라서 위 쿼리문들에서 UPDATE와 DELETE 쿼리문은 실행되지 않는다.

만약 참조하는 데이터가 없다면 변경할 수 있다.

그렇다면 데이터를 업데이트하거나 삭제하려면 어떻게 해야할까?

<실행>

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(
	num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
	mem_id CHAR(8) NOT NULL,
	prod_name CHAR(6) NOT NULL
);

ALTER TABLE buy
	ADD CONSTRAINT
	FOREIGN KEY(mem_id) REFERENCES member(mem_id)
	ON UPDATE CASCADE
	ON DELETE CASCADE;
    
INSERT INTO member VALUES('BLK', '블랙핑크', 163);
INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');

UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK';

SELECT M.mem_id, M.mem_name, B.prod_name
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;

<결과>

ON UPDATE CASCADE, ON DELETE CASCADE 문을 추가하여 외래 키가 참조하는 기준 테이블의 열의 이름을 변경할 수 있다.

ON UPDATE CASCADE는 기준 테이블의 열 이름을 변경하면 참조하는 참조 테이블의 열 이름도 모두 변경하는 옵션이고, ON DELETE CASCADE는 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 옵션이다. 즉 기준 테이블의 참조되는 열에 변경이 일어나면, 참조 테이블의 외래 키 또한 변경에 영향을 받게 된다.

고유 키 제약 조건

고유 키 제약 조건이란 중복되지 않는 유일한 값을 입력해야 하는 제약 조건으로 기본 키 제약조건과 거의 비슷하나 NULL값을 허용한다는 게 차이점이다. 또한 기본 키는 테이블에 하나만 설정 가능하나 고유 키는 여러 개를 설정할 수 있다.

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	height TINYINT UNSIGNED NULL,
	email CHAR(30) NULL UNIQUE
);

고유 키 설정을 위해서는 해당하는 열에 UNIQUE 옵션을 추가해주면 된다. 이때 UNIQUE는 NULL값도 허용하기 때문에 NOT NULL로 지정하면 기본 키와 동일하게 작동한다.

체크 제약 조건

체크 제약 조건이란 입력되는 데이터를 점검하는 제약 조건으로 CHECK(조건) 형식으로 제약 조건을 추가할 수 있다.

DROP TABLE IF EXISTS member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	height TINYINT UNSIGNED NULL CHECK (height >= 100),
	phone1 CHAR(3) NULL
)

위 예시는 height 열에 100 이상의 값만 입력되도록 제약 조건을 부여하는 것으로, 참/거짓을 판별할 수 있는 제약 조건을 추가하면 된다.

ALTER TABLE member
	ADD CONSTRAINT
	CHECK (phone1 IN ('02', '031', '032', '054', '055', '061'));

만약 테이블 생성 시 제약 조건을 추가하지 못했따면 ALTER TABLE (테이블명) ADD CONSTRAINT CHECK(조건)을 이용하여 CHECK 제약 조건을 추가할 수 있다.

기본 값 정의

기본 값이란 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정하는 것이다.

DROP TABLE IF EXISTS member;
CREATE TABLE member
(
	mem_id CHAR(8) NOT NULL PRIMARY KEY,
	mem_name VARCHAR(10) NOT NULL,
	height TINYINT UNSIGNED NULL DEFAULT 160,
	phone1 CHAR(3) NULL
);

기본 값은 DEFAULT (값)의 형식으로 특정 열에 지정하여 기본값 정의할 수 있다. 위의 예시에서 height에 값을 입력하지 않는다면 기본 값으로 160이 입력되도록 지정하였다.

ALTER TABLE member
ALTER COLUMN phone1 SET DEFAULT '02';

만약 테이블을 생성할 때 기본 값 옵션을 추가하지 않았다면 ALTER TABLE (테이블명) ALTER COLUMN (기본 값을 지정할 열의 이름) SET DEFAULT (기본값)을 이용하여 기본값을 정의할 수 있다.

INSERT INTO member VALUES('RED' '레드벨벳', 161, '054');
INSERT INTO member VALUES('SPC', '우주소녀', default, default);

만약 기본값이 설정된 열에 기본 값을 입력하고 싶다면 default라고 입력하면 된다.

NULL 값 허용

특정 열에 NULL 값을 허용하려면 NULL옵션을 사용하고, 허용하지 않으려면 NOT NULL을 사용하면 된다. 기본 키가 설정된 열에는 자동으로 NOT NULL로 인식된다.

출처

  • 혼자 공부하는 SQL(우재남 저, 한빛미디어)
profile
prefer
기술적 의사결정에 객관성을 가지는 Back-End 개발자 이선호입니다.
이전 포스트

SQL 고급 문법 (3)

다음 포스트

테이블과 뷰

0개의 댓글