데이터베이스 SQL문 DDL

 

테이블 생성 규칙

 테이블명 : 객체를 의미할 수 있는 이름으로 단수형 권고, 다른 테이블명과 중복X

 컬럼명 : 한 테이블 내에서 컬럼명 중복X, 테이블 생성시 각 컬럼들은 괄호 내에서 콤마로 구분됨,

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

 테이블명 & 컬럼명

사전에 정의된 예약어는 사용 불가,

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

테이블명과 컬럼명은 반드시 문자로 시작해야함

제약조건명 : 다른 제약조건명과 중복X

 

Oracle의 주요 데이터 타입

CHAR : 고정 문자열, 나머지 공간에 공백이 채워진다. ('AA' = 'AA  ')

VARCHAR2 : 가변 길이 문자열, 나머지 공간 사용X ('AA != 'AA  ')

NUMBER : 정수, 실수 등의 숫자정보이다. 

전체 자리수와 소수 부분 자리수를 명시한다. NUMBER(8,2) : 정수부분 6자리, 소수부분 2자리

DATE : 날짜와 시각 정보

 

제약조건

PRIMARY KEY : 기본키를 정의한다. (하나의 테이블에 하나의 기본키 제약만 정의 가능), NOT NULL & UNIQUE

FOREIGN KEY : 다른 테이블의 기본키를 참조하는 외래키를 지정한다.

NOT NULL : NULL 값의 입력을 허용하지 않는 제약조건이다.

UNIQUE : 해당 칼럼의 값이 테이블내에서 유일해야함을 제약하는 조건이다.

CHECK : 입력할 수 있는 값을 제한하는 조건이다.

 

ddl7주차 7페이지ppt 식별비식별 한번 더 확인하고 공부해보자

 

테이블 생성 SQL문

-- STADIUM TABLE
CREATE TABLE STADIUM (

STADIUM_ID		CHAR(3) NOT NULL,
STADIUM_NAME		VARCHAR2(40) NOT NULL,
HOMETEAM_ID		CHAR(3),
SEAT_COUNT		NUMBER,
ADDRESS			VARCHAR2(60),
DDD			VARCHAR2(3),
TEL			VARCHAR2(10),
CONSTRAINT		STADIUM_PK PRIMARY KEY(STADIUM_ID)
);

여기서 STADIUM_PK는 제약식의 이름이고,

PRIMARY KEY(STADIUM_ID)는 STADIUM_ID를 기본키로 한다는 제약조건을 준 것이다.

 

-- TEAM TABLE
CREATE TABLE TEAM (
--
--
CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID),
-- STADIUM 테이블이 먼저 생성되어 있어야 한다.
CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)
);

TEAM_FK가 제약명이고, STADIUM 테이블의 STADIUM_ID를 참조하는 외래키를 지정한 것이다.

 

CREATE TABLE PLAYER (
--
--
CONSTRAINT PLAYER_PK PRIMARY_KEY (PLAYER_ID),
-- TEAM 테이블이 먼저 생성되어 있어야 함
CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)
);

 

CREATE TABLE SCHEDULE (
--
--
CONSTRAINT SCHEDULE_PK PRIMARY KEY (STADIUM_ID, SCHE_DATE),
-- STADIUM 테이블이 먼저 생성되어 있어야 함
CONSTRAINT SCHEDULE_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)
);

 

제약 조건의 지정

-- CASCADE CONSTRAINTS 는 부모 TABLE을 삭제하고
-- 자식 TABLE의 FOREIGN KEY CONSTRAINT도 삭제한다. 
DROP TABLE PLAYER1 CASCADE CONSTRAINT;

CREATE TABLE PLAYER1 (
	PLAYER_ID CHAR(7) PRIMARY KEY,
    PLAYER_NAME VARCHAR2(20) NOT NULL,
    NICKNAME VARCHAR2(30) UNIQUE,
    HEIGHT NUMBER(3) CHECK (HEIGHT >= 140 AND HEIGHT <= 200),
    TEAM_ID CHAR(3) REFERENCES TEAM(TEAM_ID)
);

DROP TABLE PLAYER2 CASCADE CONSTRAINT;

CREATE TABLE PLAYER2 (
	PLAYER_ID CHAR(7) CONSTRAINT p2_pk_id PRIMARY KEY,
    PLAYER_NAME VARCHAR2(20) CONSTRAINT p2_nn_name NOT NULL,
    --
    --
    --
);

DROP TABLE PLAYER3 CASCADE CONSTRAINT;

CREATE TABLE PLAYER3 (
	PLAYER_ID CHAR(7),
    PLAYER_NAME VARCHAR2(20) CONSTARINT p3_nn_name NOT NULL,
    NICKNAME VARCHAR2(30),
    HEIGHT NUMBER(3),
    TEAM_ID CHAR(3),
    
    CONSTRAINT p3_pk_id PRIMARY KEY (PLAYER_ID),
    CONSTRAINT p3_un_nick UNIQUE (NICKNAME),
    CONSTRAINT p3_ck_height CHECK (HEIGHT >= 140 AND HEIGHT <= 200),
    CONSTRAINT p3_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 FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID)
