SQL 기본 및 활용 - 제 2절 DDL

 1.데이터 유형

- 숫자 타입

  ANSI/ISO 기준 : NUMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, BIGINT, FLOAT, REAL, DOUBLE PRECISION

  SQL Server와 Sybase : 작은 정수형, 정수형, 큰 정수형, 실수형 등 + MONEY, SMALLMONEY

  Oracle : 숫자형 타입에 대해서 NUMBER 한 가지 숫자 타입의 데이터 유형만 지원

 

- 벤더에서 ANSI/ISO 표준을 사용할 때는 기능을 중심으로 구현하므로, 일반적으로 표준과 다른 용어를 사용하는 것이 허용

   (ex: NUMERIC → NUMBER, WINDOW FUNCTION → ANALYTIC/RANK FUNCTION)  

 

- 테이블의 칼럼이 가지고 있는 대표적인 4가지 데이터 유형

  기타 유형 : ANSI/ISO에서 Binary String Type, Binary Large Object String Type, National Character String Type, Boolean Type 등

 

- 문자열 유형 :  CHAR 유형과 VARCHAR 유형의 차이

   VARCHAR 유형 : 가변 길이, CHAR 유형 : 고정길이

   비교 방법의 차이 : 

                     CHAR에서는 문자열을 비교할 때 공백(BLANK)을 채워서 비교

                     VARCHAR유형에서는 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급

                     예) CHAR 유형'AA' = 'AA '       예) VARCHAR 유형 'AA' ≠ 'AA '

 

 

2. CREATE TABLE

가. 테이블과 칼럼 정의 : 후보키중에 하나를 선정하여 기본키 칼럼으로 지정

(예) 선수 테이블 

(예) 부서와 사원 테이블

​

나. CREATE TABLE

​CREATE　TABLE　테이블이름 ( 칼럼명1 DATATYPE DEFAULT 형식, 칼럼명2 DATATYPE DEFAULT 형식, 칼럼명3 DATATYPE DEFAULT 형식 ) ;

 

다음은 테이블 생성 시에 주의해야 할 몇 가지 규칙이다.

- 테이블명 : 객체를 의미할 수 있는 적절한 이름을 사용

- 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다. - A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

- 테이블명이 잘못된 사례

 

(예) 선수 테이블 생성

테이블명 : PLAYER

테이블 설명 : K-리그 선수들의 정보를 가지고 있는 테이블

칼럼명 : => 

제약조건 : 기본키(PRIMARY KEY) → PLAYER_ID (제약조건명은 PLAYER_ID_PK)

                   값이 반드시 존재 (NOT NULL) → PLAYER_NAME, TEAM_ID

Oracle

CREATE TABLE PLAYER (

PLAYER_ID CHAR(7) NOT NULL,

PLAYER_NAME VARCHAR2(20) NOT NULL,

TEAM_ID CHAR(3) NOT NULL,

E_PLAYER_NAME VARCHAR2(40),

NICKNAME VARCHAR2(30),

JOIN_YYYY CHAR(4),

POSITION VARCHAR2(10),

BACK_NO NUMBER(2),

NATION VARCHAR2(20),

BIRTH_DATE DATE,

SOLAR CHAR(1),

HEIGHT NUMBER(3),

WEIGHT NUMBER(3),

CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),

CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID) );

SQL Server CREATE TABLE PLAYER ( PLAYER_ID CHAR(7) NOT NULL, PLAYER_NAME VARCHAR(20) NOT NULL, TEAM_ID CHAR(3) NOT NULL, E_PLAYER_NAME VARCHAR(40), NICKNAME VARCHAR(30), JOIN_YYYY CHAR(4), POSITION VARCHAR(10), BACK_NO TINYINT, NATION VARCHAR(20), BIRTH_DATE DATE, SOLAR CHAR(1), HEIGHT SMALLINT, WEIGHT SMALLINT, CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID), CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID) );

 

테이블 생성 예제에서 추가적인 주의 사항 몇 가지

- 테이블 생성시 대/소문자 구분은 하지 않는다. 기본적으로 테이블이나 칼럼명은 대문자로

- DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.

- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.

- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.

- 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

 

 

 

다. 제약조건(CONSTRAINT)

제약조건(CONSTRAINT) :  데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법

- 제약조건의 종류

