SQL | 테이블 생성, 제약조건, 뷰

2023-02-05 Comments

SQL에서 사용되는 테이블 생성, 제약조건, 뷰에 대해 정리하는 글입니다.

데이터베이스와 SQL
SQL 미리 맛보기
SQL 기본 문법
SQL 고급 문법
테이블과 뷰 ⬅ 테이블 생성, 제약조건, 뷰
인덱스
스토어드 프로시져
SQL과 파이썬 연결

테이블 생성

테이블 생성하는 과정을 실습해보겠습니다. 우선 구조를 구상하는 것이 먼저이고, data type을 적합하게 지정해줘야합니다.

네이버 쇼핑몰 데이터베이스를 만든다고 해봅시다. 회원 테이블이 있을 것이고, 제품에 대한 구매 테이블이 있어야합니다.

지난 글과 유사하네요. 구상을 했다고 하고, 만들어볼 테이블의 구조는 다음과 같습니다.

열 이름	영문 이름	데이터 형식	널 허용 여부	기타
아이디	mem_id	CHAR(8)	Yes	Primary Key
회원이름	mem_name	VARCHAR(10)	Yes
인원 수	men_number	TINYINT	Yes
주소	addr	CHAR(2)	Yes
연락처 국번	phone1	CHAR(3)	No
전화번호	phone2	CHAR(8)	No
평균 키	height	TNIYINT	No	UNSIGNED
데뷔 일자	debut_date	DATE	No

회원 테이블 (member)

열 이름	영문 이름	데이터 형식	널 허용 여부	기타
순번	num	INT	Yes	PK, 자동 증가
아이디	mem_id	CHAR(8)	Yes	Foreign Key
제품 이름	prod_name	CHAR(6)	Yes
분류	group_name	CHAR(4)	No
가격	price	INT	Yes	UNSIGNED
수량	amount	SMALLINT	Yes	UNSIGNED

구매 테이블 (buy)

데이터베이스 생성하기

1	CREATE DATABASE naver_db;

테이블 생성하기

구상한 테이블을 생성해봅시다. 이전에 배운 CREATE TABLE 예약어를 사용합니다.

회원 테이블 (member)

USE naver_db;
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
     );

구매 테이블 (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)
     );

FOREIGN KEY 예약어로 둘을 지정한다면 지정한 다른 테이블에도 값이 존재해야합니다.

AUTO_INCREMENT가 지정되면 1부터 자동으로 1씩 증가합니다.
⭐️ AUTO_INCREMENT가 지정된 열은 PK나 UNIQUE로 지정되어야 합니다!

데이터 입력

회원 테이블에 3건을 입력해보겠습니다. 이전에 배운 INSERT 예약어를 사용합니다.

1
2
3

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2015-8-8');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-1-15');

결과

이번에는 구매 테이블에 3건을 입력해보겠습니다.

1
2
3

INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);

💣여기서 오류가 발생합니다

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (naver_db.buy, CONSTRAINT buy_ibfk_1 FOREIGN KEY (mem_id) REFERENCES member (mem_id))

왜 일까요? 구매 테이블을 생성할 때, FOREIGN KEY를 지정하였습니다. 구매 테이블에 입력한 'APN’은 member 테이블에 없는 mem_id라서 에러가 발생합니다.

테이블 생성은 여기까지입니다.

이번에는 제약조건에 대해 확인해보겠습니다.

제약조건^Constraint

제약조건이란 테이블 구조에 따른 조건입니다. 제약조건을 설정하면 다음의 장점이 있습니다.

데이터의 유효성
데이터의 일관성
데이터의 안정성

대표적인 제약조건에 대해 알아보겠습니다.

PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
DEFAULT 정의
NULL 허용여부

PRIMARY KEY

중복될 수 없습니다.
NULL이 허용되지 않습니다.
테이블 당 1개만 가질 수 있습니다.
PK - FK로 연결되어 있다면, FK가 존재하는 테이블을 삭제해야만 PK 가 존재하는 테이블을 삭제할 수 있습니다.
CREATE TABLE 마지막 줄에 PRIMARY KEY(열 이름) 으로 지정할 수도 있습니다.

CREATE TABLE member
    (mem_id     CHAR(8) NOT NULL,
     ...
     PRIMARY KEY (mem_id)
    );

ALTER TABLE 구문으로도 추가할 수 있습니다. 이미 존재하는 테이블에 추가로 제약조건을 추가할 수 있습니다.

CREATE TABLE member
    (mem_id     CHAR(8) NOT NULL,
     ...
     PRIMARY KEY (mem_id)
    );
ALTER TABLE member
    ADD CONSTRAINT
    PRIMARY KEY(mem_id);

FOREIGN KEY

두 테이블 사이의 관계를 연결하고 데이터의 무결성을 보장합니다.
다른 테이블의 PK나 UNIQUE와 연결되어야 합니다.
PK가 있는 테이블을 기준 테이블이라 부르고, FK가 있는 테이블은 참조 테이블 이라 합니다.
기준 테이블의 PK의 데이터는 일괄적으로 변경될 수 없습니다. 참조 테이블에 데이터가 남게 되기 때문이죠.

