DB 초보자의 PostgreSQL 실습 노트 04: 메뉴 테이블부터 주문 테이블, 그리고 FK의 등장?

지난 글에서 우리는 고객 테이블을 직접 만들었다.

테이블을 만들고, 데이터를 넣고, 조회해서 확인까지 했다.
[ PostgreSQL 초보자 실습 03: 테이블 생성과 INSERT 데이터 입력 ]

이번에는 메뉴 테이블부터 다시 시작한다.

고객 테이블과 비슷해서 복습하기 좋다.

이번 글에서는 외우기보다

왜 테이블을 연결해야 하는지부터 보자.

이 글은 PostgreSQL 메뉴 테이블(cafe.menus)을 예시로 CREATE TABLE 작성, 데이터 타입과 제약 조건 정리, INSERT 입력, SELECT 확인까지 순서대로 따라가 보는 실습 노트다.

목차

1. 지난 글에서 한 일 다시 보기
2. 메뉴 테이블은 어떤 정보를 저장할까?
3. 메뉴 테이블 컬럼 정리하기
4. 메뉴 테이블 CREATE TABLE 작성하기
5. 사용할 SQL 키워드 확인해보자
6. 메뉴 테이블에 사용할 데이터 타입 정리
7. 제약 조건 확인하기
8. 메뉴 테이블 SQL 실제로 작성하기
9. SQL 실행 전 필수 확인
10. 메뉴 테이블 SQL 실행하고 확인하기
11. 메뉴 데이터 INSERT 하기
12. 준비 중인 메뉴는 어떻게 넣을까?

시작하기전! 알아두면 좋은 TIP.

이쯤되면 궁금할 수 있을 것 같은 부분!

SQL 문법은 줄바꿈 들여쓰기 같은것을 꼭해야하는 건가요?

SQL은 Python처럼 들여쓰기로 구조를 판단하지 않는다.
아직 파이썬을 모르는 사람은 아래 내용정도만 알고 가자!

줄바꿈과 들여쓰기는 대부분 사람이 보기 좋게 하기 위한 것이다.

SQL에서 더 중요한 것은
괄호, 쉼표, 따옴표, 세미콜론의 위치다.

1. 지난 글에서 한 일 다시 보기

지난 글에서는 고객 테이블을 직접 만들어보았다.

먼저 고객 테이블에 어떤 컬럼이 필요할지 생각했고,
CREATE TABLE로 실제 테이블을 만들었다.

그다음 INSERT INTO로 샘플 고객 데이터를 입력하고,
SELECT로 제대로 들어갔는지 확인했다.

흐름은 이랬다.

고객 테이블 구조 고민
→ CREATE TABLE로 테이블 생성
→ INSERT로 데이터 입력
→ SELECT로 결과 확인

이번 글에서는 이 흐름을 그대로 한 번 더 사용한다.

먼저 메뉴 테이블을 만들고,
그다음 주문 테이블에서 새로운 개념인 FK를 만나본다.

이전 글:

PostgreSQL 초보자 실습 03: 테이블 생성과 INSERT 데이터 입력

2. 메뉴 테이블은 어떤 정보를 저장할까?

고객 테이블은
손님 정보를 저장하는 표였다.

이번에는 메뉴 테이블을 만든다.

메뉴 테이블은
카페에서 판매하는 메뉴 정보를 저장하는 표다.

이번 항목에서 할 일은 간단하다.

메뉴 정보 생각하기
→ 필요한 항목 고르기
→ 중요한 기준 컬럼 찾기
→ SQL 컬럼으로 정리하기

먼저
어떤 정보를 저장할지,
그중 무엇이 기준이 될지,
그걸 어떤 컬럼으로 바꿀지
생각하는 것이 중요하다.

3. 메뉴 테이블 컬럼 정리하기

앞에서 메뉴 테이블은
카페에서 판매하는 메뉴 정보를 저장하는 표라고 했다.

이제 그것을 컬럼 ( columns ) 로 바꿔보자.

메뉴 하나를 저장하려면 대략 이런 정보가 필요하다.

만약 생각한 것이 이것보다 많다면, 또는 다른 항목으로 해도 괜찮다.
하지만 그전에 AI에게 검증을 받아 보도록하자.

아직은 AI가 우리보다 훨씬 잘 알기 때문이지요?

예를 들어서 메뉴 테이블에 들어가야 할 항목을

• 메뉴명
• 가격
• 분류
• 설명(메뉴에 대한)
• 매장/테이크 아웃
• 원두종류
• HOT/ICE 선택

이라고 생각했다고 하자.

AI에게 물어보고 의견을 듣고 보면 왜 필수 항목인지 왜 필요없는지
설명을 보고 납득이 된다면 넘어가자.

권장 영문 컬럼명의 폰트색을 흰색으로 정해놨다.
궁금하면 드래그 해서 확인해보자!

4. 메뉴 테이블 CREATE TABLE 작성하기

