D31 DDL
20241106 D31
DDL (DATA DEFINITION LANGUAGE) 데이터 정의 언어
- 오라클에서 제공하는 객체들을 새롭게 만들고, 구조를 변경/삭제 하는 명령문들
- 객체의 구조 자체를 정의 하는 언어로 DB관리자, 설계자가 사용하는 언어.
- 객체 생성 문법 : CREATE
- 객체 수정 문법 : ALTER
- 객체 삭제 문법 : DROP
- 오라클에서 제공하는 객체들
- 테이블(TABLE), 사용자(USER), 함수(FUNCTION), 뷰(VIEW), 시퀸스, 인덱스, 패키지, 트리거...
<CREATE TABLE>
- 테이블 : 행과 열로 구성되는 데이터 베이상의 가장 기본적인 객체 중 하나.
- 데이터베이스 상에 기록하고자 하는 데이터들은 모두 테이블을 통해 저장 및 관리.
[표현법] CREATE TABLE 테이블명 ( 컬럼명 자료형, 컬럼명 자료형, ... );
<데이터베이스의 자료형>
- 문자 자료형 (CHAR(크기) / VARCHAR2(크기)) = 크기의 기본값은 byte
- 숫자, 영문자 -> 1BYTE, 한글/한자 => 3BYTE
- 글자수 단위로 크기 지정하는 경우 뒤에 CHAR를 붙인다. (3CHAR)
1) CHAR( BYTE/CHAR ) = 최대 2000BYTE까지 저장 가능 고정길이
내가 지정한 크기보다 적은 값이 입력되도 공백으로 나머지 빈 값을 채워 처음 지정 크기 유지
주로 들어온 값 글자수가 정해져 있을 경우 사용2) VARCHAR2 ( BYTE/CHAR ) = 최대 4000byte 까지 저장 가변길이
작은 값이 압력 되면 담긴 값의 크기에 맞춰 크기가 조절
VAR는 '가변', 2는 '2배'를 의미 주로 입력 값의 글자수가 정해지지 않은 경우 사용1). NUMBER(P , S)
- 정수/실수 상관없이 NUMBER로 저장, 매개변수 생략 가능, 소숫점 자릿수 지정(S) 가능.
2). DATE
- 세기/년/월/일/시/분/초 단위로 데이터를 보관한다.
- 기본적으로 화면에는 년/월/일 정보만 표기
- DATE자료형과 NUMBER 자료형간의 연산이 가능
-- 회원들의 정보를 보관할 테이블(아이디, 비밀번호, 이름, 생년월일)
CREATE TABLE MEMBER (
MEMBER_ID VARCHAR2(20),
MEMBER_PWD VARCHAR2(20),
MEMBER_NAME VARCHAR2(20),
MEMBER_DATE DATE);
SELECT *
FROM MEMBER;
-- 데이터 딕셔너리 (Data Dictionary)
: 다양한 객체들의 정보를 저장하고 있는 시스템 테이블.
SELECT * -- 테이블 확인
FROM USER_TABLES; -- USER_ 객체명칼럼에 주석달기
[표현법] COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '회원비번';
-- 코멘트 추가 완료
INSERT INTO MEMBER VALUES( 'USER01', 'PASS01', '경민1', '1993-02-18');
INSERT INTO MEMBER VALUES( 'USER02', 'PASS02', '경민2', '93/02.18');
INSERT INTO MEMBER VALUES( NULL, NULL, NULL, '93/02.18');
INSERT INTO MEMBER VALUES( 'USER02', 'PASS02', '경민2', '93/02.18');
-- 제약이 없기 때문에 NULL값이나 같은 계정명이 등록이 되고 있다.
SELECT * FROM MEMBER; 입력확인칼럼에 반드시 값이 들어가거나, 중복된 값을 추가하지 않게 제약을 걸고 싶다면 제약조건을 지정해야 한다
<제약조건 CONSTRAINTS>
- 원하는 데이터 값을 유지하기 위한 특정 "칼럼"마다 설정하는 제약
- 제약조건이 부여된 칼럼에 입력할 데이터에 문제가 있는지 검사할 수 있다
- 제약조건을 부여함으로써 데이터의 무결성을 유지 할 수 있다.
제약조건의 종류
- NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY제약조건을 부여방식
- 컬럼 레벨 방식 , 테이블 레벨 방식이 존재함.1. NOT NULL 제약조건
칼럼에 반드시 값이 "존재" 해야 할 경우 사용 즉, NULL값이 절대 들어와서는 안되는 칼럼에 부여하는 제약조건 값이 추가/수정 될 때도 NULL값이 들어 올 수 없다. **주의사항 : 제약조건 부여시 칼럼레벨방식으로만 부여가 가능.
-- NOT NULL 제약 조건 부여 테이블
-- 칼럼레벨방식 : 칼럼명 자료형 제약조건
CREATE TABLE MEM_NOTNULL (
MEMBER_NO NUMBER NOT NULL,
MEMBER_ID VARCHAR2(20) NOT NULL,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30)
);
INSERT INTO MEM_NOTNULL VALUES( 1, 'USER01', 'PASS01', '윤휘명', '남', '010-3663-3062', 'YHCACTUS@NAVER.COM');
SELECT *
FROM MEM_NOTNULL; -- 생성확인
INSERT INTO MEM_NOTNULL VALUES( NULL, NULL, NULL, NULL, '남', '010-3663-3062', 'YHCACTUS@NAVER.COM');
-- NULL 제한을 했기 떄문제 입력을 할수 없다 '제약조건위배'
INSERT INTO MEM_NOTNULL VALUES( 1, 'USER01', 'PASS01', '윤휘명', NULL, NULL, NULL);
INSERT INTO MEM_NOTNULL VALUES( 4, 'USER04', 'PASS04', '윤휘명', ' ', ' ', ' ');
-- ' '은 NULL값으로 간주 한다
SELECT *
FROM MEM_NOTNULL;
WHERE = ' '; -- 당연히 오류
-- 이런식으로 출력할수는 없다 단지 입력 할때에 입력되는 규칙일 뿐이다2. UNIQUE 제약조건
- 컬럼에 중복값을 제한하는 조건(제약조건)
- 삽입 / 수정시 기존에 해당 칼럼값 중에 중복값이 있을 경우 추가 또는 수정이 되지 않제 제약한다.
CREATE TABLE MEM_UNIQUE (
MEMBER_NO NUMBER NOT NULL UNIQUE, --칼럼레벨방식으로 여러 제약조건 부여함
MEMBER_ID VARCHAR2(20) NOT NULL UNIQUE,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30)
);
DROP TABLE MEM_UNIQUE; -- 테이블삭제~
--테이블 레벨 방식 제약조건 부여
CREATE TABLE MEM_UNIQUE (
MEMBER_NO NUMBER NOT NULL ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
--모든 칼럼을 기술하고 가장 마지막에 제약조건을 나열
UNIQUE (MEMBER_NO) , UNIQUE(MEMBER_ID) -- 개별적인 부여가 필요하다
);
INSERT INTO MEM_UNIQUE
VALUES ( 1, 'USER01' , 'PASS01' , '홍길동', '남', '010-1111-2222', 'ABB@NAVER.COM');
INSERT INTO MEM_UNIQUE
VALUES ( 2, 'USER02' , 'PASS01' , '홍길동', '남', '010-1111-2222', 'ABB@NAVER.COM');
INSERT INTO MEM_UNIQUE
VALUES ( 3, 'USER02' , 'PASS01' , '홍길동', '남', '010-1111-2222', 'ABB@NAVER.COM');
-- UNIQUE 제약조건에 위배되었으므로 INSERT실패
-- 오류내용으로 어느칼럼에 문제가 있는지 알려주지 않으나,
-- 제약조건의 이름을 통해 어떤제약조건에서 발생한 에러인지 알 수 있다.*제약조건 부여시 제약조건명 지정 방법
1). 칼럼레벨방식 : 칼럼명 자료형 CONSTRAINT 제약조건명 제약조건
2). 테이블레벨 방식 : 칼럼명, CONSTRAINT 제약조건명 제약조건(컬럼명)**주의사항 : 제약조건명이 항상 고유한 값을 가지도록 작성
CREATE TABLE MEM_CON_NM (
MEMBER_NO NUMBER NOT NULL ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) CONSTRAINT MEM_MN_NN NOT NULL, --칼럼레벨방식
GENDER CHAR(3),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
--테이블레벨방식 -- 개별적인 부여가 필요하다
CONSTRAINT MEMBER_MEMBER_NO_UNIQUE UNIQUE (MEMBER_NO) , UNIQUE(MEMBER_ID)
);
-- 어떤 테이블에 어떤 칼럼에 어떤 제약조건을 위배했는지 한눈에 알 수 있도록 조합하여 이름짓기.3. CHECK 제약조건
- 컬럼에 기록될 수 있는 값에 대한 조건을 설정할 수 있는 제약조건.
--CHECK (조건식)
CREATE TABLE MEM_CHECK (
MEMBER_NO NUMBER NOT NULL ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3) CHECK( GENDER IN ('남', '여') ) ,
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE NOT NULL ,
UNIQUE (MEMBER_NO) , UNIQUE(MEMBER_ID)
);
INSERT INTO MEM_CHECK
VALUES(1, 'USER01', 'PASS01', '홍길동', '남', '010-1111-2222', ' ', SYSDATE);
INSERT INTO MEM_CHECK
VALUES(2, 'USER02', 'PASS01', '홍길동', NULL, '010-1111-2222', ' ', SYSDATE);
-- NULL값 추가 가능, NULL 값이 못 들어오게 하려면 NOT NULL제약 조건 추가DEFAULT 설정
- 제약조건은 아니다 특정 칼럼에 들어 올 값에 대한 기본값 설정 가능.
CREATE TABLE MEM_CHECK_DEFAULT (
MEMBER_NO NUMBER NOT NULL ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3) CHECK( GENDER IN ('남', '여') ) ,
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL ,
-- DEFAULT SYSDATE : 제약 조건보다 뒤에있으면 에러 발생
UNIQUE (MEMBER_NO) , UNIQUE(MEMBER_ID)
);
INSERT INTO MEM_CHECK_DEFAULT(MEMBER_NO, MEMBER_ID, MEMBER_PWD, MEMBER_NAME)
VALUES (1, 'USER01', 'PASS01', '김갑생');4. PRIMARY KEY (기본키) 제약조건
- 테이블에서 각 행들의 정보를 '유일하게 식별'할 수 있는 칼럼에 부여하는 제약 조건
=> 각 행들을 구분할 수 있는 식별자의 역할
EX) 사번, 부서아이디 , 직급코드 등등...
= 식별자의 조건 : 중복값 X, 값이 없으면 안된다.
주의사항 : 한 테이블에 한개의 컬럼만 기본키로 설정가능
-- PRIMARY KEY => NOT NULL + UNIQUE
CREATE TABLE MEM_PRIMARY1 (
MEMBER_NO NUMBER PRIMARY KEY ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3) CHECK( GENDER IN ('남', '여') ) ,
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL ,
UNIQUE(MEMBER_ID)
);
CREATE TABLE MEM_PRIMARY2 (
MEMBER_NO NUMBER ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3) CHECK( GENDER IN ('남', '여') ) ,
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL ,
UNIQUE(MEMBER_ID),
--CONSTRAINT MEM_NO_PK PRIMARY KEY ( MEMBER_NO )
--복합키
--테이블 레벨 방식으로만 지정 가능.
CONSTRAINT MEM_NO_PK PRIMARY KEY(MEMBER_NO, MEMBER_ID)
);
DROP TABLE MEM_PRIMARY2;
INSERT INTO MEM_PRIMARY2 (MEMBER_NO, MEMBER_ID, MEMBER_PWD, MEMBER_NAME)
VALUES (1, 'USER01', 'ZZ', '경민');
INSERT INTO MEM_PRIMARY2 (MEMBER_NO, MEMBER_ID, MEMBER_PWD, MEMBER_NAME)
VALUES (1, 'USER02', 'ZZ', '경민');
-- 복합키로 설정된 칼럼은 복합키에 적용된 칼럼의 값들이 모두 동일해야 중복값으로 간주(에러발생조건)
INSERT INTO MEM_PRIMARY2 (MEMBER_NO, MEMBER_ID, MEMBER_PWD, MEMBER_NAME)
VALUES (NULL, 'USER03', 'ZZ', '경민');
-- 복합키일 경우 한 컬럼이 값이 NULL이더라도 제약조건에 위배된다.5. FOREIGN KEY ( 외래키)
- 해당 칼럼에 다른 테이블에 존재하는 값만 들어와야 하는 칼럼에 부여하는 제약조건.
- 외래키 제약조건으로 설정된 칼럼은 다른 테이블과 관계를 형성 할 수 있다.
- 다른 테이블(부모테이블), 현재테이블(자식테이블)
[표현법] 1). 컬럼레벨 컬럼명 자료형 [CONSTRAINT 제약조건명] REFERENCES 참조할테이블명 [(참조할칼럼명)] 2). 테이블레벨 [CONSTRAINT 제약조건명] FOREIGN KEY(칼럼명) REFERENCES 참조할테이블명[(참조할칼럼명)]- 참조할칼럼명 생략시 참조할테이블의 PRIMARY KEY가 자동 설정.
- 주의사항 : 참조할 컬럼명의 데이터타입과, 현재 컬럼의 데이터타입이 동일
-- 부모테이블 생성
-- 회원등급에 대한 데이터 보관 테이블
CREATE TABLE MEM_GRADE (
GRADE_CODE CHAR(2) PRIMARY KEY, --등급코드 (G1, G2...)
GRADE_NAME VARCHAR2(20) NOT NULL
);
INSERT INTO MEM_GRADE VALUES( 'G1' , '일반회원');
INSERT INTO MEM_GRADE VALUES( 'G2' , '우수회원');
INSERT INTO MEM_GRADE VALUES( 'G3' , '특별회원');
CREATE TABLE MEM (
MEMBER_NO NUMBER PRIMARY KEY ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
MEMBER_GRADE CHAR(2) REFERENCES MEM_GRADE ,
GENDER CHAR(3) CHECK( GENDER IN ('남', '여') ) ,
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL ,
UNIQUE(MEMBER_ID)
--FOREIGN KEY (MEMBER_GRADE) REFERENCES MEM_GRADE (GRADE_CODE)
);
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (1, 'USER01', 'PASS01', '경민', 'G3');
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (2, 'USER02', 'PASS01', '길동', 'G2');
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (3, 'USER03', 'PASS01', '희동', 'G1');
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (4, 'USER04', 'PASS01', '희동', NULL);
-- NULL 추가 가능.
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (5, 'USER05', 'PASS01', '희동', 'G4');
-- 외래키 제약조건 위배. PARENT KEY NOY FOUND. 부모에 존재하지 않는 값 추가하려할때 발생
-- 부모 테이블의 G1칼럼이 존재하는 행을 삭제 / 수정 해야하는 경우를 가정.
DELETE FROM MEM_GRADE
WHERE GPADE_CODE = 'G1';
-- 무결성 제약조건 위배 CHILD RECODE FOUND
-- 자식테이블에서 G1값을 참조하여 사용하고 있기 떄문에 삭제 불가
-- 외래키 제약조건 부여시 부모테이블의 칼럼이 삭제되는 경우에 대한
-- '조치사항'을 추가로 기술할 수 있다.
DROP TABLE MEM; --테이블 삭제~* 자식 테이블 생성시 외래키 제약조건을 부여 할 때.
부모테이블의 테이터가 삭제되었을 경우 자식테이블에는 처리할 방벙 옵션을 정할수 있다
(단, 테이블 생성시에만 가능)* FOREIGN KEY 삭제 옵션
- ON DELETE RESTRICTED : 삭제를 제한하는 옵션(기본값)
- ON DELETE SET NULL : 부모레코드가 삭제될대 해당 데이터를 사용하는 자식 데이터의 칼럼은 NULL 바꾸는
- ON DELETE CASCADE : 부모레코드가 삭제될 때 해당 데이터를 사용하는 자식 데이터의 행을 함꼐 삭제하겠다.
CREATE TABLE MEM (
MEMBER_NO NUMBER PRIMARY KEY ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
MEMBER_GRADE CHAR(2) REFERENCES MEM_GRADE ON DELETE SET NULL,
GENDER CHAR(3) CHECK( GENDER IN ('남', '여') ) ,
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL ,
UNIQUE(MEMBER_ID)
);
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (1, 'USER01', 'PASS01', '경민', 'G3');
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (2, 'USER02', 'PASS01', '길동', 'G2');
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (3, 'USER03', 'PASS01', '희동', 'G1');
SELECT * FROM MEM;
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';
DROP TABLE MEM;
CREATE TABLE MEM (
MEMBER_NO NUMBER PRIMARY KEY ,
MEMBER_ID VARCHAR2(20) NOT NULL ,
MEMBER_PWD VARCHAR2(20) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
MEMBER_GRADE CHAR(2), --REFERENCES MEM_GRADE ON DELETE SET NULL,
GENDER CHAR(3) CHECK( GENDER IN ('남', '여') ) ,
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL ,
UNIQUE(MEMBER_ID),
FOREIGN KEY (MEMBER_GRADE) REFERENCES MEM_GRADE (GRADE_CODE) ON DELETE CASCADE
);
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (1, 'USER01', 'PASS01', '경민', 'G3');
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (2, 'USER02', 'PASS01', '길동', 'G2');
INSERT INTO MEM(MEMBER_NO, MEMBER_ID, MEMBER_PWD,MEMBER_NAME, MEMBER_GRADE)
VALUES (3, 'USER03', 'PASS01', '희동', 'G1');
SELECT * FROM MEM;
DELETE FROM MEM_GRADE WHERE GRADE_CODE = 'G1';
-- 자식테이블의 GRADE_CODE 가 G1인 형도 모두 함께 삭제.DDL
1. ALTER
- 객체의 구조를 수정하는 구문.
<테이블 구조 수정>
- ALTER TABLE 테이블명 수정내용
- 수정할 내용
1) 칼럼 추가 / 수정 / 삭제
2) 제약 조건 추가 / 삭제
3) 테이블의 이름, 컬럼의 이름 , 제약 조건의 이름 (RENAME)
-- 1) 컬럼 추가 / 수정 / 삭제
-- 1_1) 컬럼추가(ADD) : ADD 추가할 컬럼명 자료형 [DEFAULT 기본값]
-- EMP_DEPT 테이블에 CNAME 칼럼 추가
ALTER TABLE EMP_DEPT ADD CNAME VARCHAR2 (20);
-- EMP_DRPT 테이블에 LNAME 칼럼 추가 기본값으로 '한국' 부여
ALTER TABLE EMP_DEPT ADD LNAME VARCHAR2 (20) DEFAULT '한국';
-- 새로운 칼럼이 생성되면서 DEFAULT 값으로 채워진다
-- 1_2) 칼럼 수정 (MODIFY)
-- 칼럼의 자료형 수정 : MODIFY 칼럼명 바꾸고자 하는 자료형
-- 칼럼의 기본값 수정 : MODIFY 카럼명 DEFAULT 바꿀값
-- EMP_DEPT 테이블의 EMP_ID 칼럼의 자료형을 CHAR(4) 로 변경
ALTER TABLE EMP_DEPT MODIFY EMP_ID CHAR(4);
-- 에러) EMP_ID 값이 사용 중이므로 수정이 불가
--CNAME의 자료형을 DATE로 변경 (값이 비어있으므로 다른자료형으로의 변경 가능)
ALTER TABLE EMP_DEPT MODIFY CNAME DATE;
--EMP_DEPT의 EMP_NAME의 값을 VARCHAR2(1) 로 변경
ALTER TABLE EMP_DEPT MODIFY EMP_NAME VARCHAR2(1);
-- 에러) 값이 더 큰범위에서는 작은범위로 변경이 불가능
-- 기존에 담겨있는 값에서 가장 큰 값에 크기에 아래의 바이트로는 변경 불가능
UPDATE EMP_DEPT SET EMP_NAME = NULL;문자 -> 날짜 (0) ? 칼럼에 값이 하나도 없을 경우
문자 -> 날짜 (X) ? 칼럼에 문자열이 이미 들어간 경우
문자 (20) -> 문자(1) (X) ? 칼럼에 1바이트보다 큰값이 있는경우
문자 (20) -> 문자(1) (O) ? 칼럼에 1바이트보다 큰값이 하나도 없는경우
--EMP_DEPT 에서 DEPT_TITLE 칼럼의 데이터 타입을 CARCHAR2(40) 으로
-- CNANE 컬럼의 데이터 타입은 VARCHAR2(2)
-- LNAME 의 기본값을 '미국' 으로 변경
ALTER TABLE EMP_DEPT
MODIFY DEPT_TITLE VARCHAR2(40)
MODIFY CNAME CNAME VARCHAR2(2)
MODIFY LNAME DEFAULT '미국';
UPDATE EMP_DEPT SET LNAME = '한국';
-- 1_3) 칼럼 삭제 ( DROP COLUMN) : DROP COLUMN 삭제할 칼럼명
CREATE TABLE EMP_DEPT2
AS SELECT * FROM EMP_DEPT;
-- EMP_DEPT2 로부터 EMP_ID 값을 지우기
ALTER TABLE EMP_DEPT2 DROP COLUMN EMP_ID;
SELECT * FROM EMP_DEPT2;
ROLLBACK; -- 롤백 불가
2_2) 제약조건 추가 / 삭제
- PRIMARY KEY : ADD PRIMATY KEY(칼럼명);
- FOREIGN KET : ADD FOREIGN (칼럼명) REFERENCES 참조할테이블명[(칼럼명)] [옵션]
- UNIQUE : ADD UNIQUE(칼럼명)
- CHECK : ADD CHECK(칼럼에 대한 조건식);
- NOT NULL : MODIFY 컬럼명 NOT NULL; <<- 독특한 친구
[CONSTRAINT 제약조건명 ] 는 생략이 가능
DELETE FROM EMP_DEPT;
INSERT INTO EMP_DEPT
(
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NULL, '미국'
FROM (SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NULL, NULL,NULL , ROW_NUMBER() OVER(PARTITION BY DEPT_CODE ORDER BY DEPT_CODE) RN
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE
)
WHERE RN = 1
);
-- EMP_DEPT 테이블에 EMP_ID에는 PRIMARY KEY 제약조건을,
-- DEPT_TITLE 에는 UNIQUE 제약조건을 LNAME 에는 NOT NULL 제약 조건을 추가
ALTER TABLE EMP_DEPT ADD CONSTRAINT EMP_DEPT_ID_PK PRIMARY KEY(EMP_ID);
ALTER TABLE EMP_DEPT ADD CONSTRAINT EMP_DEPT_TITLE_UQ UNIQUE(DEPT_TITLE);
ALTER TABLE EMP_DEPT MODIFY LNAME NOT NULL;
2_2) 제약조건 삭제
DROP CONSTARINT 제약조건명;
NOT NULL : MODIFY 컬럼명 NULL;
-- EMP_DEPT 테이블로 부터 PK 제약조건 지우기
ALTER TABLE EMP_DEPT DROP CONSTRAINT EMP_DEPT_ID_PK;
-- EMP_DEPT 테이블로 부터 UNIQUE , NOTNULL 제약조건 삭제
ALTER TABLE EMP_DEPT
DROP CONSTRAINT EMP_DEPT_TITLE_UQ
MODIFY LNAME NULL;
3) RENAME ( 칼럼명 / 테이블명/ 제약조건명)
-- 3_1) 컬럼명 변경 : RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명
-- EMP_DEPT 에서 DEPT_TITLE 컬럼을 DEPT_NAME 으로 바꾸기
ALTER TABLE EMP_DEPT RENAME COLUMN DEPT_TITLE TO DEPT_NAME;
-- 3_2) 제약조건명 변경 : RENAME CONSTRAINT 기존명 TO 바꿀명
-- EMPLOYEE 테이블에서 SYS_C008359 제약조건을 EMP_NO_NN로 변경
--SYS_C008359 EMP_ID
ALTER TABLE EMPLOYEE RENAME CONSTRAINT SYS_C008359 TO EMP_NO_NN;
-- 3_3) 테이블명 변경 : RENAME 기존명 TO 바꿀명;
-- RENAME TO 바꿀명;
-- EMP_DEPT 테이블을 EMP_TEST 로 변경
ALTER TABLE EMP_DEPT RENAME TO EMP_TEST;2. DROP
- 객체를 삭제하는 구문
DROP TABLE 테이블명;
DROP TABLE EMP_01;
-- 부모 테이블을 삭제해야 할 경우 ?
-- EMP_TEST 테이블에 EMP_ID 칼럼을 PK로 만들기
ALTER TABLE EMP_TEST ADD CONSTRAINT EMP_TEST_ID_PK PRIMARY KEY(EMP_ID);
-- EMP_COPY 를 생성후 EMP_TEST의 EMP_ID값을 외래키로 추가하가
CREATE TABLE EMP_COPY AS SELECT * FROM EMP_TEST;
-- 이때 부모테이블 EMP_TEST , 자식테이블 EMP_COPY로 만들기
ALTER TABLE EMP_COPY ADD CONSTRAINT EMP_COPY_ID_FK FOREIGN KEY(EMP_ID)
REFERENCES EMP_TEST (EMP_ID);
DROP TABLE EMP_TEST;
-- 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다 오류 삭제 불가
-- 삭제 하려면?
-- 방법1) 자식테이블을 먼저 삭제한 후 부모테이블을 삭제
-- 방법2) 부모테이블만 삭제하되, 맞물려있는 외래키 제약조건도 함께 삭제
-- DROP TABLE EMP_TEST CASCADE CONSTRAINT;
-- ALTER TABLE EMP_COPY DROP CONSTRAINT 외래키제약조건명;
-- DROP TABLE EMP_TEST
DROP TABLE EMP_TEST CASCADE CONSTRAINT;
