postgres Database # Primary Key

# PRIMARY KEY

기본키는 데이터베이스에서 굉장히 중요한 개념이다.

기본키의 특징은 모든 row에 대해서,

식별가능해야 한다. 기본키가 중복된다면,

기본키로 사용할 수 없다는 것이다.

 

사람마다 구별할 수 있는 기본키는 주민번호가 된다.

여권번호가 될 수도 있다.

자동차를 구별할 수 있는 건 차대번호가 된다.

 

기본키는 항상 중복이 없어야 한다.

 

기본키를 확인하려면 테이블을 확인하면 아래에

기본키에 대한 내역이 출력된다.

 

person_pkey가 이 테이블의 기본키로 작동하고 있다.

 

# 기본키 제거

 

기본키를  ALTER 명령어로 삭제를 하면

기본키가 삭제된 것을 확인할 수 있다.

테이블 아래에 어떤 CONSTRAINT도 출력되지

않는다.

 

이 테이블에 새로운  row를 추가하게 되면 id가 동일하더라도

기본키가 삭제되었기 때문에, INSERT가 정상적으로 작동하는

것을 확인할 수 있다.

 

id=1인 ROW를 확인해보면 아래와 같이 다수의 ROW가 나오는 

것을 확인할 수 있다. 데이터의 무결성과 중복제거를 해야하는

상황에서 아래 테이블은 부적합한 데이터베이스 테이블이다.

 

데이터베이스에 새로운 기본키를 추가하고,(ALTER)

새로운 ROW를 추가하고,

조회를 해보면 정상적으로 기본키가 작동하고 있고,

중복을 제거한 데이터베이스를 확인할 수 있다.

 

# 새로운 CONSTRAINT 추가하기

새로운 제약사항를 추가하여 다수의 키로 ROW를

구분할 수 있다.

 

email을 기본키로 만든다고 가정해보자.

동일한 email을 가진 새로운 ROW를 

INSERT한다.

테이블을 확인해보면, 동일한 email을 가진

2개의 ROW를 확인할 수 있다.

 

새로운 제약사항을 추가한다.

 ALTER TABLE person ADD CONSTRAINT unique_email_address UNIQUE(email);

하지만 에러가 발생한다.

기존에 이미 중복된 ROW를 가지고 있기 때문에,

제약사항 추가가 불가능하다는 에러 메시지다.

기존 중복된 ROW를 제거한다.

 

다시 제약사항을 추가하게 되면,

정상적으로 추가된 것을 확인할 수 있고,

테이블을 확인해보면 정상적으로

제약사항이 추가된 것을 확인할 수 있다.

 

이제 중복된 email을 가진 ROW를 추가하게 되면,

제약사항이 작동하여 INSERT가 불가능 하게 된다.

Err: 중복된 키 값이 "unique_email_address" 고유 제약 조건을 위반함

 

제약사항 추가하는 방법 두번째

CONSTRAINT "name"없이 제약조건을

추가할 수 있다. 하지만 이렇게 추가를

하게 되면, postgres 자체에서 default로

name을 추가하게 된다.

ALTER TABLE person ADD UNIQUE(email);

 

아래 예시에서는 제약조건의 이름을

"person_email_key"로 자동 설정 되었다.

 

# 제약조건 제거

ALTER TABLE person DROP CONSTRAINT unique_email_address;

 

# CHECK 제약조건 추가

성별을 나타내는 제약조건을 추가하는 경우

Female과 Male 두가지의 데이터만 받을 

것이다. 다른 데이터가 들어오는 것을 방지하기 

위한 조건이 CHECK 제약조건이다.

 

ALTER TABLE person ADD CONSTRAINT gender_constraint CHECK (gender='Female' OR gender='Male');

 

CHECK 제약조건을 추가 후 테이블을 확인해보면,

새로운 제약조건이 추가된 것을 확인할 수 있다.

 

새로운 유형의 gender record를 추가하면 

제약사항이 작동하여 INSERT가 불가능하다.

 

 

# DELETE RECORDS

특정 record를 삭제하는 명령문이다.

중요한 것은 DROP 명령문과는 다르게,

테이블 ROW가 삭제되는 것이 아니라,

테이블 ROW는 그대로 존재하는 상황에서,

그 안의 데이터만 삭제되는 것이다.

 

DELETE 후 새로운 record를 INSERT 하게 되면,

PRIMARY KEY값이 추가로 계속 증가한 것을

확인할 수 있다.

DELETE FROM person;
DELETE FROM person WHERE gender='Female' AND id=1;

 

# UPDATE RECORDS

INSERT, DELETE, UPDATE 모두

DML의 한 종류다.

테이블의 데이터를 조작하는 명령어다.

 

SET은 복수의 column을 담을 수 있는

키워드다. UPDATE 명령어로 복수의

column 데이터를 변경할 수 있다.

UPDATE person SET gender='Male' WHERE gender='Bigender' AND gender='Genderfluid';

 

 

 

 

# ON CONFLICT Excetion catch

제약조건이 존재하는 경우 새로운 record를 입력하면

에러가 발생한다. 이런 경우 에러를 처리해주는 catch문이

필요하다.

 

제약조건이 있는 경우 ON CONFLICT 키워드를 사용해서,

제약조건 위반이 발생한 경우 필요한 조치를 미리

catch해준다. 

ON CONFLICT(email) DO NOTHING;

Exception을 처리해주게 되면,

제약조건을 위반한 INSERT문을 입력해도

새로운 Record가 입력되지 않고,

INSERT 0 0을 반환한다.

 

# UPSERT

CONFLICT가 발생하는 경우 DO NOTHING문은

소극적인 대응이다. 

예를 들어 User Registration 화면에서 사용자가

입력한 자료값이 confilct를 만든다고해도, DB는

정상 작동해야 할 것이다. 이 때 잘못된 자료값을

정제해서 DB에 등록하는 작업이 필요하다.

 

ON CONFLICT(email) DO UPDATE SET email=EXCLUDED.email;

 

기존에 제약사항이 존재하면, 추가로 INSERT가 불가능하다.

이처럼 CONFLICT가 발생할 때, DO UPDATE를 통해 

제외된 자료값으로 UPDATE가 가능하다.

 

이 경우 사용해야 하는 키워드가 EXCLUDED다.

제약조건에 의해 제외된 값이지만 UPDATE를 통해서

실제 TABLE에 등록이 가능한 것이다.

 

기본키 제약조건이 존재하여 id=4인 경우 INSERT가 불가능하다.

 

기본키 제약사항 CONFLICT가 발생하는 경우

first_name과 last_name은 INSERT문의 값인

EXCLUDED.first_name과 EXCLUDED.last_name값으로

UPDATE를 해라는 명령문을 추가하면,

두개의 column만 UPDATE가 된 것을 확인할 수 있다.

ON CONFLICT(id) DO UPDATE SET first_name=EXCLUDED.first_name, last_name=EXCLUDED.last_name;