[Oracle] DDL

DDL

• 데이터 정의어
• 데이터베이스 객체를 생성/수정/삭제한다.
• 데이터베이스 객체 → 테이블, 뷰, 인덱스, 프로시저, 트리거, 제약사항, 시노닙 등..
• CREATE, ALTER, DROP

 

테이블 생성하기(CREATE TABLE)

테이블 생성하기 → 스키마 정의하기 → 속성(컬럼) 정의하기 → 컬럼의 이름, 자료형, 제약사항을 정리

create table 테이블명
(
	컬럼 정의,
	컬럼 정의,
	컬럼 정의,
	컬럼명 자료형(길이),
	컬럼명 자료형(길이) NULL 제약사항
);

 

제약사항(Constraint)

• 해당 컬럼에 들어갈 데이터(값)에 대한 조건
  • 조건을 만족하면 → 대입
  • 조건을 불만족하면 → 에러 발생
• 유효성 검사 도구
• 데이터 무결성을 보장하기 위한 도구(★★★)

 

1. NOT NULL

• 해당 컬럼이 반드시 값을 가져야 한다.
• 해당 컬럼에 값이 없으면 에러 발생
• 필수값

CREATE TABLE tblMemo
(
	-- 컬럼명 자료형(길이) NULL 제약사항
	seq number(3) NOT NULL,			-- 메모 번호(NN)
	name varchar2(30) NULL,			-- 작성자
	memo varchar2(1000) NOT NULL, 	-- 메모(NN)
	regdate DATE NULL 				-- 작성날짜
);

INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (1, '홍길동', '메모입니다', sysdate);

-- ORA-01400: cannot insert NULL into ("HR"."TBLMEMO"."MEMO")
INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (2, '홍길동', null, sysdate); -- 명시적으로 null을 기입하는 방법

-- ORA-01400: cannot insert NULL into ("HR"."TBLMEMO"."MEMO")
INSERT INTO tblmemo (seq, name, regdate) VALUES (2, '홍길동', sysdate); -- 생략된 컬럼에는 null이 들어간다.

-- ORA-01400: cannot insert NULL into ("HR"."TBLMEMO"."MEMO")
INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (2, '홍길동', '', sysdate); -- 빈문자('')도 null로 취급한다.

• NOT NULL 에러 발생 Case
  • ORA-01400: cannot insert NULL into ("HR"."TBLMEMO"."MEMO") → 명시적 NULL 기입, 컬럼 생략, 빈문자(' ') 입력

 

2. PRIMARY KEY, PK

• 기본키
• 테이블의 행을 구분하기 위한 제약 사항
• 행을 식별하는 수많은 키(후보키)들 중 대표로 선정된 키
• 모든 테이블은 반드시 1개의 기본키가 존재해야 한다.(★★★)
• 중복값을 가질 수 없다. → UNIQUE
• 값을 반드시 가진다. → NOT NULL
• PK = UQ(UNIQUE) + NN(NOT NULL)

CREATE TABLE tblMemo
(
	-- 컬럼명 자료형(길이) NULL 제약사항
	seq number(3) PRIMARY KEY ,		       -- 메모 번호(PK)
	name varchar2(30) NULL,			         -- 작성자
	memo varchar2(1000) NOT NULL, 	     -- 메모(NN)
	regdate DATE NULL 				           -- 작성날짜
);


--INSERT 가능(O)
INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (1, '홍길동', '메모입니다', sysdate);


--INSERT 불가(X -> ERROR)
-- ORA-00001: unique constraint (HR.SYS_C007087) violated
INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (1, '홍길동', '메모입니다', sysdate); -- INSERT 후, 동일 seq로 재 INSERT 시!!

-- ORA-01400: cannot insert NULL into ("HR"."TBLMEMO"."SEQ")
INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (null, '홍길동', '메모입니다', sysdate);

• PRIMARY KEY 에러 발생 Case
  • ORA-00001: unique constraint (HR.SYS_C007087) violated → 중복값 입력 에러
  • ORA-01400: cannot insert NULL into ("HR"."TBLMEMO"."SEQ") → null 입력 에러

 

WHY? 왜 기본키가 반드시 필요할까?
: 기본키가 없는 상태에서 중복값이 있는 컬럼명으로 조건을 걸어 수정/삭제 시, 중복값 모두 처리가 된다. 따라서 중복되지 않는 고유한 값인 기본키가 반드시 필요하다.

 

