데이터베이스 및 테이블 생성

제약 조건(Constraints)

Constraints는 테이블의 데이터 컬럼에 적용되는 규칙을 말한다. 잘못된 데이터가 데이터베이스에 입력되는 것을 방지하여 데이터의 정확성과 신뢰성을 보장할 수 있다.

 

제약 조건은 주로 두 가지(열 제약 조건, 테이블 제약 조건)로 나눌 수 있다.

• 열 제약 조건(Column Constraints)
  • 열의 데이터가 특정 조건을 준수하도록 제한하는 조건
• 테이블 제약 조건(Table Constraints)
  • 개별 컬럼이 아닌 전체 테이블에 적용되는 조건

 

가장 일반적으로 사용되는 제약 조건

제약 조건	설명
NOT NULL	해당 컬럼을 NULL 값을 가질 수 없도록 제한
UNIQUE	해당 칼럼에서의 값이 유일하도록 제한
PRIMARY KEY	데이터베이스 테이블에서 각 행(레코드)를 고유하게 식별할 수 있는 키
FOREIGN KEY	해당 칼럼을 다른 테이블의 PK를 참조하도록 제한
CHECK	행의 모든 값이 특정한 조건을 만족하도록 한다.

 

CHECK 제약 조건 예시

CREATE TABLE products (
	product_no INTEGER PRIMARY KEY,
    name TEXT,
    price NUMERIC CHECK (price > 0) -- 정수 또는 소수값 저장 + 0 이상 커야 한다.
);

 

CASCADE

참조 테이블(부모 테이블)의 해당 행(row)이 삭제되거나 업데이트될 때, 자동적으로 기본 테이블(자식 테이블)에서도 매치되는 행이 똑같이 삭제 또는 업데이트된다.

공식 문서는 CASCADE를 FOREIN KEY CONSTRAINTS에서 옵션으로 사용할 수 있는 Referential Actions라고 설명한다.

 

ON DELETE CASCADE 설

ON DELETE CASCADE 옵션을 적용하면 부모 테이블에서 row를 삭제할 경우 연결된 자식 테이블의 row가 함꼐 삭제된다. 연결된 데이터를 한 번에 지울 수 있어 데이터의 관리가 편리하고 일관성을 유지할 수 있다는 장점이 있다.

 

마찬가지로 ON UPDATE CASCADE를 설정하면 UPDATE할 때 CASCADE 옵션이 적용된다. 근데 PK 같은 경우는 바뀌면 안되기 때문에(UPDATE가 되면 안됨) 쓸 일이 없다고 봐도 무방하다.

 

CREATE

 

구문

CREATE TABLE table_name (
	column_name_1 TYPE COLUMN_CONSTRATNS,
    column_name_2 TYPE COLUMN_CONSTRATNS,
    
    TABLE_CONSTRANTS
);

 

예시

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

• PostgreSQL에서 시퀀스는 정수 시퀀스를 생성하는 특수한 종류의 데이터베이스 개체이다.
• 시퀀스는 종종 테이블의 PK 칼럼에 이용된다.
• 시퀀스가 생성되고, 시퀀스에서 생성된 다음값(NEXT VALUE)를 해당 칼럼의 기본값으로 설정한다.
• 삽입(INSERT) 시 자동으로 고유한 정수 항목을 기록하므로 PK에 적합하다. 

 

INSERT

INSERT는 테이블에 레코드를 추가할 수 있도록 수행한다. 문자 또는 날짜 값의 경우 작은 따옴표('')로 묶는다.

 

구문

-- 일부 칼럼에 대응되는 값만 입력
INSERT INTO table (column_1, column_2, ...) -- COLUMN_LIST
VALUES (value_1, value_2), (value_1, value_2), ...; -- VALUE_LIST

-- 전체 칼럼에 대응되는 값을 모두 입력
INSERT INTO table VALUES (전체 COLUMN의 VALUE_LIST);

 

예시

INSERT INTO account(username, password, email, created_on)
VALUES ('oneny', 'password', 'oneny@mail.com', CURRENT_TIMESTAMP);

INSERT INTO job(job_name) VALUES ('Web Programmer');

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

INSERT INTO
	links(url, name)
VALUES
	('https://www.google.com', 'Google'),
    ('https://www.yahoo.com', 'Yahoo'),
    ('https://www.bing.com', 'Bing');

 

UPDATE

UPDATE는 테이블에 있는 컬럼의 값을 수정, 변경할 수 있도록 수행한다.

 

구문

UPDATE table
SET column_1 = value_1, column_2 = value_2, ...
WHERE condition;

 

예시

UPDATE account
SET last_login = CURRENT_TIMESTAMP
WHERE last_login IS NULL;

 

UPDATE JOIN

UPDATE table_a
SET original_col = table_b.new_col
FROM table_b
WHERE table_a.d = table_b.d;

UPDATE account_job
SET hire_date = account.created_on
FROM account
WHERE account_job.user_id = account.user_id;

다른 테이블의 값을 사용하여 업데이트할 수 있다.

 

결과값에 영향이 있는 값을 불러오기

UPDATE account
SET last_login = created_on
RETURNING account_id, last_login;

UPDATE 명령어를 사용할 때, 결과가 보여지지 않지만 영향을 받은 특정 칼럼을 확인하고 싶은 경우 RETURING을 사용할 수 있다.

 

DELETE

DELETE는 테이블의 레코드를 삭제할 수 있도록 수행한다.

DELETE FROM table
WHERE row_id = 1;

 

ALTER

ALTER는 현재 테이블 구조를 변경할 수 있도록 수행한다.

• 컬럼을 추가, 삭제 및 이름을 변경할 수 있다.
• 컬럼의 데이터 타입을 변경할 수 있다.
• 컬럼의 CHECK 제약 조건을 추가할 수 있다.
• 컬럼에 대한 DEFAULT 값을 설정할 수 있다.
• 테이블의 이름을 변경할 수 있다.

 

구문

ALTER TABLE table_name actoin;

 

테이블 이름 변경

ALTER TABLE information RENAME TO new_info;

 

컬럼 추가

ALTER TABLE table_name
ADD COLUMN new_col TYPE;

 

컬럼 삭제

ALTER TABLE table_name
DROP COLUMN col_name;

 

제약조건 추가 및 수정/삭제

ALTER TABLE table_name
ALTER COLUMN col_name
SET DEFAULT value -- SET NO NULL, DROP DEFAULT, DROP NOT NULL, ADD CONSTRAINT constraint_name;

 

DROP

DROP은 테이블의 컬럼을 삭제할 수 있도록 수행한다. PostgreSQL에서는 자동적으로 해당 컬럼과 관련된 인덱스 및 제약조건들을 삭제해준다.

그러나, 추가 CASCADE 절이 없으면 VIEWS, TRIGGERS, 또는 Stored Procedures에 사용되는 열은 삭제되지 않는다.

 

구문

ALTER TABLE table_name
DROP COLUMN col_name;

 

모든 종속 관계까지 제거(Remove All Dependencies)

ALTER TABLE table_name
DROP COLUMN col_name CASCADE;

 

해당 컬럼 체크 후 삭제

ALTER TABLE table_name
DROP COLUMN IF EXISTS col_name -- 해당 컬럼 없으면 에러가 발생하기 때문에 에러 피하기 위해 작성

 

여러 컬럼 삭제

ALTER TABLE table_name
DROP COLUMN col_one,
DROP COLUMN col_two;

 

데이터 타입 관련 공식문서 자료

https://www.postgresql.org/docs/current/datatype.html

Chapter 8. Data Types