[SQL]ALTER TABLE

작성일자 : 2023-08-18
수정일자 : 2023-09-03
Ver 0.1.2

1. 칼럼 추가

• 테이블에 칼럼을 추가할 때는 ALTER TABLE 명령문의 ADD 절을 사용한다.
• 추가한 칼럼들은 기존 칼럼들 맨 뒤에 추가된다.
  • Syntax

ALTER TABLE [schema.]table
        ADD(column datatype [DEFAULT expr][NOT NULL]
          [,column datatype [DEFAULT expr][NOT NULL]]...
           );

• 구문 설명
  • column : 테이블에 추가할 칼럼의 이름
  • datatype : 해당 칼럼에 저장될 데이터의 타입(ex. varchar2, number, date, ..)
  • DEFAULT : 해당 칼럼에 값이 입력되지 않을 경우, 저장될 기본 값(expr)
  • NOT NULL : 해당 칼럼에 NOT NULL 제약 조건을 지정

EX)

ALTER TABLE t1
        ADD( HIREDATE DATE NOT NULL
            ,COMM     NUMBER DEFAULT 500
            );

DESC t1

COLUMN	Nullable	Type
EMPNO		NUMBER(4)
ENAME	NOT NULL	VARCHAR2(10)
SAL		NUMBER(7,2)
DEPTNO	NOT NULL	NUMBER(2)
HIREDATE	NOT NULL	DATE
COMM		NUMBER

---

2. 칼럼 수정

• 기존 칼럼의 구조를 수정할 때는 ALTER TABLE 명령문의 MODIFY 절을 사용한다.
• 기존 칼럼에 값이 존재하면, 데이터 타입의 크기를 줄일 수 없다. (크기를 늘리는 것은 가능하다.)
  • Syntax

ALTER TABLE [schema.]table
     MODIFY(column datatype [DEFAULT expr][NOT NULL]
          [,column datatype [DEFAULT expr][NOT NULL]]...
           );

• 구문 설명
  • column : 수정하려는 칼럼의 이름
  • datatype : 해당 칼럼에 저장될 데이터의 타입(ex. varchar2, number, date, ..)
  • DEFAULT : 해당 칼럼에 값이 입력되지 않을 경우, 저장될 기본 값(expr)
  • NOT NULL : 해당 칼럼에 NOT NULL 제약 조건을 지정

EX)

ALTER TABLE t1
     MODIFY( ENAME     VARCHAR2(10)
            ,HIREDATE  VARCHAR2(8) NULL
            );

DESC t1

수정 후

COLUMN	Nullable	Type
EMPNO		NUMBER(4)
ENAME	NOT NULL	VARCHAR2(10)
SAL		NUMBER(7,2)
DEPTNO	NOT NULL	NUMBER(2)
HIREDATE		VARCHAR2(8)
COMM		NUMBER

수정 전

COLUMN	Nullable	Type
EMPNO		NUMBER(4)
ENAME	NOT NULL	VARCHAR2(20)
SAL		NUMBER(7,2)
DEPTNO	NOT NULL	NUMBER(2)
HIREDATE	NOT NULL	DATE
COMM		NUMBER

---

3. 칼럼명 변경

• 기존 칼럼의 이름을 변경할 때는 ALTER TABLE 명령문의 RENAME COLUMN 절을 사용한다.
  • Syntax

ALTER TABLE [schema.]table
RENAME COLUMN old_column TO new_column;

• 구문 설명
  • old_column : 기존 칼럼의 변경 전 이름
  • new_column : 칼럼의 변경 후 이름

EX)

ALTER TABLE t1
RENAME COLUMN COMM TO COMMISION;

DESC t1

COLUMN	Nullable	Type
EMPNO		NUMBER(4)
ENAME	NOT NULL	VARCHAR2(10)
SAL		NUMBER(7,2)
DEPTNO	NOT NULL	NUMBER(2)
HIREDATE		VARCHAR2(8)
COMMISION		NUMBER

---

4. 제약조건(Constraints) 추가

• 특정 칼럼에 제약 조건을 추가할 때는 ALTER TABLE 명령문의 ADD CONSTRAINT 절을 사용한다.
  • Syntax

ALTER TABLE [schema.]table
ADD CONSTRAINT constraint_name constraint_type (column [, column, ...]);

