[SQL 기본과 활용] DDL

테이블 생성

• 테이블 생성 규직

      테이블명

         객체를 의미할 수 있는 이름으로, 단수형을 권고함

         다른 테이블의 이름과 중복되지 않아야 함

 

      칼럼명

         한 테이블 내에서는 칼럼명이 중복되지 않아야 함(다른 테이블과는 중복되어도 에러가 발생하지 않음)

         테이블 생성시 각 칼럼들은 괄호 내에서 콤마로 구분됨

         칼럼 뒤에 데이터 유형이 반드시 지정되어야 함

 

      테이블명 & 칼럼명

         사전에 정의된 예약어(Reserved word)는 사용불가

         ex) SELECT, FROM WHERE 같은 것

         테이블명과 칼럼명에는 문자, 숫자, 일부 기호( _ $ # )만 허용됨

         테이블명과 칼럼명은 반드시 문자로 시작해야 함( 숫자 기호 불가)

 

      제약 조건명 : 다른 제약 조건의 이름과 중복되지 않아야 함

 

• Oracle의 주요 데이터 타입

Type	설명
CHAR	- 고정 문자열 ( 기본 1Byte ~ 최대 2,000Byte) " 공간이 고정 "  - 변수에 할당된 값이 고정 길이보다 작은 경우, 나머니 공간에 공백이 채워짐    (참고 : 'AA' = 'AA  ' )
VARCHAR2	- 가변 길이 문자열 ( 기본 1Byte ~ 최대 4,000Byte)  - 변수에 할당된 값이 고정 길이보다 작은 경우, 나머지 공간은 사용하지 않음    (참고 : 'AA' <> 'AA  ' )
NUMBER	- 정수, 실수 등의 숫자 정보  - MS SQL Server의 경우 10가지 이상의 숫자 타입 지원 -  전체 자리수와 소수 부분 자리수를 명시함    예 : NUMBER(8,2) -> 정수부분 6자리, 소수부분 2자리
DATE	- 날짜와 시각 정보

*CHAR, VARCHAR2 비교, NUMBER(전체자리수, 소수자리수) 주의!

 

• 제약조건(Constraints)

구분	설명
PRIMARY KEY (기본키)	- 기본키를 정의함 ( 하나의 테이블에 하나의 기본키 제약만 정의 가능)  - NOT NULL & UNIQUE
FOREIGN KEY (외래키)	- 다른 테이블의 기본키를 참조하는 외래키를 지정함  - 외래키 지정시 참조 무결성 제약 옵션 선택
NOT NULL	- NULL 값의 입력을 허용하지 않는 제약조건
UNIQUE	- 해당 칼럼의 값이 테이블 내에서 유일해야 함을 제약하는 조건
CHECK	- 입력할 수 있는 값을 제한하는 조건    예 : CONSTRAINT BACK_NO_CK CHECK ( BACK_NO < 99 )

*NOT NULL, UNIQUE는 기본키이기 위한 필요조건.(충분조건 아님)

*참조 무결성 제약조건(FK 때문에 수정이나 삭제를 못하는 경우)

*FK → 테이블 생성 순서가 중요함

 

 - 제약 조건명을 명시적으로 부여할 수도 있고, 묵시적으로 제약 조건명 없이 제약 조건을 설정할 수도 있음.

 

 

PLAYER 테이블 구조

 

• 테이블 생성 SQL문 - STADIUM

 

CREATE TABLE STADIUM(

STADIUM_ID CHAR(3) NOT NULL '칼럼이름' '데이터타입' '제약조건'

STADIUM_NAME VARCHAR2(40) NOT NULL

.

.

.

CONSTRAINT STADIUM_PK PRIMARY KEY ( STADIUM_ID)

CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)

*STADIUM_ID를 TEAM_FK라는 FK로 만들건데 그건 STADIUM테이블의 STADIUM_ID에서 가져온거라는 뜻

);

 

• 제약조건 지정

DROP TABLE PLAYER1 CASCADE CONSTRAINT

*PLAYER1테이블을 삭제하는데 관련 제약 조건도 삭제하라는 뜻

 

 - 방법1(묵시적 방법)

PLAYER_ID CHAR(7) PRIMARY KEY,

PLAYER_NAME VARCHAR2(20) NOT NULL,

NICKNAME VARCHAR2(30) UNIQUE,

HEIGHT NUMBER(3) CHECK (HEIGHT ≥ 150 AND HEIGHT ≤200),

TEAM_ID CHAR(3) REFERENCES TEAM(TEAM_ID)

 

 - 방법2(명시적 방법)

PLAYER_ID CHAR(7) CONSTRAINT p2_pk_id(제약조건의 이름) PRIMARY KEY,

PLAYER_NAME VARCHAR2(20) CONSTRAINT p2_mm_name NOT NULL,

NICKNAME VARCHAR2(30) CONSTRAINT p2_un_nick UNIQUE,