ON DELETE CASCADE ON UPDATE RESTRICT;
-- ON DELETE CASCADE : 기본키 삭제되면 해당 값을 외래키로 갖는 레코드도 삭제
-- ON UPDATE RESTRICT : 기본값의 갱신을 불허

ON UPDATE (RESTRICT) , ON UPDATE (RESTRICT)

RESTRICT는 디폴트 값이다.

NO ACTION은 RESTRICT와 동일하게 동작한다.

-- ON DELETE CASCADE : 기본키가 삭제되면 해당 값을 외래키로 갖는 레코드도 삭제
-- ON UPDATE CASCADE : 기본키가 갱신되면 이를 참조하는 외래키를 새로운 값으로 업데이트
-- ON DELETE SET NULL
-- ON UPDATE SET NULL
: 기본키가 삭제 또는 갱신되면 이를 참조하는 외래키를 NULL로 업데이트

 

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

DROP TABLE PLAYER_TEMP CASCADE CONSTRAINT;

CREATE TABLE PLAYER_TEMP AS SELECT * FROM PLAYER;

-- 생성한 테이블 제약조건 확인해보기
SELECT * FROM ALL_CONSTRAINTS
WHERE TABLE_NAME IN ('PLAYER', 'PLAYER_TEMP') ORDER BY TABLE_NAME;

제약 조건은 NOT NULL 만 복제된다.

따라서 PK, FK, UNIQUE, CHECK 등은 수동으로 추가해주어야 한다.

 

ALTER 문을 통한 테이블 변경

-- 컬럼의 추가 (ADD)
ALTER TABLE PLAYER_TEMP ADD (ADDRESS VARCHAR2(80));

-- 컬럼의 삭제 (DROP COLUMN)
-- 삭제 후 최소 하나 이상의 컬럼이 테이블에 존재해야 한다.
ALTER TABLE PLAYER_TEMP DROP COLUMN ADDRESS;

-- 컬럼명 변경 (RENAME COLUMN)
ALTER TABLE PLAYER_TEMP RENAME COLUMN PLAYER_ID TO PLAYER_NEW_ID;

-- 컬럼의 정의 수정 (MODIFY)
ALTER TABLE PLAYER_TEMP MODIFY (PLAYER_NAME NOT NULL); -- PLAYER_NAME 속성 NOT NULL로 변경
ALTER TABLE PLAYER_TEMP MODIFY (PLAYER_NAME NULL); -- NOT NULL 제약 조건 삭제됨

-- 제약 조건의 추가/삭제
ALTER TABLE PLAYER_TEMP ADD CONSTRAINT PLAYER_TEMP_FK
FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

ALTER TABLE PLAYER_TEMP DROP CONSTRAINT PLAYER_TEMP_FK; -- 제약조건 삭제

-- RENAME 문을 통한 테이블 명칭 변경
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'; -- 테이블 오브젝트 조회
RENAME PLAYER_TEMP TO PLAYER_NEW_TEMP; -- 테이블 명칭 변경

칼럼 정의 수정

- 데이터 타입 변경은 테이블에 아무 행도 없거나 해당 컬럼이 NULL만 갖고 있을 때 가능하다.

- 컬럼 크기 확대는 항상 가능하지만, 컬럼 크기 축소는 테이블에 아무 행도 없거나 해당 컬림이 NULL만 갖고 있거나,

현재 저장된 값을 수용할 수 있는 크기로의 축소만 가능하다.

- DEFAULT 값 추가 및 수정은 이후 삽입되는 행에만 영향을 미친다.

- NOT NULL 제약 조건 추가는 테이블에 아무 행도 없거나 해당 컬럼에 NULL이 없을 때 가능하다.

- NOT NULL 제약 조건 삭제는 항상 가능하다.

 

 

 

 

DROP 문을 통한 테이블 삭제

DROP TABLE TEAM;
DROP TABLE TEAM CASCADE CONSTRAINT; -- 관련된 제약조건을 함께 삭제

 

문제

-- PLAYER_TEMP 테이블에서 PLAYER_NEW_ID를 PK로 지정하라
ALTER TABLE PLAYER_TEMP ADD CONSTARINT player_id_pk PRIMARY KEY (PLAYER_NEW_ID);

-- PLAYER_TEMP 테이블에서 TEAM 테이블의 TEAM_ID를 참조하는 제약조건명이 PLAYER_TEMP_FK 인 컬럼 TEAM_ID를 생성하라
ALTER TABLE PLAYER_TEMP ADD CONSTRAINT PLAYER_TEMP_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);