• 구문 설명
  • constraint_name : 추가하려는 제약 조건의 이름
  • constraint_type : 추가하려는 제약 조건의 유형
  • column : 해당 제약 조건이 적용될 칼럼(들)

 

• 제약 조건(Constraints) : 오라클 데이터베이스는 데이터 무결성(Data Integrity)을 보장하기 위해 아래와 같은 제약 조건을 지정할 수 있다.

제약조건	설명
UNIQUE 제약	- UNIQUE 제약 조건이 걸린 칼럼(들)에 대해서 동일한 값이 입력되지 못하게 제약한다.
NOT NULL 제약	- NOT NULL 제약 조건이 걸린 칼럼에 대해서 NULL 값이 입력되지 못하게 제약한다.
PK(Primary Key) 제약	- PK 제약 조건 = UNIQUE 제약 조건 + NOT NULL 제약 조건 - 식별자(개별 행을 고유하게 식별할 수 있는 구분자) 역할을 하는 칼럼에 대해 지정한다.
FK(Foreign Key) 제약	- FK 제약조건이 걸린 두 테이블의 칼럼(들)에 대해서 아래와 같은 제약을 한다. - 부모 테이블에 존재하지 않는 칼럼 값은 자식 테이블에 입력할 수 없다. -자식 테이블에 존재하는 칼럼 값은 부모 테이블에서 삭제할 수 없다.
CHECK 제약	- CHECK 제약 조건이 걸린 칼럼에 대해조건을 만족하지 않는 값이 입력되지 못하게 한다. -TRUE or FALSE로 평가될 수 있는 조건을 지정한다.

EX) PK 추가 예제

ALTER TABLE t2
ADD CONSTRAINT t2_pk PRIMARY KEY (DEPTNO);

INSERT INTO t2 (DEPTNO, DNAME, LOC)
     VALUES(10, 'ACCOUNTING', 'NEWYORK');

INSERT INTO t2 (DEPTNO, DNAME, LOC)
     VALUES(20, 'RESEARCH', 'DALLAS');

INSERT INTO t2 (DEPTNO, DNAME, LOC)
     VALUES(20, 'OPERATIONS', 'BOSTON');

ORA-00001 : 무결성 제약 조건(T2_PK)에 위배됩니다.

INSERT INTO t2 (DEPTNO, DNAME, LOC)
     VALUES(30, 'RESEARCH', 'DALLAS');

INSERT INTO t2 (DEPTNO, DNAME, LOC)
     VALUES(NULL, 'SALES', 'CHICAGO');

ORA-01400 : NULL을 ("T2","DEPTNO")안에 삽입할 수 없습니다.

COMMIT;

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	RESEARCH	DALLAS

EX) FK 추가 예제

ALTER TABLE t1
ADD CONSTRAINT t1_fk FOREIGN KEY (DEPTNO) REFERENCES t2 (DEPTNO);

INSERT INTO t1 (EMPNO,ENAME,HIREDATE)
     VALUES(7782, 'CLARK', 10, '19810609');

INSERT INTO t1 (EMPNO,ENAME,HIREDATE)
     VALUES(7369, 'SMITH', 20, '19801217');

INSERT INTO t1 (EMPNO,ENAME,HIREDATE)
     VALUES(7566, 'JONES', 20, '19810402');

INSERT INTO t1 (EMPNO,ENAME,HIREDATE)
     VALUES(7499, 'ALLEN', 50, '19810220');

ORA-02291 : 무결성 제약 조건(T1_FK)이 위배되었습니다 - 부모 키가 없습니다.

DELETE t2 a 
 WHERE a.DEPTNO = 30;

DELETE t2 a 
 WHERE a.DEPTNO = 20;

ORA-02292 : 무결성 제약 조건(T1_FK)이 위배되었습니다 - 자식 레코드가 발견되었습니다.

COMMIT;

[T1 조회 결과]

EMPNO	ENAME	SAL	DEPTNO	HIREDATE	COMMISION
7782	CLARK		10	19810609	500
7369	SMITH		20	19801217	500
7566	JONES		20	19810402	500

[T2 조회 결과]

DEPTNO	HIREDATE	COMMISION
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
20	RESEARCH	DALLAS

---

5. 제약조건(Constraints) 삭제