HEIGHT NUMBER(3) CONSTRAINT p2_ck_height CHECK (HEIGHT ≥ 150 AND HEIGHT ≤200),

TEAM_ID CHAR(3) CONSTRAINT p2_fk_tid REFERENCES TEAM(TEAM_ID;

 

 - 방법3

PLAYER_ID CHAR(7)

PLAYER_NAME VARCHAR2(20) NOT NULL,

NICKNAME VARCHAR2(30)

HEIGHT NUMBER(3)

 

CONSTRAINT p2_pk_id(제약조건의 이름) PRIMARY KEY (PLAYER_ID),

CONSTRAINT p2_un_nick UNIQUE (NICKNAME),

CONSTRAINT p2_ck_height CHECK (HEIGHT ≥ 150 AND HEIGHT ≤200),

CONSTRAINT p2_fk_tid FOREIGN KEY (TEAM_ID)REFERENCES TEAM(TEAM_ID)

 

 

SELECT * FROM ALL_CONSTRAINTS

WHERE TABLE_NAME IN ('PLAYER1', 'PLAYER2', 'PLAYER3')

ORDER BY CONSTRAINT_NAME;

*제약 조건 확인

 

 

• FK 제약 조건의 옵션
  예) CONSTRAINT fk1 ROREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)
       ON DELETE CASCADE ON UPDATE RESTRICT;(옵션)

 - Referential Triggered Action

ON UPDATE, ON DELETE

 

 - Referential Action

RESTRICT(Default) : 기본값의 삭제 또는 갱신을 불허

NO ACTION : RESTRICT와 동일하게 동작(명시해주는 역할)

CASCADE:

기본키가 삭제되면 해당 값을 외래키로 갖는 레코드도 삭제

기본키가 갱신되면 이를 참조하는 외래키를 새로운 값으로 업데이트

SET NULL

기본키가 삭제 또는 갱신되면 이를 참조하는 외래키를 NULL로 업데이트

 

기존 테이블을 활용한 테이블 생성 ( SELECT문 활용)

 - 제약 조건은 NOT NULL만 복제됨

   PK, FK, UNIQUE, CHECK 등은 수동으로 추가해야 함

   CREATE TABLE PLAYER_TEMP

   AS SELECT * FROM PLAYER;

   PLAYER테이블에서 가져온거를 PLAYER_TEMP 테이블로 만들겠다는 의미

테이블 변경

• ALTER 문을 통한 테이블 변경

칼럼의 추가/삭제/수정, 제약조건의 추가/삭제

   (1) 칼럼의 추가(ADD)

      새로 추가한 칼럼은 테이블의 맨 마지막에 추가됨

      ex)

      ALTER TABLE 테이블이름 ADD (칼럼이름, 데이터타입);

 

   (2) 칼럼의 삭제(DROP COLUMN)

      삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 함

      ex)

      ALTER TABLE 테이블이름 DROP COLUMN 칼럼이름;

 

   (3) 칼럼명 변경(RENAME COLUMN)

      해당 칼럼의 모든 정의가 그대로 유지가 됨

      ex)

      ALTER TABLE 테이블이름 RENAME COLUMN 칼럼이름 TO 새 이름;

 

   (4) 칼럼의 정의 수정(MODIFY)

"이미 입력되어 있는 값에 영향을 미치는 변경은 허용하지 않음"

      데이터 타입 변경

        테이블에 아무 행도 없거나, 해당 칼럼이 NULL만 갖고 있을 때 가능

      칼럼의 크기 변경

        칼럼의 크기 확대 → 항상 가능

        칼럼의 크기 축소 → 테이블에 아무 행도 없거나, 해당 칼럼이 NULL만 갖고 있거나, 현재 저장된 값을 수용할 수          있는 크기로의 축소만 가능

      DEFAULT 값 추가 및 수정

        추가 수정 이후 삽입되는 행에만 영향을 미침

      NOT NULL 제약조건 추가

        테이블에 아무 행도 없거나, 해당 칼럼에 NULL이 없을 때 가능

      NOT NULL 제약 조건 삭제 → 항상 가능

        ALTER TABLE 테이블명 MODIFY (속성명 NOT NULL / NULL)

 

   (5) 제약 조건의 추가/삭제(ADD/DROP CONSTRAINT)

      테이블 생성 이후에도 제약 조건을 추가/삭제할 수 있음

         ALTER TABLE 테이블명 ADD CONSTRAINT 제약이름 제약조건 참조

         ALTER TABLE 테이블명 DROP CONSTRAINT 제약이름

 

• RENAME 문을 통한 테이블 명칭 변경

RENAME 기존테이블명 TO 새테이블명

 

SELECT * FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'TABLE';

 

 

• DROP 문을 활용한 테이블 삭제

DROP TABLE 테이블명;

      테이블 삭제할 때 다른 테이블에 영향을 준다면(제약조건 때문에)

      CASCADE CONSTRAINT; (조건문 추가)