[PostgreSQL] Database와 Table 생성하기 / data type, key

Data Types

우리는 데이터 유형을 이미 접했는데 이번 시간엔 그러한 데이터 유형을 검토해볼 것이다.
테이블을 생성할 때 각 열이 보유한 데이터 타입을 선택해야하기 때문이다.

데이터 타입에는 다음과 같은 종류가 있다.

• Boolean
• Character
• Numeric
• Temporal
• UUID
• Array
• JSON
• Hstore key-value pair
• Special types such as network address and geometry data

postgresql.org/docs/current/datatype.html 에 들어가서 이러한 데이터타입의 조건을 살펴볼 수 있다.

데이터 타입을 지정할 떄는 어떠한 타입을 지정해야할 지 고민하는 과정이 필요하다. 예를들어 전화번호 데이터를 저장할 때 numeric 데이터를 쓸지 텍스트 데이터로 쓸지 고민해야한다.
일반적으로 전화번호로 숫자 연산을 하지는 않을 테니 텍스트로 표현하게 된다.

잘 모르겠으면 구글에 서치하면 대부분의 사람들이 어떤 데이터 유형으로 저장하는지 살펴볼 수 있다.

따라서 데이터베이스와 테이블을 만들 때는 시간을 들여 어떻게 저장할 것인지 계획을 세워야한다.

Primary Key & Foreign Key

기본키(Primary Key) 는 테이블에서 행을 고유하게 식별하는데 사용되는 칼럼이나 칼럼 그룹이다.
예를 들어 dvd 렌탈 데이터베이스에서는 고객 고유 customer_id 가 기본키에 해당한다.
기본키는 integer 기반이고 유일해야 한다.

외래키(Foreign key) 는 다른 테이블의 행을 고유하게 식별하는 테이블의 필드 또는 필드 그룹이다. 외래키는 다른 테이블의 기본키를 참조하는 테이블에 정의된다.
외래키를 포함하는 테이블을 참조 테이블 혹은 자식테이블이라고 하고 외래키가 참조하는 테이블을 참조테이블 혹은 상위 테이블이라고 한다.

하나의 테이블은 다른 테이블과의 관계에 따라 여러개의 외래키를 가질 수 있다.

예를 들어 dvd 렌탈 데이터베이스 payment 테이블에서 각 payment 행에는 payment 테이블의 기본키인 payment_id 가 있다.그리고 customer_id 도 존재했다. 이때 customer_id 가 외래키가 된다.

Constraints

Constraints 는 테이블에서 데이터 칼럼에 적용되는 규칙이다.
데이터베이스로 들어오는 유효하지 않은 데이터를 막는데 사용된다.
이것들은 데이터베이스에서 데이터의 정확성과 신뢰성을 보장한다.

Constraints 는 두가지 주요 카테고리로 분류될 수 있다.

• 칼럼 Constraints
  - 특정 조건을 준수하도록 열의 데이터를 제한함.
• 테이블 Constraints
  - 개별 열이 아닌 전체 테이블에 적용됨.
  

Column 에서 가장 많이 쓰이는 constraints 는
NOT NULL 과 UNIQUE Constraint 이다.

• NOT NULL Constraint
  - 칼럼이 NULL 을 가질수 없음을 보장함.
  
• UNIQUE Contraint
  - 칼럼의 모든 값이 달라야함.
• PRIMARY Key
  - 데이터베이스 테이블에서 각 행/레코드를 유니크하게 구별한다.
• FOREIGN Key
  - 다른 테이블의 열을 기반으로 데이터를 제한함.
• CHECK Constraint
  - 칼럼의 모든 값이 특정 조건을 만족해야함.
• EXCLUSION Constraint
  - 지정된 연산자를 사용하여 지정된 열 또는 표현식에서 두 행을 비교할 경우 모든 비교가 TRUE를 반환하지는 않는지 확인함.

테이블에서 Constraints 를 보자

• CHECK (condition)
  - 데이터를 인서트하거나 업데이트할 때 조건을 확인하는 것.
• REFERENCES
  - 칼럼에 저장된 값이 다른 테이블의 한 칼럼에 무조건 존재해야하도록 함.
  
• UNIQUE (column_list)
  - 칼럼 속 값이 괄호안에 유니크하게 리스트 되어 있어야함을 강제함.
• PRIMARY KEY(column_list)
  - 유저가 여러개의 칼럼으로 구성된 기본키를 정의할 수 있게함.

Create Table

우선 전체 구문은 이렇다.

CREATE TABLE table_name (
	column_name TYPE column_constraint,
    column_name TYPE column_constraint,
    table_constraint table_constraint
    )
    INHERITS existing_table_name;

공통 간단 구문은 이렇다.

CREATE TABLE table_name (
	column_name TYPE column_constraint,
    column_name TYPE column_constraint,
    );

테이블마다 기본키가 있어야하므로 예를 들어 이렇게 쓰면 된다. players 라는 테이블을 만드는데 player_id 라는 SERIAL 타입의 칼럼을 만든다. 이 SERIAL 타입은 PostgreSQL 에서 integer 의 시퀀스를 생성해내는 데이터베이스 오브젝트의 특별한 한 종류이다. 주의할 점은 우선 테이블을 만들고 나서 하나의 행을 삭제한다해도 다시 시리얼 넘버가 조정되진 않는다. 처음에 1,2,3,4,5,6,7 의 시리얼 넘버를 부여받았다할 때 만약 나중에 4를 삭제하면 1,2,3,5,6,7 만 남게 된다.

시퀀스는 테이블에서 자주 primary 키 칼럼으로 사용된다.

CREATE TABLE players (
	player_id SERIAL PRIMARY KEY,
    age SMALLINT NOT NULL
    );

pgadmin 에서 데이터베이스를 만들고 쿼리툴을 열어서 다음처럼 테이블 account 를 만든다.
유저 id 의 타입은 SERIAL 로 하고 NULL 이면 안된다. username 과 email 은 유니크 해야하고 NULL 이면 안된다. 생성 날짜와 마지막 로그인 시점은 TIMESTAMP 타입으로 한다.