칼럼에 걸려 있는 제약 조건을 삭제할 때는 ALTER TABLE 명령문의 DROP CONTRAINT 절을 사용한다.

• Syntax

ALTER TABLE [schema.]table
DROP CONSTRAINT constraint_name [CASCADE];

• 구문 설명
  • constraint_name : 삭제하려는 제약 조건의 이름
  • CASCADE : 참조하고 있는 FK 제약 조건을 함께 삭제

 

EX) PK 삭제 예제

ALTER TABLE t2
DROP CONSTRAINT t1_pk;

ORA-02273 : 고유/기본 키가 외부 키에 의해 참조되었습니다

ALTER TABLE t2
DROP CONSTRAINT t1_pk CASCADE; -- t2_pk, t1_fk 제약 조건 삭제

DELETE t2 a 
 WHERE a.DEPTNO = 10;

INSERT INTO t2 (DEPTNO, DNAME, LOC)
     VALUES(20, 'RESEARCH', 'DALLAS');

INSERT INTO t2 (DEPTNO, DNAME, LOC)
     VALUES(20, 'RESEARCH', 'DALLAS');

INSERT INTO t1 (EMPNO,ENAME,HIREDATE)
     VALUES(7499, 'ALLEN', 50, '19810220');

INSERT INTO t1 (EMPNO,ENAME,HIREDATE)
     VALUES(7902, 'FORD', 90, '19811203');

COMMIT;

[T2 조회 결과]

DEPTNO	HIREDATE	COMMISION
20	RESEARCH	DALLAS
20	RESEARCH	DALLAS
20	RESEARCH	DALLAS

[T1 조회 결과]

EMPNO	ENAME	SAL	DEPTNO	HIREDATE	COMMISION
7782	CLARK		10	19810609	500
7369	SMITH		20	19801217	500
7566	JONES		20	19810402	500
7499	ALLEN		50	19810220	500
7902	FORD		90	19811203	500

---

6. 칼럼 삭제

기존 칼럼을 삭제할 때는 ALTER TABLE 명령문의 DROP 절을 사용한다.

• Syntax

ALTER TABLE [schema.]table
DROP (column [, colmun, ...]) [CASCADE CONTRAINS];

• 구문 설명
  • column : 삭제하려는 칼럼의 이름
  • CASCADE CONSTRAINTS : 참조하고 있는 FK 제약 조건을 함께 삭제

 

EX) 칼럼 삭제 예제

ALTER TABLE t1
DROP (hiredate, commission);

DESC t1

COLUMN	Nullable	Type
EMPNO		NUMBER(4)
ENAME	NOT NULL	VARCHAR2(10)
SAL		NUMBER(7,2)
DEPTNO	NOT NULL	NUMBER(2)

EMPNO	ENAME	SAL	DEPTNO
7782	CLARK		10
7369	SMITH		20
7566	JONES		20
7499	ALLEN		50
7902	JONES		90

---

7. COMMENT

테이블에 관한 코멘트를 추가할 때는 COMMENT 문을 사용한다.

• Syntax

COMMENT ON TABLE [schema.]table
IS 'comment';

• 구문 설명
  • table : 대상 테이블명
  • comment : 테이블에 추가할 코멘트

 

EX)

COMMENT ON TABLE t1 IS '사원';

SELECT a.*
  FROM user_tab_comments a
 WHERE a.table_name = 'T1';

TABLE_NAME	TABLE_TYPE	COMMENT
T1	TABLE	사원

칼럼에 관한 코멘트를 추가할 때도 COMMENT 문을 사용한다.

• Syntax

COMMENT ON COLUMN [schema.]table.column
IS 'comment';

• 구문 설명
  • table : 대상 테이블명
  • column : 대상 칼럼명
  • comment : 테이블에 추가할 코멘트

 

EX)

COMMENT ON COLUMN t1.empno IS '사원번호';
COMMENT ON COLUMN t1.ename IS '사원명';
COMMENT ON COLUMN t1.sal IS '급여';
COMMENT ON COLUMN t1.deptno IS '부서번호';

SELECT a.*
  FROM user_col_comments a
 WHERE a.table_name = 'T1';

TABLE_NAME	COLUMN_NAME	COMMENT
T1	EMPNO	사원번호
T1	ENAME	사원명
T1	SAL	급여
T1	DEPTNO	부서번호