SQL 기초 - 14. 테이블 관리 (테이블 수정, 필드 수정, SQL ALTER)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 14. 테이블 관리 (테이블 수정, 필드 수정, SQL ALTER)

사실 테이블 컬럼 추가/삭제/변경 등의 작업은 GUI 도구를 사용해서 하는 것이 더 쉽다!

다만 스크립트 환경에서 ALTER 쿼리를 이용해야 하는 상황이 있을 수 있으니, 간단한 예제를 통해 어떤 쿼리가 있는지는 머릿속에 넣어두도록 하자.

 

-- ADD를 통해 컬럼을 추가할 수 있다
ALTER TABLE tCity ADD mayor CHAR(12) NULL;

UPDATE tCity SET mayor = '오륙도' WHERE name = '부산';

ALTER TABLE tCity ADD mayor CHAR(12) NOT NULL;

-- DROP을 이용해 COLUMN을 삭제한다.
ALTER TABLE tCity DROP COLUMN mayor;

DROP TABLE tProject;
CREATE TABLE tProject
(
	projectID INT,
	employee CHAR(10),
	project VARCHAR(30),
	cost INT
);

INSERT INTO tProject VALUES (1, '김상형', '홍콩 수출건', 800);
INSERT INTO tProject VALUES (1, '김상형', 'TV 광고건', 3400);

-- ALTER를 통해 TABLE을 조작할 때는 무결성 규칙을 잘 지켜주어야 한다.
TRUNCATE TABLE tProject;
ALTER TABLE tProject ADD CONSTRAINT PK_projectID PRIMARY KEY(projectID);

오라클, MSSQL : ALTER TABLE tProject DROP CONSTRAINT PK_projectID;
오라클, 마리아 : ALTER TABLE tProject DROP PRIMARY KEY;

-- 예를 들어, FOREIGN KEY 등의 제약이 있을 경우 이런 제약을 없애주어야 할 수도 있다.
DELETE FROM tProject WHERE employee='바이든';
ALTER TABLE tProject ADD CONSTRAINT FK_Project_Employee 
FOREIGN KEY(employee) REFERENCES tEmployee(name);

ALTER TABLE tProject DROP CONSTRAINT FK_Project_Employee;

오라클, 마리아 : ALTER TABLE tCity MODIFY region CHAR(30);
MSSQL : ALTER TABLE tCity ALTER COLUMN region CHAR(30);

INSERT INTO tCity VALUES ('제주',1849,67,'y','제주특별자치도');

오라클, 마리아 : ALTER TABLE tCity MODIFY region CHAR(1);
MSSQL : ALTER TABLE tCity ALTER COLUMN region CHAR(1);

오라클, 마리아 : ALTER TABLE tCity MODIFY popu DECIMAL(10,2);
MSSQL : ALTER TABLE tCity ALTER COLUMN popu DECIMAL(10,2);

UPDATE tCity SET popu = 21.2389 WHERE name = '오산';

CREATE TABLE tCityBackup AS SELECT * FROM tCity;		-- 백업
TRUNCATE TABLE tCity;					-- 원본 비움
ALTER TABLE tCity MODIFY popu DECIMAL(10,2);		-- 필드 타입 변경
INSERT INTO tCity SELECT * FROM tCityBackup;		-- 백업 복원
UPDATE tCity SET popu = 21.2389 WHERE name = '오산';		-- 필드값 변경
COMMIT;									-- 확정
DROP TABLE tCityBackup;					-- 백업 삭제

-- NOT NULL같은 조건이 붙은 컬럼을 만들 수 있다.
-- 하지만 이후 추가되는 데이터는 NOT NULL 제약을 지켜야한다.
-- 또한 기존에 NULL값이 존재하는 컬럼을 NOT NULL속성으로 변경하려면 기존 NULL 데이터를 삭제해야 한다.
DELETE FROM tProject WHERE employee='트럼프';
오라클, 마리아 : ALTER TABLE tProject MODIFY project VARCHAR(30) NOT NULL;	
MSSQL : ALTER TABLE tProject ALTER COLUMN project VARCHAR(30) NOT NULL;

오라클 : ALTER TABLE tProject MODIFY cost DEFAULT 100;
마리아 : ALTER TABLE tProject MODIFY cost VARCHAR(30) DEFAULT 100;
MSSQL : ALTER TABLE tProject ALTER COLUMN project VARCHAR(30) DEFAULT 100 NOT NULL;

INSERT INTO tProject (projectid, employee, project) VALUES (4, '오바마', '기후 변화 회의 참석');

INSERT INTO tProject VALUES (5, '클린턴', '북핵 제거', -100);

DELETE FROM tProject WHERE employee='클린턴';
ALTER TABLE tProject ADD CONSTRAINT cost_check CHECK(cost > 0);

CREATE TABLE tCityBackup AS SELECT * FROM tCity;
DROP TABLE tCity;
CREATE TABLE tCity
(
	name CHAR(10) PRIMARY KEY,
	region CHAR(6) NOT NULL,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL
);
INSERT INTO tCity (name, region, area, popu, metro) 
    SELECT name, region, area, popu, metro FROM tCityBackup;
COMMIT;
DROP TABLE tCityBackup;

-- RENAME을 이용해 컬럼 이름을 바꿀 수 있다.
오라클 : ALTER TABLE tCity RENAME COLUMN popu TO ingu;
MSSQL : sp_rename 'tCity.popu', 'ingu';
마리아 : ALTER TABLE tCity CHANGE popu ingu INT NULL; 

오라클, 마리아 : ALTER TABLE tCity RENAME TO tDosi;
MSSQL : sp_rename 'tCity', 'tDosi';

-- COMMENT를 이용해 DB객체에 설명을 내장시킬 수 있다.
COMMENT ON TABLE tCity IS '도시 목록';
COMMENT ON COLUMN tCity.popu IS '인구';

SELECT COMMENTS FROM user_tab_comments where table_name = 'TCITY';
SELECT * FROM user_col_comments where table_name = 'TCITY';

-- 아래는 SQL Server의 쿼리 예제이다.
sp_addextendedproperty 'MS_Description', '도시목록', 'USER', DBO, 'TABLE', tCity;
sp_addextendedproperty 'MS_Description', '인구', 'USER', DBO, 'TABLE', tCity, 'COLUMN', popu;