[PostgreSQL] How to Control Table

데이터베이스 및 테이블 만들기

• DATA TYPES
• PRIMARY AND FOREIGN KEYS
• CONSTRAINTS
• CREATE
• INSERT
• UPDATE
• DELETE,AFTER,DROP

DATA TYPES

• Boolean
  • Ture or False
• Character
  • char, varchar, and test
• Numeric
  • integer and floating-point number
• Temporal
  • date, time, timestamp, and interval
• UUID
  • Universally Unique Identifies
• Array
  • Stores an array of strings, numbers, etc
• JSON
• Hstore key-value paio
• Special types such as newwork address and geometirc data
• 데이터 타입에 맞는 유형으로 저장 -> 전화번호 같은 경우 연산이 필요 없으니 문자로 저장하는 게 나음
• 모르겠으면 인터넷으로 찾아보기

PRIMARY AND FOREIGN KEYS

PRIMARY KEY

• A primary key is a column or a group of columns used to identify a row uniquely in a table
• For example, in our dvdrental database we saw customers had a unique, non-null customer_id column as their primary key
• Primary keys are also importatn since they allow us to easily discern what columns should be used for joining tables together
• 프라이머리 키는 어떤 테이블을 함께 JOIN 해야 할지 알려준다.
• 따라서 유니크 해야하고 null 이 없어야 한다.

FOREIGN KEY

• A foreign key is a field or group of fields in a table that uniquely identifies a row in another table
• A foreign key is defined in a table that references to the primary key of the other table
• 다른 테이블의 행을 고유하게 식별하는 테이블의 필드
• foreign key 는 다른 테이블의 primary key 에 대해 레퍼런스를 주도록 되어있다.
• The table that contains the foreign key is called referencing table or child table
• The table to which the foreign key references is called referenced table or parent table
• A table can have multiple foregin keys depending on its relationships with other tables
• foreing key 를 포함하고 있는 테이블은 레퍼린스 테이블이나 차일드 테이블이라고 부른다.
• foreing key 가 레퍼런스 하는 테이블은 레퍼런스 테이블이나 페런트 테이블이라고 부른다.
• 다른 테이블과 얼마나 많은 관계를 맺고 있는지에 따라 다양한 foreing key를 가지고 있을지가 결정된다.

CONSTRAINTS

• Constraints are the ruls enforeced on data columns on table
• These are used to prevent invailid data from being entered into the database
• This ensures the accuracy and reliability of the data in the database
• 테이블에 있는 데이터 열에 적용되는 규칙을 제약 조건이라고 한다.
• 제약 조건을 걸어서 불필요 데이터를 필터링 할 수 있다.
• Constraints can be divided into two main categories:
  • Column Constraints
    • Constrains the data in a column to adhere to certain conditions
  • Table Constraints
    • applied to the entire table rather than to an individual column
• 특정 조건에 연결된 하나의 열에 있는 데이터만 제약하는 세로단 제약 조건이 있고,
• 개별 세로단이 아닌 전체 표에 적용되는 표 대상 제약 조건이 있다.
• The most common constraints used:
  • NOT NULL Constraint
    • Ensures that a column cannot have NULL value
  • UIQUE Constraint
    • Ensures that all values in a column are different
• 회원 가입 생각하면 된다.
  • PRIMARY KEY
    • Uniquely identifies each row/record in a database table
  • FOREIGN KEY Constrains data based on columns in other tables
  • CHECK Constraint
    • Ensures that all values in a column satisfy certain conditions
    • 모든 값이 특정한 조건을 만족해야한다.
  • EXCLUSION Constraint
    • Ensures that if any two rows are compared on the specified column or expression using the specified column or expression using the specified operator, not all of these comparisions will return TURE
    • 특정 오퍼레이터를 사용한 특정 열이나 식에서 어떤 두 열이 비교될 때 모든 비교 값이 참으로 판명되지 않아야 한다.
• Table Constraints
  • CHECK (condition)
    • to check a condition when inserting or updating data
  • REFERENCES
    • to constrain the value stored in the column that must exist in a column in another table
  • UNIQUE (column_list)
    • foreces the values stored in the columns listed inside the parentheses to be unique
  • PRIMARY KEY(column_list)
    • Allows you to define the primary key that consists of multiple columns

CREATE

# FULL GENERAL SYNTAX 
CREATE TABLE table_name (
column_name TYPE column_constraint,
column_name TYPE column_constraint,
table_constraint table_constraint
) INHERITS existing_table_name;

#COMMON SIMPLE SYNTAX
CREATE TABLE table_name(
column_name TYPE column_constraint,
column_name TYPE column_constraint,
);

#EXAMPLE SYNTAX
CREATE TABLE players(
player_id SERIAL PRIMARY KEY,
age SMALLINT NOT NULL)

SERIAL

• In PostgreSQL, a sequence is a special kind of database object that generates a sequence of integers
• A sequence is often used as the primary key column in a table
• It will create a sequence object and set the next value generated by the sequence as the default value for the column
• This is perfect for a primary key, becauese it logs unique integer entries for you automatically upon insertion
• If a row is later removed, the column with the SERIAL data type will not adjust, marking the fact a row was removed from the sequence, for example
  • 1,2,3,5,6,7
    • You know row 4 was removed at some point