- NULL 의미 :

조건에 맞는 데이터가 없을 때의 공집합과도 다르다.

‘NULL’은 ‘아직 정의되지 않은 미지의 값’이거나 ‘현재 데이터를 입력하지 못하는 경우’를 의미

 

- DEFAULT 의미

데이터 입력 시에 칼럼의 값이 지정되어 있지 않을 경우 기본값(DEFAULT)

 

예제 팀 테이블

테이블명 : TEAM

테이블 설명 : K-리그 선수들의 소속팀에 대한 정보를 가지고 있는 테이블

칼럼명 : =>

제약조건 : 기본 키(PRIMARY KEY) → TEAM_ID (제약조건명은 TEAM_ID_PK)

                    NOT NULL → REGION_NAME, TEAM_NAME, STADIUM_ID (제약조건명은 미적용)

Oracle

SQL Server

CREATE TABLE TEAM ( TEAM_ID CHAR(3) NOT NULL, REGION_NAME VARCHAR2(8) NOT NULL, TEAM_NAME VARCHAR2(40) NOT NULL, E_TEAM_NAME VARCHAR2(50), ORIG_YYYY CHAR(4), STADIUM_ID CHAR(3) NOT NULL, ZIP_CODE1 CHAR(3), ZIP_CODE2 CHAR(3), ADDRESS VARCHAR2(80), DDD VARCHAR2(3), TEL VARCHAR2(10), FAX VARCHAR2(10), HOMEPAGE VARCHAR2(50), OWNER VARCHAR2(10), CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID), CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID) );

CREATE TABLE TEAM ( TEAM_ID CHAR(3) NOT NULL, REGION_NAME VARCHAR(8) NOT NULL, TEAM_NAME VARCHAR(40) NOT NULL, E_TEAM_NAME VARCHAR(50), ORIG_YYYY CHAR(4), STADIUM_ID CHAR(3) NOT NULL, ZIP_CODE1 CHAR(3), ZIP_CODE2 CHAR(3), ADDRESS VARCHAR(80), DDD VARCHAR(3), TEL VARCHAR(10), FAX VARCHAR(10), HOMEPAGE VARCHAR(50), OWNER VARCHAR(10), CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID), CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID) );

​

라. 생성된 테이블 구조 확인

예제 선수(PLAYER) 테이블의 구조를 확인한다.

실행 결과 Oracle

DESCRIBE PLAYER;

 

실행 결과 SQL Server

exec sp_help 'dbo.PLAYER' go

 

​

마. SELECT 문장을 통한 테이블 생성 사례

SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법(CTAS: Create Table ~ As Select ~)

CTAS 기법 사용시 주의할 점 :

              기존 테이블의 제약조건 중에 NOT NULL만 새로운 복제 테이블에 적용이 되고,

              기본키, 고유키, 외래키, CHECK 등의 다른 제약 조건은 없어진다는 점이다.

              제약 조건을 추가하기 위해서는 뒤에 나오는 ALTER TABLE 기능을 사용해야 한다.

             SQL Server에서는 Select ~ Into ~ 를 활용하여 위와 같은 결과를 얻을 수 있다.

                         단, 칼럼 속성에 Identity를 사용했다면 Identity 속성까지 같이 적용이 된다.

 

예제 선수(PLAYER) 테이블과 같은 내용으로 TEAM_TEMP라는 복사 테이블을 만들어 본다.

예제 Oracle

CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM; 테이블이 생성되었다.

예제 SQL Server

SELECT * INTO TEAM_TEMP FROM TEAM; (1개 행이 영향을 받음)

​

​

3. ALTER TABLE

가. ADD COLUMN : 

ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;

주의 : 새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치를 지정할 수는 없다.

 

예제 PLAYER 테이블에 ADDRESS(데이터 유형은 가변 문자로 자릿수 80자리로 설정한다.) 칼럼을 추가한다.

예제 Oracle

ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));

 

예제 SQL Server

ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80);

 

나. DROP COLUMN

ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

예제 앞에서 PLAYER 테이블에 새롭게 추가한 ADDRESS 칼럼을 삭제한다.

예제 Oracle

ALTER TABLE PLAYER DROP COLUMN ADDRESS;  

 

예제 SQL Server

ALTER TABLE PLAYER DROP COLUMN ADDRESS; 

​

​