앞에서 메뉴 테이블에 필요한 컬럼을 정리했다.

이제 그 내용을 바탕으로
실제 SQL을 작성해보자.

이번에도 흐름은 고객 테이블과 같다.

우선 컬럼을 확인하자!

사용할 SQL 키워드 확인해보자.

SQL 작성 첫 등장은 키워드들이다.
아래의 예시처럼 ” 만든다 테이블 ” 영어문법 처럼 해석 된다?

메뉴 테이블에 사용할 데이터 타입 정리

데이터 타입은 이 컬럼에는 어떤 종류의 값을 저장할 것인가를 정하는 것

VARCHAR(100) <<< 괄호 안의 숫자는 직접 정하는 것인데
기본적으로 이름과 카테고리는 100, 50으로 진행해는 것이다.

테이블을 만들고
(테이블 안에) 컬럼은 이런 타입으로~

제약 조건 확인하기

테이블에 사용할 조건, 정확히는 제약 조건(Constraint) 을 확인
조건은 “데이터를 아무렇게나 넣지 않도록 막아주는 규칙”이다.

테이블을 만들고
(테이블 안에) 컬럼1은 이런 타입이고 이런 조건으로
컬럼2는 이런 타입이고 이런 조건으로
컬럼3은 이런 타입이고 이런 조건으로
.
.

4-1. 메뉴 테이블 SQL 실제로 작성하기

메뉴 테이블에 필요한 컬럼, 데이터 타입, 조건까지 확인했다.

이제 이 내용을 하나로 합쳐서
실제 SQL 문장으로 만들어보자.

SQL은 어렵게 보면 길어 보이지만,
사실 앞에서 정리한 조각들을 순서대로 끼워 넣는 것이다.

우리의 말(자연어)을 활용하여 프로그래밍 언어의 코드 형식과 유사하게 정리해 놓은 것을

의사 코드 또는 유사 코드(Pseudocode)

라고 하는데 먼저 유사코드를 먼저 생각해보는 습관을 들여보자.

유사코드를 잘 작성하게 된다면, 코딩은 AI에게~ 요즘은 그런시대니까!

해보자!

cafe 스키마 안에 메뉴테이블을 만들고 그 안에
메뉴 하나를 구분하는 아이디 컬럼을 만든다 숫자 타입의 기본값으로(중요)
메뉴명을 글자 타입으로 만들고 100글자 이내로 조건을 둔다. 없으면 안된다.
가격은 숫자 타입으로 하고 없으면 안된다.
카테고리는 글자타입으로 50글자 이내로 한다.
판매중인지 아닌지는 판매중인지 아닌지를 구분하고 기본으로는 판매중으로 한다.

처음에는 이렇게 하는 것도 어려울 수 있지만 시도해보고 또 시도 해보자!
이렇게 작성을 했다면 하나씩 퍼즐을 끼워 맞추듯 실제 SQL 요소들을 넣어보고
AI에게 확인 받으며 공부를 하면 좋을 것 같다.

그렇게 완성이 된 SQL

CREATE TABLE cafe.menus (
    menu_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price INTEGER NOT NULL,
    category VARCHAR(50),
    is_available BOOLEAN DEFAULT TRUE
);

DBeaver에 입력하고 실행해보자!

잠깐!

4-2. SQL 실행 전 필수 확인

지금까지 이어서 작업을 하고 있다면 상관 없지만,
종료 했다가 작업을 하는 경우에는 우리가 진행하던
데이터 베이스 ( cafe_practice ) 가 맞는지
스키마 ( cafe ) 가 맞는지 기본값으로 되어 있는지
확인하고 SQL을 입력하고 실행해야 한다.

미세하지만 경로의 데이터 베이스와 스키마가 진한 회색인지! 확인
기본이 아닐 때 : 오른쪽 마우스를 클릭하고 Set as default 클릭

종료하기 전까지 cafe_practice 를 하다가 종료 했다면 최근 SQL편집기를 누르면
이전에 했던 스크립트가 보일 것이다.

그게 아니라면 SQL편집기를 눌러서 찾거나
새 SQL편집기를 열어도 상관 없다.

작업을 시작하기전 제대로 cafe_practice
데이터 베이스와 연결 되어 있는지 확인하기 위해

SELECT current_database();

입력 후 실행

만약 다른 데이터 베이스가 표시 된다면 위에서
데이터 베이스와 스키마가 기본 값으로 되어 있는지 한 번 확인해보자.

4-3. 메뉴 테이블 SQL 실행하고 확인하기

CREATE TABLE cafe.menus (
    menu_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price INTEGER NOT NULL,
    category VARCHAR(50),
    is_available BOOLEAN DEFAULT TRUE
);

입력 후 SQL을 실행해 보자.

에러가 나지 않고 이렇게 출력됐다면 성공이다.

cafe 스키마 안에
menus 테이블이라는 빈 표를 만들었다.