위의 구매 테이블 (buy)를 생성할 때 실습하였으므로 생성 예시는 넘어가겠습니다.

그러면 두 테이블의 데이터를 바꿔줄 수 없는 걸까요?

기준 테이블과 참조 테이블에 있는 데이터를 동시에 바꿔주는 예약어는 ON UPDATE CASCADE와 ON DELETE CASCADE 입니다. 테이블을 생성할 때, 제약조건과 함께 사용할 수 있습니다.

CREATE TABLE buy
    (...);
ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

이렇게 지정해주면 기준 테이블에서 데이터가 변동되어도 참조 테이블도 같이 바뀝니다.

기타 제약조건

UNIQUE

이름처럼 중복되지 않는 유이한 값을 입력해야 하는 조건입니다.

CHECK

입력한 데이터를 점검하는 기능입니다.
어떤 열에 값이 음수가 되지 않게 하거나, 어떤 열은 Categorical로 정해진 값만 입력하게 할 수 있습니다.

예시 1)

CREATE TABLE member
    (mem_id     CHAR(8) NOT NULL PRIMARY KEY,
     height     TINYINT UNSIGNED NULL CHECK (height >= 100),
     debut_date DATE NULL
     );

예시 2)

1
2
3

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

Default 정의

default를 지정했다면 값을 입력할 때, default를 입력해서 자동으로 그 값이 채워지게 할 수 있습니다.

예시는 다음과 같습니다

테이블 생성

CREATE TABLE member
    (mem_id     CHAR(8) NOT NULL PRIMARY KEY,
     height     TINYINT UNSIGNED NULL DEFAULT 160
     );

입력

1	INSERT INTO member VALUES('WMN', default)

NULL 허용 여부

이 부분은 계속 실습에 사용하였으므로 넘어가겠습니다.

뷰^View

데이터베이스의 주요 개체 중 하나입니다.
SELECT 문으로 사용자에게 일부의 테이블만 보여주는 기능을 합니다.
단순 뷰 와 복합 뷰로 나뉘어 집니다.

기본 구조는 다음과 같습니다.

1
2
3

CREATE VIEW 뷰_이름
AS
    SELECT 문;

이게 전부입니다. SELECT에는 보여주고 싶은 테이블을 넣어주면, 뷰_이름으로 테이블처럼 조회할 수 있습니다

뷰를 사용하면 다음의 장점이 있습니다

보안에 도움이 됩니다. ⭐️
복잡한 SQL을 단순하게 만들 수 있습니다.
예를 들어, 여러 JOIN을 한 테이블의 일정 열과 형태를 변경해 새로 생성한 열을 뷰로 지정하면 유용하게 확인할 수 있을 것입니다.

참고

뷰를 조회할 열에 공백이 있으면 백틱(```)을 사용해 구분해야 합니다.
뷰의 수정은 ALTER VIEW를 사용합니다. (기존에 있는 뷰를 새로 만든다고 생각하면 됩니다)
뷰의 삭제는 DROP VIEW를 사용합니다.
뷰를 생성할 때, CREATE OR REPLACE VIEW를 사용하면 기존에 뷰가 있어도 덮어 쓸수 있습니다.
CREATE VIEW만 사용하면 에러가 나옵니다.
SHOW CREATE VIEW으로 뷰의 소스코드를 확인할 수 있습니다.
뷰가 일부의 열만 보여주고 다른 열에 제약조건이 있다면 입력이 불가능할 수 있습니다.
뷰를 생성할때 설정된 값의 범위에 대해 WITH CHECK OPTION을 끝에 추가해주면, 범위에 해당하지 않는 값을 입력되지 않도록 할 수 있습니다.

뷰의 생성

CREATE VIEW v_height167
AS
    SELECT * FROM member WHERE height >= 167
             WITH CHECK OPTION;

CHECK의 범위를 벗어나는 데이터 입력

1	INSERT INTO v_height167 VALUES('TOB', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01')

💣 결과는 에러가 나타납니다

여기서 중간 점검을 해보겠습니다.

기존에 뷰가 있으면 덮어쓰고, 없으면 새로 생성하는 SQL문을 작성하려 합니다.

1
2
3

[                ] 뷰_이름
AS
    SELECT 문;

1️⃣ CREATE AND REPLACE VIEW
2️⃣ CREATE OR REPLACE VIEW
3️⃣ CREATE AND OVERWRITE VIEW
4️⃣ CREATE OR OVERWRITE VIEW

…

정답은 2️⃣ CREATE OR REPLACE VIEW 입니다. OVERWRITE라는 예약어는 없습니다.

오늘은 SQL의 테이블 생성, 제약조건과 데이터베이스 핵심 개체중 하나인 뷰에 대해 정리하였습니다.

다음에는 인덱스에 대해 정리해보겠습니다.

읽어주셔서 감사합니다~👋

SQL | 테이블 생성, 제약조건, 뷰

테이블 생성

데이터베이스 생성하기

테이블 생성하기

데이터 입력

제약조건Constraint

PRIMARY KEY

FOREIGN KEY

기타 제약조건

뷰View

제약조건^Constraint

뷰^View