다. MODIFY COLUMN

ALTER TABLE 명령을 이용해 칼럼의 데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건에 대한 변경을 포함

 

Oracle ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 DEFAULT 식 NOT NULL, 칼럼명2 데이터 유형 …);

SQL Server ALTER TABLE 테이블명 ALTER (칼럼명1 데이터 유형 DEFAULT 식 NOT NULL, 칼럼명2 데이터 유형 …);

 

칼럼을 변경할 때는 몇 가지 사항을 고려해서 변경해야 한다.

- 해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다.

- 해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

​- 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.

- 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

- 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

 

예제 TEAM 테이블의 ORIG_YYYY 칼럼의 데이터 유형을 CHAR(4)→VARCHAR2(8)으로 변경하고, 향후 입력되는 데이터의 DEFAULT 값으로 '20020129'을 적용하고, 모든 행의 ORIG_YYYY 칼럼에 NULL이 없으므로 제약조건을 NULL → NOT NULL로 변경한다.

 

예제 Oracle

ALTER TABLE TEAM_TEMP MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

예제 SQL Server

ALTER TABLE TEAM_TEMP ALTER COLUMN ORIG_YYYY VARCAHR(8) NOT NULL; 

ALTER TABLE TEAM_TEMP ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY; 

​

■ RENAME COLUMN

ALTER TABLE 테이블명 RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명;

 

예제 Oracle

=> RENAME COLUMN으로 칼럼명이 변경되면, 해당 칼럼과 관계된 제약조건에 대해서도 자동으로 변경되는 장점이 있지만, ADD/DROP COLUMN 기능처럼 ANSI/ISO에 명시되어 있는 기능이 아니고 Oracle 등 일부 DBMS에서만 지원하는 기능이다.

 

ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEMP_ID;

ALTER TABLE PLAYER RENAME COLUMN TEMP_ID TO PLAYER_ID; 

 

예제 SQL Server

sp_rename 변경해야 할 칼럼명, 새로운 칼럼명, 'COLUMN';

sp_rename 'dbo.TEAM_TEMP.TEAM_ID', 'TEAM_TEMP_ID', 'COLUMN';

주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있다.

 

라. DROP CONSTRAINT

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

 

예제 PLAYER 테이블의 외래키 제약조건을 삭제한다.

예제 Oracle

ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK; 테이블이 변경되었다.

 

예제 SQL Server

ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK; 명령이 완료되었다.

​

마. ADD CONSTRAINT

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

 

예제 PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다.

제약조건명은 PLAYER_FK로 하고, PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건이다.

 

예제 Oracle

ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

 

예제 SQL Server

ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

 

예제 PLAYER 테이블이 참조하는 TEAM 테이블을 제거해본다.

예제 Oracle

DROP TABLE TEAM; ERROR: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있다.

​※ 테이블은 삭제되지 않음

 

예제 SQL Server

DROP TABLE TEAM; ERROR: 엔터티 'TEAM'은 FOREIGN KEY 제약 조건을 참조하므로 삭제할 수 없다. ※ 테이블은 삭제되지 않음

 

 

예제 PLAYER 테이블이 참조하는 TEAM 테이블의 데이터를 삭제해본다.

예제 Oracle

 DELETE TEAM WHERE TEAM_ID = 'K10'; ERROR: 무결성 제약조건(SCOTT.PLAYER_FK)이 위배되었다. 자식 레코드가 발견되었다. ※ 데이터는 삭제되지 않음

 

예제 SQL Server

DELETE TEAM WHERE TEAM_ID = 'K10'; ERROR: FOREIGN KEY 제약 조건을 참조하므로 삭제할 수 없다. ※ 데이터는 삭제되지 않음

​

 

위와 같이 참조 제약조건을 추가하면 PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID 칼럼을 참조하게 된다. 참조 무결성 옵션에 따라서 만약 TEAM 테이블이나 TEAM 테이블의 데이터를 삭제하려 할 경우 외부(PLAYER 테이블)에서 참조되고 있기 때문에 삭제가 불가능하게 제약을 할 수 있다. 즉, 외부키(FK)를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지하는 효과를 볼 수 있다.

 

 

4. RENAME TABLE

RENAME 변경전 테이블명 TO 변경후 테이블명;

SQL Server에서는 sp_rename을 이용하여 테이블 이름을 변경할 수 있다.

