[오라클] 제약 조건 생성, ALTER, DROP, MODIFY, UNUSED, TRUNCATE

ddl(data difinition language)
- create, alter, drop, rename  -

 

테이블 복사

A테이블의 데이터를 새로운 B테이블에 '복사'할 수 있다.

CREATE TABLE ANALYST (NUM,NAME,JOB,DNUM)
AS
SELECT PNO,PNAME,JOB,DNO FROM PERSONNEL
WHERE JOB='ANALYST';

기존의 PERSONNEL 테이블에서 WHERE조건을 통해 해당되는 값만 복사하여

ANALYST라는 테이블을 만들었다.

 

CREATE TABLE을 통해 새로운 테이블을 만들며

AS 역시 'INSERT INTO'의 경우 기존에 있는 테이블에 넣기에 입력하지 않지만 지금은 새롭게 만드므로 입력한다.

컬럼명도 괄호 내에서 새롭게 임의로 입력할 수 있다.

 

WHERE 0=1;

조건에서 거짓값을 주게 되면

일치하지 않으므로 데이터는 복사되지 않고 컬럼틀, 즉 컬럼명과 틀만 나오게 된다.

 

또한 제약조건도 복사되지 않는다.

 

테이블 데이터 변경

'ALTER' 를 통해 테이블을 변경한다.

ALTER TABLE SALESMAN1
ADD BIGI VARCHAR2(10) DEFAULT('AA');

ADD로 컬럼을 추가할 수 있다.

데이터 타입은 VARCHAR2 이며 길이는 10이다.

 

DEFAULT는 아무 것도 입력하지 않는 기본 컬럼 데이터 값이 괄호 속의 'AA'로 나오게 한다.

 

제약조건

Primary Key (PK) : 중복값x, 널값x, 한 테이블 내에서 only 1개!

Unique Key (UK) : 중복값x, 널값 가능

NOT NULL : 지정 컬럼은 데이터 입력 필수

Foreign Key (FK) : 외부참조키

CHECK : 조건식을 만족하는 데이터만 입력

 

• CHECK 제약조건 추가

ALTER TABLE SALESMAN1
ADD (MARRIAGE CHAR(8) CONSTRAINT SALESMAN1_MARRIAGE_CK
CHECK (MARRIAGE IN ('SINGLE','MARRIED')));

CHECK 제약조건은 

MARRIAGE와 같은 컬럼에게 조건을 주어 해당되는 값만 들어가도록 한다.

예시에는 SINGEL과 MARRIED 값만 들어가게 하였다.

 

• Primary Key 제약조건 추가

ALTER TABLE MANAGER1
ADD CONSTRAINT MANAGER1_PNO_PK PRIMARY KEY(PNO);

ADD CONSTRAINT 제약조건명 제약조건(해당컬럼);

을 입력한다.

 

임의로 지정할 제약조건명이 없다면

ADD PRIMARY KEY(PNO);

SELECT * FROM USER_CONSTRAINTS;

ADD 제약조건(컬럼명);

오라클 자체에서 제약조건명을 주기 때문에 

추후 제약조건을 확인할 때 이름을 알 수 있다.

 

MODIFY (수정)

테이블 내 컬럼을 변경시키기 위해선 MODIFY를 입력한다.

ALTER TABLE MANAGER1
MODIFY PNAME VARCHAR(16);

PNAME 컬럼은 기존의 데이터 길이보다 더 늘어나 16바이트를 입력할 수 있게 된다.

다만, 컬럼에 이미 있는 데이터 길이보다 작아질 순 없다. 

 

DROP (제약조건 삭제)

제약조건명을 입력하거나 입력하지 않고 삭제하는 방법이 있다.

ALTER TABLE MANAGER1
DROP CONSTRAINT MANAGER1_PNO_PK;

먼저 제약조건 이름을 입력한 경우이다.

DROP CONSTRAINT를 입력후 제약조건명만 입력해주면 된다.

 

혹은 밑의 방법도 가능하다.

ALTER TABLE SALESMAN1
DROP PRIMARY KEY;

위와 달리 CONSTRAINT는 입력하지 않는다.

삭제할 제약 조건만 입력하면 이름을 입력하지 않아도 삭제된다.

 

하지만 참조하는 테이블이 있는 경우, 참조되는 컬럼을 지울 수 없다.

강제로 삭제하기 위해선

ALTER TABLE DIVISION
DROP PRIMARY KEY CASCADE;

CASCADE를 입력함으로써 강제 삭제가 가능하다.

 

컬럼 삭제 및 비활성화

• 컬럼 삭제

ALTER TABLE SALESMAN1
DROP COLUMN MARRIAGE PURGE;

DROP COLUMN으로 특정 컬럼을 삭제한다.

 

PURGE를 입력할 경우, 바로 지워지기 때문에 RECYCLEBIN에 들어가지 않고 제거된다.

못살려내기 때문에 주의해야 한다.

 

• 컬럼 비활성화

ALTER TABLE SALESMAN1
SET UNUSED COLUMN DNO;

ALTER TABLE SALESMAN1
DROP UNUSED COLUMNS;

비활성화는 삭제하는 것이 아니라

클라이언트가 접속을 못하게, 즉 볼 수 없도록 숨겨놓도록 비활성화하는 것이다.

SET UNUSED를 사용하여 특정 컬럼을 비활성화시킨다.

 

비활성화시킨 컬럼을 삭제하기 위해선

밑에 있는 DROP UNUSED를 사용한다.

입력 시 COLUMN'S' 복수형이라는 점 주의하자.

 

복원

지운 테이블을 다시 복원시키는 방법이다.

SELECT * FROM RECYCLEBIN;

FLASHBACK TABLE MANAGER1 TO BEFORE DROP;

FLASHBACK TABLE "BIN$OBuvqkaWTCeBSqcI4uBDWQ==$0" TO BEFORE DROP;

FLASHBACK TABLE 을 이용하여 삭제 전(TO BEFORE DROP) 테이블을 다시 복원시킨다.

테이블명을 입력해도 되고

RECYCLEBIN을 검색하여 나온 쓰레기값의 이름을 입력해도 된다.

 

 

TRUNCATE / DELETE

DELETE PERSONNEL;
ROLLBACK;

TRUNCATE TABLE PERSONNEL;
ROLLBACK;

DELETE는 퍼스널이라는 테이블을 삭제한다.

TRANSACTION은 INSERT,UPDATE,DELETE가 실행되는 경우 바로 시작하기 때문에

마찬가지로 실행되며,

ROLLBACK은 COMMIT 전까지의 상태를 모두 되돌리기 때문에 삭제된 테이블이 다시 복원된다.

 

TRUNCATE는 데이터 레코드를 완전히 삭제하며 데이터베이스에 반영된다.

ROLLBACK이 되지 않아 다시 되돌릴 수 없으므로 주의해야 한다!

 

RENAME

RENAME PAY3000 TO HIGHPAY;

테이블명을 변경할 수 있다.

PAY3000이라는 테이블을 HIGHPAY로 변경한다.

이후 PAY3000을 검색해도 나오지 않게 된다.

 

 

 

 

 

#국비37일차