3. FOREIGN KEY, FK

• 외래키, 참조키
• 추후 정리 예정이다.

 

4. UNIQUE

• 유일하다. → 레코드간의 중복값을 가질 수 없다.
• NULL을 가질 수 있다. → 식별자가 될 수 없다.

-- 초등학교 교실
학생(번호(PK), 이름(NN), 직책(UQ))
1, 홍길동, 반장
2, 아무개, NULL
3, 하하하, 부반장
4, 테스트, NULL

 

5. CHECK

• 사용자 정의형
• WHERE절의 조건 → 컬럼의 제약사항으로 적용

CREATE TABLE tblMemo
(
	-- 컬럼명 자료형(길이) NULL 제약사항
	seq number(3) PRIMARY KEY ,		-- 메모 번호(NN)
	name varchar2(30),				-- 작성자(UQ)
	memo varchar2(1000), 			-- 메모(NN)
	regdate DATE, 					-- 작성날짜
	priority NUMBER(1) CHECK (priority BETWEEN 1 AND 3),		-- 중요도(1(중요), 2(보통), 3(안중요))
	category varchar2(30) CHECK (category IN ('할일', '공부', '약속', '가족', '개인'))		-- 카테고리(할일, 공부, 약속, 가족, 개인) 
);

SELECT * FROM tblmemo;

INSERT INTO tblmemo (seq, name, memo, regdate, priority, category) VALUES (1, '홍길동', '메모입니다', sysdate, 1, '할일');

-- ORA-02290: check constraint (HR.SYS_C007093) violated
INSERT INTO tblmemo (seq, name, memo, regdate, priority, category) VALUES (2, '홍길동', '메모입니다', sysdate, 5, '할일');

-- ORA-02290: check constraint (HR.SYS_C007094) violated
INSERT INTO tblmemo (seq, name, memo, regdate, priority, category) VALUES (3, '홍길동', '메모입니다', sysdate, 1, '메롱');

• CHECK 에러 발생 Case
  • ORA-02290: check constraint (HR.SYS_C007093) violated

 

6. DEFAULT

• 기본값 설정
• INSERT/UPDATE 작업 시, 컬럼에 값을 안 넣으면 NULL 대신 미리 설정한 값을 대입

CREATE TABLE tblMemo
(
	-- 컬럼명 자료형(길이) NULL 제약사항
	seq number(3) PRIMARY KEY ,		-- 메모 번호(NN)
	name varchar2(30) DEFAULT '익명',				-- 작성자(UQ)
	memo varchar2(1000), 			-- 메모(NN)
	regdate DATE DEFAULT sysdate 					-- 작성날짜
);

SELECT * FROM tblmemo;

INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (1, '홍길동', '메모입니다', sysdate);

INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (2, null, '메모입니다', null); --null을 명시하면 'null'이 대입됨. > default 동작(X)

INSERT INTO tblmemo (seq, memo) VALUES (3, '메모입니다'); --컬럼을 생략하면 default값이 대입됨. > 암시적

INSERT INTO tblmemo (seq, name, memo, regdate) VALUES (4, default, '메모입니다', default); --DEFAULT 상수

 

제약 사항을 만드는 방법

1. 컬럼 수준에서 만드는 방법

 - 위에서 사용한 방법

 - 컬럼을 선언할 때 제약 사항도 같이 선언하는 방법

 

2. 테이블 수준에서 만드는 방법

CREATE TABLE tblMemo
(
	-- 컬럼명 자료형(길이) NULL 제약사항
	seq number(3),
	name varchar2(30),
	memo varchar2(1000) DEFAULT '메모입니다.',
	regdate DATE NOT null,
	
	-- 테이블 수준에서 제약 사항 정의 > 가독성
	CONSTRAINT tblmemo_seq_pk PRIMARY KEY(seq), --추후 제약의 이름을 보고 어디에서 Error가 발생되었는지 알 수 있음!
	CONSTRAINT tblmemo_name_uq UNIQUE(name),
	CONSTRAINT tblmemo_memo_ck CHECK(LENGTH(memo) >= 10) --자료형에서는 최대값만 설정 가능하고 최소값은 설정 불가하므로!
	-- NOT NULL과 DEFAUL는 밑으로 내보내는 방법 없이 반드시 위에 제약사항을 걸어야 함!!

 

3. 외부에서 만드는 방법

 

*글 작성에 참고한 내용: 학원 쌤의 열정적인 수업