sp_rename 변경전 테이블명, 변경후 테이블명;

 

예제 RENAME 문장을 이용하여 TEAM 테이블명을 다른 이름으로 변경하고, 다시 TEAM 테이블로 변경한다.

 

예제 Oracle

RENAME TEAM TO TEAM_BACKUP; 테이블 이름이 변경되었다. RENAME TEAM_BACKUP TO TEAM;

 

예제 SQL Server

sp_rename 'dbo.TEAM','TEAM_BACKUP'; 주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있다.

sp_rename 'dbo.TEAM_BACKUP','TEAM'; 주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있다.

​

​

​

5. DROP TABLE

DROP TABLE 테이블명 CASCADE CONSTRAINT;

CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미한다.

SQL Server에서는 CASCADE 옵션이 존재하지 않으며 테이블을 삭제하기 전에 참조하는 FOREIGN KEY 제약 조건 또는 참조하는 테이블을 먼저 삭제해야 한다.

 

예제 PLAYER 테이블을 제거한다.

예제 Oracle

DROP TABLE PLAYER; 테이블이 삭제되었다. DESC PLAYER; ERROR: 설명할 객체를 찾을 수 없다.

 

예제 SQL Server

DROP TABLE PLAYER; 명령이 완료되었다. exec sp_help 'dbo.PLAYER'; 메시지 15009, 수준 16, 상태 1, 프로시저 sp_help, 줄 66 데이터베이스 ‘northwind'에 엔터티 'dbo.player'이(가) 없거나 이 작업에 적합하지 않다.

 

 

 

 

6. TRUNCATE TABLE

TRUNCATE TABLE PLAYER;

TRUNCATE TABLE은 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 된다.

 

예제 TRUNCATE TABLE을 사용하여 해당 테이블의 모든 행을 삭제하고 테이블 구조를 확인한다.

예제 Oracle

TRUNCATE TABLE TEAM; 테이블이 트렁케이트되었다.

 

예제 SQL Server

TRUNCATE TABLE TEAM; 명령이 완료되었다.

 

예제 DROP TABLE을 사용하여 해당 테이블을 제거하고 테이블 구조를 확인한다.

예제 Oracle

DROP TABLE TEAM; 테이블이 삭제되었다. DESC TEAM; ERROR: 설명할 객체를 찾을 수 없다.

예제 SQL Server

DROP TABLE TEAM; 명령이 완료되었다.

exec sp_help 'dbo.TEAM'; 메시지 15009, 수준 16, 상태 1, 프로시저 sp_help, 줄 66 데이터베이스 'northwind'에 엔터티 'dbo.TEAM'이(가) 없거나 이 작업에 적합하지 않다.

 

​

TRUNCATE는 데이터 구조의 변경 없이 테이블의 데이터를 일괄 삭제하는 명령어로 DML로 분류할 수도 있지만 내부 처리 방식이나 Auto Commit 특성 등으로 인해 DDL로 분류하였다.

테이블에 있는 데이터를 삭제하는 명령어는 TRUNCATE TABLE 명령어 이외에도 다음 DML 절에서 살펴볼 DELETE 명령어가 있다. 그러나DELETE와 TRUNCATE는 처리하는 방식 자체가 다르다.

테이블의 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE을 권고한다. 단, TRUNCATE TABLE의 경우 정상적인 복구가 불가능(로그를 가지고 있다.)하므로 주의해야 한다.

 

Delete/modify action:

1) Cascade : Master 삭제시 Child 같이 삭제

2) Set null : Master 삭제시 Child 해당 필드 Null

3) Set default : Master 삭제시 Child 해당 필드 Default 값으로 설정

4) Restrict : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용

5) No action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

 

Insert action:

1) Automatic : Master 테이블에 PK가 없는 경우 Master PK를 생성후 Child 입력

2) Set null :  Master 테이블에 PK가 없는 경우 Child 외부키를 null 값으로 처리

3) Set default : Master 테이블에 PK가 없는경우 Child 외부키를 지정된 기본값으로 입력

4) Dependent : Master 테이블에 PK가 존재할 때만 Child 입력허용

5) No action: 참조무결성을 위반하는 입력 액션을 취하지 않음.

 

연습 문제:

[CREATE TABLE 관련 문제]