혹시나 에러가 발생했다면 이것을 확인해보자.

문제가 없다면 정말 menus 테이블이 만들어졌는지 확인해보자.

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'cafe'
  AND table_name = 'menus';

공부 TIP. 이런 SQL을 의사코드로 바꿔보기!
더듬 더듬 이전의 내용들을 찾아보며 들을 맞춰가면 바꿔보고
이 SQL은 뭘 말하기에 아래와 같은 결과가 나왔는지 해석해보자

5. 메뉴 데이터 INSERT 하기

지금까지는 menus라는 빈 메뉴판을 만든 상태다.

아직 안에는 실제 메뉴 데이터가 없다.

이제 INSERT INTO를 사용해서
카페 메뉴 데이터를 넣어보자.

이번에 넣을 데이터는 예시로 이렇게 잡는다.

다른 메뉴 넣고 싶어요? 넣어도 된다!

여기서 menu_id는 직접 입력하지 않는다.

왜냐하면 menu_id는 자동으로 만들어지도록 설정했기 때문이다.

그리고 is_available도 직접 입력하지 않는다.

왜냐하면 값을 넣지 않으면
기본값으로 TRUE가 들어가도록 설정했기 때문이다.

어?그럼 여기서 이런 질문이 생길 수도 있다.
나는 생겼기 때문에..ㅋㅋ

그럼 판매 중이 아닌 메뉴는 어떻게 하나? 예를 들어서
준비중인 메뉴지만 노출은 하고 싶을 때 말이다.

INSERT INTO cafe.menus (name, price, category, is_available)
VALUES ('딸기라떼', 5000, '음료', FALSE);

status VARCHAR(20) DEFAULT 'available'

이번에 입력할 컬럼은 위의 예시에서 보이는 것처럼
메뉴이름, 가격, 분류 이렇게 세 가지다.

의사 코드, 유사 코드로 먼저 생각해보자.

카페 스키마 안에 있는 메뉴 테이블에 메뉴이름, 가격, 분류 컬럼을 넣고
메뉴는
아메리카노는 3000원이고 커피
카페라떼는 4000원이고 커피
바닐라라떼는 4500원이고 커피
치즈케이크는 5500원 디저트
초코머핀은 3500원 디저트다

를 SQL로 바꾸면!

INSERT INTO cafe.menus (name, price, category)
VALUES
    ('아메리카노', 3000, '커피'),
    ('카페라떼', 4000, '커피'),
    ('바닐라라떼', 4500, '커피'),
    ('치즈케이크', 5500, '디저트'),
    ('초코머핀', 3500, '디저트');

입력을 하려고 보니

분명 테이블이 생성 된것을 확인했는데..?
그리고 있는데!??

DBeaver 편집기가 아직 cafe.menus를 제대로 인식 못 하고 있는 상태
라고 보면 된다고 한다. 실행을 해보고 결과를 보자.

정상적으로 실행이 됐다.

빨간 물결선이 아주 거슬리기는 하지만 문제가 없는 것.

입력된 데이터를 조회해서 확인하자.

SELECT *
FROM cafe.menus
ORDER BY menu_id;

결과를 해석하자면

조회 결과를 보면 메뉴 데이터가 정상적으로 들어간 것을 확인할 수 있다.

menu_id는 직접 입력하지 않았지만 1번부터 자동으로 생성되었다.

is_available도 직접 입력하지 않았지만,
DEFAULT TRUE 조건 때문에 모두 판매 중(TRUE) 상태로 들어갔다.

즉, 메뉴 테이블 생성과 메뉴 데이터 입력까지 정상적으로 완료되었다.

준비 중인 메뉴는 어떻게 넣을까?

방금 입력한 메뉴들은 is_available을 생략했다.

그래서 기본값인 TRUE,
즉 판매 중으로 들어갔다.

그런데 준비 중인 메뉴처럼
아직 판매하지 않는 메뉴를 넣고 싶다면
is_available 값을 직접 넣으면 된다.

INSERT INTO cafe.menus (name, price, category, is_available)
VALUES ('딸기라떼', 5000, '음료', FALSE);

의사 코드

딸기라떼 메뉴를 추가한다.
가격은 5000원이고,
분류는 음료다.
하지만 아직 판매 중은 아니므로 FALSE로 넣는다.

Rows 1로 한줄 추가 됐고, Query에 딸기라떼가 FALSE 로 보인다.

잘 입력 됐는지 확인해보자.

SELECT *
FROM cafe.menus
ORDER BY menu_id;

체크 표시가 비어 있는 딸기라떼가 추가 된 것을 볼 수 있다.

[ PostgreSQL 초보자 실습 03: 테이블 생성과 INSERT 데이터 입력 ]

에 이어서 메뉴 테이블을 생성하고 데이터 입력까지 복습하는 느낌으로 진행 되었다.

다음에는 FK(외래키)에 대해 알아보고 주문 테이블까지 이어서 해보도록 한다.