CREATE TABLE account (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(50) NOT NULL,
email VARCHAR(250) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP 
)

CREATE TABLE job(
job_id SERIAL PRIMARY KEY,
job_name VARCHAR(200) UNIQUE NOT NULL
)

CREATE TABLE account_job(
user_id INTEGER REFERENCES account(user_id),
job_id INTEGER REFERENCES job(job_id),
hire_date TIMESTAMP
)
# SERIAL 을 쓰지 않는다.
# 표의 프라이머리 키일 때만 SERIAL을 사용한다.
# 다른 표와 레퍼런스 할 떄는 그냥 일반 INTEGER면 된다.
# SERIAL은 INTEGER의 시퀀스일 뿐이다.

INSERT

• INSERT allows you to add in rows to a table
• General Syntax
  • INSERT INTO table(column1, column2, ….
  • VALUES
  • (value1, value2, ..),
  • (value1, value2, ..), …;

INSERT INTO TABLE (column1m column2,...)
SELECT column1,column2,...
FROM conther_table
WHERE condition;

INSERT INTO account(username,password,email,created_on)
VALUES
('Jose','password','jose@email.com',CURRENT_TIMESTAMP)

INSERT INTO job(job_name)
values
('Astronaut')

INSERT INTO job(job_name)
values
('President')

INSERT INTO account_job(user_id,job_id,hire_date)
VALUES
(1,1,CURRENT_TIMESTAMP)

UPDATE

• The UPDATE keyword allows for the changing of values of the columns in a table

UPDATE table
SET 
column1 = value1,
column2 = calue2, ...
WHERE
condition;

# 마지막 접속 일에 대한 값이 없으면 값을 현 타임스탬프로 업데이트
UPDATE account SET last_login = CURRENT_TIMESTAMP
WHERE last_login IS NULL;

# 모든 지난 접속 기록을 업데이트 하고 싶을 때
# Reset everythin without WHERE condition
UPDATE account 
SET last_login = CURRENT_TIMESTAMP

# 처음부터 세팅
UPDATE account
SET
last_login = created_on

# 다른 테이블의 값을 사용할 때
UPDATE TableA
SET
original_col = TableB.new_col
FROM tableB
WHERE tableA.id = TableB.id

# 결과 값에 영향이 있는 값을 불러올 때
UPDATE account
SET
last_login = created_on
RETURNING account_id, last_login <- 결과 값을 보여줄 테이블

DELETE

DELETE FROM table
WHERE row_id = 1

# 다른 테이블에 존재하는지 여부에 따라 삭제할 때
DELETE FROM tableA
USING tableB
WHERE tableA.id = tableB.id

# 전체삭제
DELETE FROM table

• Similar to UPDATE command, you can also add in a RETURNING call to return rows that were removed

AFTER

• The ALTER clause allows for changes to an existing table structure, such as:
  • Adding, dropping,or renaming columns
  • Chagning a column’s data type
  • Set DEFAULT values for a column
  • Add CHECK constraints
  • Rename table

# Adding Columns
ALTER TABLE table_name action
ADD COLUMN new_col TYPE

# Removing Columns
ALTER TABLE table_name
DROP COLUMN col_name

# Alter constraints
ALTER TABLE table_name
ALTER COLUMN col_name
SET DEFAULT value
-- or SET NOT NULL
-- or ADD CONSTRAINT constraint_name

CREATE TABLE information (
info_id SERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
person VARCHAR(50) NOT NULL UNIQUE
)

ALTER TABLE information
RENAME TO new_info

ALTER TABLE new_info
RENAME COLUMN person TO people

ALTER TABLE new_info
ALTER COLUMN people DROP NOT NULL (or SET NOT NULL)

DROP

• DROP allows for the complete removal of a column in a table
• In PostgreSQL this will also automatically remove all of its indexes and constraints involving the column
• However, it will not remove columns used in views, triggers, or stored procedures without the additional CASCADE clause

# General Syntax
ALTER TABLE table_name
DROP COLUMN col_name

# Remove all dependencies
ALTER TABLE table_name
DROP COLUMN col_name CASCADE

# Check for existence to avoid error
ALTER TABLE table_name
DROP COLUMN IF EXISTS col_name

# Drop multiple columns
ALTER TABLE table_name
DROP COLUMN col_one,
DROP COLUMN col_two

CHECK

• The CHECK constraint allows us to create more customized constraints that adhere to a certain condition
• Such as making sure all inserted integer values fall below a certain threshold

# General Syntax
CREATE TABLE example(
ex_id SERIAL PRIMARY KEY,
age SMALLINT CHECK (age > 21),
parent_age SMALLINT CHECK(
parent_age > age)
);

# PRACTICE
CREATE TABLE employees(
emp_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
birthdate DATE CHECK 
(birthdate > '1900-01-01'),
hire_date DATE CHECK
(hire_date > birthdate )
salary INTEGER CHECK
(salary > 0)
)

INSERT INTO employees(
first_name,
last_name,
birthdate,
hire_date,
salary
)
VALUES
('Jose',
'JACK',
 '1990-11-11',
 '2011-11-11',
 100
)

• SERIAL을 사용하면 오류 때문에 생성이 되지 않은 테이블에도 프라이머리 키가 들어있고 그 키에서 어떤 오류가 발생했는지 확인이 가능하다.