product 
prod_id: varchar2(10)not null (primary key)
prod_nm:varchar2(100)not null
reg_de:date not null
regr_no:number(10)null
(IE표기)

두가지 방법이 가능하다.

1.  ALTER TABLE _____ADD CONSTRAINT TABLE_PK PRIMARY KEY(___) 나중에 괄호 닫고 추가하는 경우

2. CONSTRAINT TABLE_PK PRIMARY KEY()를 CREATE TABLE ___()안에 추가하는 경우

CREATE TABLE PRODUCT(
PROD_ID VARCHAR2(10)NOT NULL
,PROD_NM VARCHAR2(100)NOT NULL
,REG_DT DATE NOT NULL
,REGR_NO NUMBER(10) NULL);

ALTER TABLE PRODUCT ADD CONSTRAINT PRIMARY KEY PRODUCT_PK PRIMART KEY(PROD_ID);


CREATE TABLE PRODUCT(
PROD_ID VARCHAR2(10)NOT NULL
,PROD_NM VARCHAR2(100)NOT NULL
,REG_DT DATE NOT NULL
,REGR_NO NUMBER(10) NULL
CONSTRAINT PRODUCT_PK PRIMARY KEY(PROD_ID));

[ALTER TABLE 관련문제]

기관 분류 
분류 ID: VARCHAR(10)NULL NULL <<PK                        VARCHAR(10)NOT NULL
분류명: VARCHAR(10)NOT NULL                        >>>>.  VARCHAR(30)NOT NULL
등록일자: VARCHAR(10)NULL                                  DATE NOT NULL

 

>>반드시 ALTER 하나당 한개만 변경 가능 

ALTER TABLE 기관분류 ALTER COLUMN 분류명 VARCHAR(30) NOT NULL;
ALTER TABLE 기관분류 ALTER COLUMN 등록일자 DATE NOT NULL;
>>올바른 경우 

ALTER TABLE 기관분류 ALTER COLUMN (분류명 VARCHAR(30)NOT NULL, 등록일자 DATE NOT NULL);
>>틀린 경우 (반드시 하나의 ALTER당 하나의 COLUMN을 바꿀수 있다***)

[DELETE 관련문제]

CREATE TABLE T
(C INTEGER PRIMARY KEY,
D INTEGER);

C	D
2	1
1	1

CRATE TABLE S
(B INTEGER PRIMARY KEY,
C INTEGER REFERENCES T(C) ON DELETE CASCADE); >>T테이블에 C를 참조하고 삭제될 시 테이블B의 모든 데이터 같이 삭제된다.

B	C
1	1
2	1

CREATE TABLE R
(A INTEGER PRIMARY KEY,
B INTEGER REFERENCE S(B) ON DELETE SET NULL); >>S테이블에 B를 참조하고 삭제되면 NULL로 setting

A	B
1	1
2	2

Table R에 남아있는 데이터 값은?

(1, NULL)과 (2,NULL)

>>따라서 S테이블 전체가 삭제되므로 테이블 S를 참조한 COLUMN B는 NULL로 설정된다.

DELETE CASCADE 상위 테이블에 지정된 행이 먼저 삭제됨을 지정합니다. 그런 다음, 종속 행이 삭제됩니다. 

예를 들어, 부서 테이블에서 해당 행을 삭제하여 부서를 삭제할 수 있습니다. 부서 테이블에서 행을 삭제하면 다음도 삭제됩니다.

• 해당 부서에 보고하는 모든 부서에 대한 행
• 해당 부서 등에 보고하는 모든 부서

DELETE SET NULL각 종속 행에서 외부 키의 널 입력 가능한 각 열이 디폴트 값으로 설정됨을 지정합니다. 이는 삭제 중인 행을 참조하는 외부 키의 멤버인 경우에만 열이 디폴트 값으로 설정됨을 의미합니다. 직계 종속자인 종속 행만 영향을 받습니다. 

 

[제약조건 관련문제]

테이블 생성시 칼럼별 생성할 수 있는 제약 조건에 대한 설명

UNIQUE: 테이블 내에서 중복되는 값이 없지만 NULL 입력 가능하다.
PK: UNIQUE & NOT NULL, 주키로 테이블당 1개만 생성 가능
FK: 외래키로 테이블당 여러개 생성이 가능하다
NOT NULL: 명시적으로 NULL 입력을 방지

