[PostgreSQL] ALTER 어떻게 쓰더라?

alter 로 참 이것저것 할 수 있는 게 많다.
하지만 (늘 그렇지만) 잘 까먹으니 기록해두겠다 😋

일단 결론부터 작성하고 테스트하는 쿼리를 아래에 차례차례 써보겠다.

🥝 결론부터!

1. 컬럼 추가/삭제하기

-- 추가
alter table <테이블_이름> 
add column <컬럼_이름> <데이터_타입>;


-- 삭제
alter table <테이블_이름> 
drop column <컬럼_이름>;

2. 컬럼 이름 변경하기

alter table <테이블_이름> 
rename column <컬럼_이름> to <변경할_컬럼_이름>;

3. NOT NULL 추가/제거

alter table <테이블_이름>  
alter column <컬럼_이름> set not null;

alter table <테이블_이름>  
alter column <컬럼_이름> drop not null; 

4. Primary key 추가/제거

alter table <테이블_이름>
add constraint <기본키_제약조건_이름> 
	primary key (<기본키로 설정할 컬럼명>)

alter table <테이블_이름> 
drop constraint <기본키_제약조건_이름>;

5. Foreign key 추가/제거

alter table <테이블_이름>
add constraint <외래키_제약조건_이름>  
		foreign key (<외래키로 지정할 컬럼명>) 
		references <참조할_테이블_이름>(<참조 테이블의 컬럼>);

alter table <테이블_이름> 
drop constraint <외래키_제약조건_이름>;

🥝 테이블에 컬럼 추가/제거

1. 컬럼 추가하기

-- 테스트 테이블 생성
create table test_alter (
	id integer not null primary key,
	name varchar(20) not null
);


-- 테스트 테이블에 컬럼 추가
alter table test_alter 
add column more_column varchar(20);

-- 컬럼 추가 1
alter table test_alter 
add column add_col_01 varchar(20);

-- 컬럼 추가 2 + check 제약도 같이주기
alter table test_alter 
add column add_col_02 integer check (add_col_02 > 0);

-- insert test : 성공
insert into test_alter 
values (1, 'goodjob', 'some', 20);


-- insert test : 실패, add_col_02 의 check 조건 때문에 실패
--insert into test_alter 
-- values (1, 'goodjob', 'some', -1);


select * from test_alter;
--------------------------------------
-- ||id|name   |add_col_01|add_col_02|
-- ||--+-------+----------+----------+
-- || 1|goodjob|some      |        20|
--------------------------------------


------------------------------------------------------------
-- 참고) 컬럼 추가 및 not null 제약
------------------------------------------------------------


-- 아래 쿼리를 돌려보자. 아마 에러가 날것이다.

alter table test_alter 
add column add_col_03 varchar(20) not null;
-- SQL Error [23502]: 오류: "add_col_03" 열(해당 릴레이션 "test_alter")
--    의 자료 가운데 null 값이 있습니다


-- 아쉽지만 add column 방식으로는 not null 제약을 주지 못한다.
-- 대신 컬럼을 먼저 추가한 다음에 임시적으로 값을 다 넣어주고 나서
-- not null 제약 조건을 추가하는 방식으로 이를 해결할 수 있다.


alter table test_alter 
add column add_col_03 varchar(20);

update test_alter 
set add_col_03 = 'temp'
where id = 1;


alter table test_alter 
alter column add_col_03 set not null; 

select * from test_alter;

--||id|name   |add_col_01|add_col_02|add_col_03|
--||--+-------+----------+----------+----------+
--|| 1|goodjob|some      |        20|temp      |


-- not null 확인
SELECT 
   table_name, 
   column_name, 
   data_type,
   is_nullable 
FROM 
   information_schema.columns
WHERE 
   table_name = 'test_alter'
;   

--||table_name|column_name|data_type        | is_nullable|
--||----------+-----------+-----------------+------------+
--||test_alter|id         |integer          | NO         |
--||test_alter|add_col_02 |integer          | YES        |
--||test_alter|name       |character varying| NO         |
--||test_alter|add_col_01 |character varying| YES        |
--||test_alter|add_col_03 |character varying| NO         |

2. 컬럼 제거하기

제거하기에 앞서서 외래키로 사용되는 컬럼을 지웠을 때는 어떻게 되는지 보기 위해서
테이블을 다시 생성하고 테스트 해보자.

drop table test_alter cascade;

create table test_alter (
	id integer not null primary key,
	age integer not null,
	name varchar(20) not null unique -- 외래키로 사용하기 위해서 unique 설정
);

create table test_refer (
	refer_id integer not null primary key,
	alter_id varchar references test_alter(name)
);

insert into test_alter 
values(1, 20, 'some');

insert into test_refer
values(1, 'some');

select * from test_alter;
select * from test_refer;

-- ********* 컬럼 삭제 *********
alter table test_alter 
drop column age;
-- ****************************

select * from test_alter;

--id|name|
----+----+
-- 1|some|


-- 외래키로 쓰이는 컬럼을 지우려고 하면...?
alter table test_alter 
drop column name;
-- SQL Error [2BP01]: 오류: 기타 다른 개체들이 이 개체에 의존하고 있어...(생략)


alter table test_alter 
drop column name cascade;
-- test_refer_alter_id_fkey 제약 조건(해당 개체: test_refer 테이블) 개체가 덩달아 삭제됨

-- 하지만 외래키를 참조하는 테이블의 row 가 지워지는 건 아님!
-- 그냥 외래키 제약 조건이 삭제될 뿐이다!
select * from test_refer ;
--||refer_id|alter_id|
--||--------+--------+
--||       1|some    |

🥝 컬럼명 수정

create table change_name (
	id serial primary key,
	name varchar(20) not null
);

alter table change_name 
rename column name to changed_name;
-- 참고로 만약 name 을 외래키로 쓰는 다른 테이블이 있는 상태에서
-- 컬럼 이름을 변경하면, 자식 테이블의 참조하는 컬럼 이름도 자동으로 바뀐다.

🥝 테이블에 제약조건 추가

이번 게시물의 가장 중요한 부분이다! 집중!

1. NOT NULL 추가/제거

not null 제약조건을 추가 및 삭제해보자.

create table test_table (
	id serial primary key,
	name varchar(20)
);

alter table test_table  
alter column name set not null;

alter table test_table  
alter column name drop not null; 

2. primary key 추가/삭제

기본키 제약조건을 추가 및 삭제해보자.

create table test_table (
	id serial,
	name varchar(20)
);

alter table test_table
add constraint test_table_pk primary key (id);

alter table test_table 
drop constraint test_table_pk;


-- 참고) 아래 쿼리로 기본키 생성/삭제 여부를 확인할 수 있다.
SELECT 
   * 
FROM 
   information_schema.key_column_usage
WHERE 
   table_name = 'test_table'
;

3. foreign key 추가

외래키 제약조건을 추가 및 삭제해보자.

drop table if exists test_table cascade;

create table test_table (
	id serial primary key,
	name varchar(20) unique -- 외래키로 사용되기 위해서 unique 로...
);

create table test_refer(
	t_id serial,
	fk_name varchar(20)
)

alter table test_refer
add constraint test_table_fk foreign key (fk_name) references test_table(name);

alter table test_refer 
drop constraint test_table_fk;


-- 확인!
SELECT 
   * 
FROM 
   information_schema.key_column_usage
WHERE 
   table_name = 'test_refer'
;