check 제약조건(constrain)는 데이터베이스에서 데이터의 무결성을 유지하기 위하여 테이블의 특정 컬럼(culumn)에 설정하는 제약이다.

기본키(primary key)는 반드시 테이블 당 하나의 제약만을 정의할 수 있다.

외래키는 테이블간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키가 참조하도록 생성한다

고유키로 지정된 모든 컬럼들은 null 값을 가질 수 있다.

 

[테이블 생성 및 구조 관련문제]

EMP

EMP_NO:VARCHAR2(10) NOT NULL (PK)
EMP(NM):VARCHAR2(30) NOT NULL
DEPT_CODE:VARCHAR2(4)NOT NULL
JOIN_DATE:DATE NOT NULL
REGIST_DATE:DATE NULL

위와 같은 테이블 구조를 정의하려고 한다. 이때 아직 부서가 정의되지 않은 사원은 기본부서(코드:0000)로 배치하고, 입사일자(JOIN_DATE) 기준으로 많은 조회가 발생하므로 입사일자에 Index를 생성하려고 할때 올바른 SQL문장 작성

CREATE TABLE EMP(
EMP_NO VARCHAR2(10) PRIMARY KEY,
EMP_NM VARCHAR2(30) NOT NULL,
DEPT_CODE VARCHAR2(4) DEFAULT '0000' NOT NULL,
JOIN_DATE DATE NOT NULL
REGIST_DATE DATE NULL);

CREATE INDEX IDX_EMP_01 ON EMP(JOIN_DATE);
=======================================================

CREATE TABLE EMP(
EMP_NO VARCHAR2(10),
EMP_NM VARCHAR2(30) NOT NULL,
DEPT_CODE VARCHAR2(4) DEFAULT '0000' NOT NULL,
JOIN_DATE DATE NOT NULL
REGIST_DATE DATE NULL);

ALTER TABLE EMP ADD CONSTAINT EMP_PK PRIMARY KEY(EMP_NO);

CREATE INDEX IDX_EMP_01 ON EMP(JOIN_DATE);

=======================================================
CREATE TABLE EMP(
EMP_NO VARCHAR2(10),
EMP_NM VARCHAR2(30) NOT NULL,
DEPT_CODE VARCHAR2(4) DEFAULT '0000' NOT NULL,
JOIN_DATE DATE NOT NULL
REGIST_DATE DATE NULL,
CONSTRAINT EMP_PK PRIMARY KEY(EMP_NO));

CREATE INDEX IDX_EMP_01 ON EMP(JOIN_DATE);

 

[테이블 삭제 관련문제]

우리가 관리하는 데이터베이스의 "매출" 테이블이 너무나 많은 디스크용량을 차지하여 "매출" 테이블에서 필요한 데이터만을 추출하여 별도의 테이블로 옮겨 놓았다. 이후 원본 테이블의 데이터를 모두 삭제함과 동시에 디스크 사용량도 초기화 하고자 한다.( 단 "매출" 테이블의 스키마 정의는 유지한다)

>>TRUNCATE TABLE 매출
TRUNCATE TABLE은 테이블 자체가 삭제되는 것이 아니고 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 
테이블 구조를 완정히 삭제하기 위해서는 DROP TABLE을 실행하면 된다.

특정 테이블의 모든 데이터를 삭제하고 디스크 사용량을 초기화 하기 위해서는 TRUNCATE를 사용한다.
DELETE TABLE은 테이블의 모든 데이터를 삭제하지만, 디스크 사용량을 초기화 하지는 않는다.
DROP TABLE은 테이블의 데이터를 모두 삭제하고, 디스크 사용량도 초기화할 수 있지만, 테이블의 스키마 정의도 함께 삭제된다.
DELETE TABLE FROM은 존재하지 않는 명령어이다.

 

다음중 DELETE와 TRUNCATE, DROP 명령어에 대해 비교한 설명

DROP 명령어는 테이블 정의 자체를 삭제하고, TRUNCATE 명령어는 테이블을 초기 상태로 만든다.

TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제시 DELETE보다 빠르다.

DROP, TRUNCATE 는 DDL의 성격을 가지므로 모두 Auto commit 된다.

DROP, TRUNCATE 는 rollback이 불가능하다.

 

출처 : http://www.bysql.net