techhan

techhan

Always a Student

2020년 11월 04일

업데이트:

DDL(Data Definition Language)

데이터 정의 언어로 객체(Object)를 만들고(CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말한다.

  • 오라클 객체 종류
    • 테이블(TABLE)
    • 뷰(VIEW)
    • 시퀀스(SEQUENCE)
    • 인덱스(INDEX)
    • 패키지(PACKAGE)
    • 프로시저(PROCEDUAL)
    • 함수(FUNCTION)
    • 트리거(TRIGGER)
    • 동의어(SYNONYM)
    • 사용자(USER)



CREATE

테이블이나 인덱스, 뷰 등 데이터베이스 객체를 생성하는 구문이다.

  • 테이블(TABLE)
    • 행(ROW)과 열(COLUMN)로 구성되는 데이터베이스의 가장 기본적인 객체이다.
    • 데이터베이스 내에서 모든 데이터는 테이블을 통해 저장된다. 
/*
- 테이블 생성 SQL 구문 작성법
    
CREATE TABLE 테이블명(
    컬럼명 데이터타입(크기 | 자릿수),
    컬럼명 데이터타입(크기 | 자릿수),
    컬럼명 데이터타입(크기 | 자릿수),
    ...
    );
*/

-- CHAR : 고정길이 문자 자료형 (2000BYTE)
                    --> 지정된 바이트보다 적은 양의 데이터가 저장되어도
                    -- 남은 용량을 반환하지 않음

-- VARCHAR : 가변길이 문자 자료형  (2000BYTE)
                            --> 지정된 바이트보다 적은 양의 데이터가 저장된 경우
                            -->  남은 크기(용량) 반환
                            
--> VARCHAR2 : 4000 BYTE 가변길이 문자 자료형


-- 문자형 데이터를 30바이트까지 저장할 수 있는 MEMBER_ID 컬럼
CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(30),
    MEMBER_PWD VARCHAR2(30), 
    MEMBER_NAME VARCHAR2(20),
    MEMBER_SSN CHAR(14),
    ENROLL_DATE DATE DEFAULT SYSDATE
    );
    
-- DEFAULT SYSDATE : 입력되는 값이 없거나 DEFAULT가 입력된 경우 현재 시간 기록



-- MEMBER TABLE 생성 확인
SELECT * FROM MEMBER;

--  2. 컬럼에 코멘트(주석)달기
--  [작성법]
-- COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS ' 회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS ' 회원비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS ' 회원이름';
COMMENT ON COLUMN MEMBER.MEMBER_SSN IS ' 주민등록번호';
COMMENT ON COLUMN MEMBER.ENROLL_DATE IS ' 회원가입일';


-- 사용자가 생성한 테이블 생성 확인
SELECT * FROM USER_TABLES;

-- 딕셔너리 뷰(DICTIONARY VIEW)
-- USER_TALBES : 사용자가 작성한 테이블을 확인하는 뷰(가상의 테이블)

/* - DATA DIXTIONARY(데이터 딕셔너리) (★★★★★)
    - 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블
    
    - 데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의
     작업을 할 때 데이터베이스 서버에 의해서 자동으로 갱신됨
     
*/

SELECT USERNAME FROM  DBA_USERS;
--   DBA_USERS : 데이터베이스에 존재하는 모든 사용자(USER) 정보를 조회하는 딕셔너리 뷰
-- (SYS AS SYSDBA 계정으로만 가능)


SELECT * FROM 
USER_TAB_COLUMNS
WHERE TABLE_NAME = 'MEMBER';
-- USER_TAB_COLUMNS : 테이블, 뷰, 클러스터의 컬럼과 관련된 정보를 조회하는 딕셔너리 뷰


DESC MEMBER;
-- DESC문 : 테이블의 구조를 표시하는 구문


-- MEMBER 테이블에 샘플 데이터 삽입
INSERT INTO MEMBER VALUES('MEM01', '123ABC' , '홍길동', '000101-3345678', DEFAULT);

-- 데이터 삽입 확인
SELECT * FROM MEMBER;

COMMIT;
-- 테이블 조작 내용(DML)을 실제 DB에 반영하는 TCL 구문


-- 가입일 -> SYSDATE를 활용
INSERT INTO MEMBER VALUES ('MEM02', 'QWER1234', '김영희', '971208-2123456', SYSDATE); 

-- 가입일 -> DEFAULT 활용(테이블 생성 시 정의된 값이 반영됨)
INSERT INTO MEMBER VALUES ('MEM03', 'ZZ9786', '박철수', '900314-1122334',  DEFAULT); -- DEFAULT

-- 가입일 -> INSERT 시 미작성 하는 경우 -> DEAFULT 값이 반영됨
INSERT INTO MEMBER(MEMBER_ID, MEMBER_PWD, MEMBER_NAME, MEMBER_SSN)
VALUES ('MEM04', 'ASDQWE','이지연', '851011-2345678');


COMMIT;
SELECT * FROM MEMBER;

-- 주민등록번호, 전화번호 등 숫자가 포함된 데이터의 컬럼을 문자형으로 하는 이유

CREATE TABLE MEMBER2(
    MEMBER_ID VARCHAR2(30),
    MEMBER_PWD VARCHAR2(30), 
    MEMBER_NAME VARCHAR2(20),
    MEMBER_SSN NUMBER, 
    ENROLL_DATE DATE DEFAULT SYSDATE
    );
    

INSERT INTO MEMBER2 VALUES ('MEM02', 'QWER1234', '김영희', 0712082123456, SYSDATE); 

SELECT * FROM MEMBER2;
-- 주민등록번호 제일 앞 0이 사라지는 문제 발생
  --> 이를 해결하기 위해 문자 데이터 타입 사용



제약 조건(CONSTRANITS)

테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약 조건을 설정할 수 있다. 이는 데이터 무결성 보장을 주 목적으로 하며, 입력 데이터에 문제가 없는지에 대한 검사와 데이터의 수정/삭제 가능 여부 검사 등을 위해 사용한다.

  • 제약 조건
    • NOT NULL : 데이터에 NULL을 허용하지 않음
    • UNIQUE : 중복된 값을 허용하지 않음
    • PRIMARY KEY : NULL과 중복 값을 허용하지 않음(컬럼의 고유 식별자로 사용하기 위해)
    • FOREIGN KEY : 참조되는 테이블의 컬럼 값이 존재하면 허용한다.
    • CHECK : 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용한다.
-- 제약 조건 확인 방법
SELECT * FROM USER_CONSTRAINTS;
--USER_CONSTRAINTS : 사용자가 작성한 제약 조건을 확인하는 딕셔너리 뷰

SELECT * FROM USER_CONS_COLUMNS;
--  USER_CONS_COLUMNS  : 제약 조건이 설정된 컬럼을 확인하는 딕셔너리 뷰


NOT NULL

해당 컬럼에 반드시 값이 기록되어야 하는 경우에 사용한다. 특정 컬럼에 값을 저장하거나 수정할 때 NULL 값을 허용하지 않도록 컬럼 레벨에서 제한한다.

-- NOT NULL 제약 조건이 없는 경우
CREATE TABLE USER_NOT_NN (
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(20),
    USER_NAME VARCHAR2(30),
    GENDER CHAR(3), 
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

INSERT INTO USER_NOT_NN
VALUES(1, 'USER01', 'PASS01', '김영주', '남', '010-1111-2222', 'USER01@naver.com');


INSERT INTO USER_NOT_NN
VALUES(2, NULL, NULL,NULL, '남', '010-1111-2222', 'USER01@naver.com');

SELECT * FROM USER_NOT_NN;


-- NOT NULL 제약 조건이 설정된 경우
-- (★★★★★★★★★★) NOT NULL 제약 조건은  테이블 생성 시 컬럼 레벨로만 지정 가능함
CREATE TABLE USER_NN (
    USER_NO NUMBER NOT NULL, -- 컬럼 지정 시 제약 조건도 같이 설정  --> 컬럼 레벨 설정
    USER_ID VARCHAR2(20) NOT NULL,
    USER_PWD VARCHAR2(20) NOT NULL,
    USER_NAME VARCHAR2(30) ,
    GENDER CHAR(3), 
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50) NOT NULL
);

-- 작성한 제약 조건 확인
SELECT C1.TABLE_NAME, COLUMN_NAME,  SEARCH_CONDITION
FROM USER_CONSTRAINTS C1
JOIN USER_CONS_COLUMNS C2 USING(CONSTRAINT_NAME)
WHERE C1.TABLE_NAME = 'USER_NN';


INSERT INTO USER_NN
VALUES(1, 'USER01', 'PASS01', '김영주', '남', '010-1111-2222', 'USER01@naver.com');

INSERT INTO USER_NN
VALUES(2, 'USER02', 'PASS02' ,NULL, '남', '010-1111-2222', 'USER01@naver.com');
-- ORA-01400: cannot insert NULL into ("KH"."USER_NN"."USER_ID").
 --> NOT NULL 제약 조건을 위배해서 에러 발생



UNIQUE

컬럼 입력 값에 대해 중복을 제한하는 제약조건으로 컬럼 레벨과 테이블 레벨에 설정 가능하다. 단, UNIQUE 제약 조건이 설정된 컬럼에도 NULL 값은 중복으로 저장될 수 있다.

-- USER_NN 테이블에 중복 데이터 삽입(성공 -> 무결성이 침범)
SELECT * FROM USER_NN;

INSERT INTO USER_NN
VALUES(1, 'USER01', 'PASS01', '김영주', '남', '010-1111-2222', 'USER01@naver.com');

-- 1) 컬럼 레벨로  UNIQUE 제약 조건 설정

-- UNIQUE 제약 조건 테이블 생성
CREATE TABLE USER_UK(
    USER_NO NUMBER,
 --   USER_ID VARCHAR2(20) UNIQUE, -- 기본 컬럼 레벨 제약 조건 지정
    USER_ID VARCHAR2(20) CONSTRAINT USER_ID_UK  UNIQUE,
                                                    -- CONSTRAINT   제약조건명   제약조건
    USER_PWD VARCHAR2(20) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER CHAR(3),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);



-- 삽입을 순서대로 하나씩 진행
INSERT INTO USER_UK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');


INSERT INTO USER_UK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
-- ORA-00001: unique constraint (KH.USER_ID_UK) violated
--> USER_ID 컬럼에 중복데이터가 삽입되어 UNIQUE 제약 조건 위배

INSERT INTO USER_UK
VALUES(1, NULL, 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
-- UNIQUE는 NULL 값 삽입 가능

INSERT INTO USER_UK
VALUES(1, NULL, 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
-- UNIQUE는 NULL 값 중복 삽입도 가능

-- 삽입 결과 확인
SELECT  * FROM USER_UK; 


-- 오류 보고에 나타나는 SYS_C008635 같은 제약 조건명으로
-- 해당 제약 조건이 설정된 테이블명, 컬럼, 제약 조건 타입 조회 
SELECT UCC.TABLE_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
AND UCC.CONSTRAINT_NAME = 'USER_ID_UK';


--------------------------------------------

-- 2) 테이블 레벨에서 제약 조건 설정


/* [작성법]
	CREATE TABLE 테이블명(
            컬럼명 데이터타입,... ,
            [CONSTRAINT 제약조건명] 제약조건 (컬럼명)
            );
*/


CREATE TABLE USER_UK2(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(20) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER CHAR(3),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    -- UNIQUE(USER_ID) -- 기본 테이블 레벨 제약 조건 설정
    CONSTRAINT USER_ID_UK2 UNIQUE (USER_ID)
);


-- USER_UK2 테이블에 설정된 제약조건 조회
SELECT C1.TABLE_NAME,  COLUMN_NAME  ,SEARCH_CONDITION
FROM USER_CONSTRAINTS  C1 
JOIN USER_CONS_COLUMNS C2 USING(CONSTRAINT_NAME)
WHERE C1.TABLE_NAME = 'USER_UK2';


-- 데이터를 삽입하여 UNIQUE 제약 조건 확인
INSERT INTO USER_UK2
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_UK2
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
-- ORA-00001: unique constraint (KH.USER_ID_UK2) violated


SELECT * FROM USER_UK2;


-- 3) UNIQUE 복합키
-- 두 개 이상의 컬럼을 묶어 하나의 UNIQUE 제약 조건 설정
-- 설정된 컬럼의 데이터가 모두 일치해야 중복으로 판정됨
-- 복합키는 테이블 레벨로만 작성할 수 있다.


CREATE TABLE USER_UK3(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(20) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER CHAR(3),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    CONSTRAINT USER_NO_ID_UK UNIQUE (USER_NO, USER_ID)
);




INSERT INTO USER_UK3
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_UK3
VALUES(2, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_UK3
VALUES(2, 'user02', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_UK3
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
--> 여러 컬럼을 묶어서 UNIQUE 제약 조건이 설정되어 있으면 
-- 두 컬럼이 모두 중복되는 값일 경우에만 오류 발생

SELECT * FROM USER_UK3;


SELECT UC.TABLE_NAME, UCC.COLUMN_NAME, UCC.CONSTRAINT_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UCC.CONSTRAINT_NAME = 'USER_NO_ID_UK';
--> 두개의 UNIQUE 제약 조전이이 하나의 제약 조건명으로 되어있는 것 확인



PRIMARY KEY(기본키)

테이블에서 한 행의 정보를 구분하기 위한 고유 식별자(IDENTIFIER) 역할을 하며, NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있다. 한 테이블 당 하나만 설정이 가능하며 컬럼 레벨과 테이블 레벨 둘 다 지정이 가능하다.

-- 1) PRIMARY KEY가 설정된 테이블 생성

CREATE TABLE USER_PK(
  --USER_NO NUMBER PRIMARY KEY, -- 컬럼 레벨로 PK 설정
 -- USER_NO NUMBER CONSTRAINT USER_PK_PK PRIMARY KEY,  -- 컬럼 레벨로 PK 설정, + 이름 지정
  USER_NO NUMBER,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER CHAR(3),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  --PRIMARY KEY (USER_NO) -- 테이블 레벨 PK 설정
  CONSTRAINT USER_PK_PK PRIMARY KEY(USER_NO) -- 테이블 레벨 PK 설정 + 이름 지정
);


-- 순서 대로 삽입 하면서 확인
INSERT INTO USER_PK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_PK
VALUES(1, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr');
-- ORA-00001: unique constraint (KH.USER_PK_PK) violated

INSERT INTO USER_PK
VALUES(NULL, 'user03', 'pass03', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr');
-- ORA-01400: cannot insert NULL into ("KH"."USER_PK"."USER_NO")


-- PK_USER_NO 제약조건 확인
SELECT UC.TABLE_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UC.CONSTRAINT_NAME = 'USER_PK_PK';

-- 2) PRIMARY KEY 복합키
 --> 테이블 레벨로만 작성할 수 있음
CREATE TABLE USER_PK2(
  USER_NO NUMBER,
  USER_ID VARCHAR2(20),
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER CHAR(3),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  CONSTRAINT USER_NO_ID_PK PRIMARY KEY(USER_NO, USER_ID)
);

-- 순서 대로 삽입 하면서 확인
INSERT INTO USER_PK2
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_PK2
VALUES(1, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr');

INSERT INTO USER_PK2
VALUES(2, 'user01', 'pass01', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr');

INSERT INTO USER_PK2
VALUES(1, 'user01', 'pass01', '신사임당', '여', '010-9999-9999', 'sin123@kh.or.kr');
-- ORA-00001: unique constraint (KH.USER_NO_ID_PK) violated


SELECT * FROM USER_PK2;



FOREIGN KEY(외부키/외래키)

참조 무결성을 위한 제약조건으로 참조된 다른 테이블이 제공한 값만 사용하도록 제한을 건다. 참조되는 컬럼과 참조된 컬럼을 통해 테이블 간에 관계가 형성되는데, 참조되는 값은 제공되는 값 외에 NULL을 사용 가능하며 참조할 테이블의 참조할 컬럼명을 생략할 경우 PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 된다.

/*
 -- 컬럼 레벨로 제약 조건 설정
  컬럼명 데이터타입 [CONSTRAINT 제약조건명] REFERENCES 참조테이블명[(참조 컬럼명)] [삭제 옵션]
  
  -- 테이블 레벨로 제약 조건 설정
  [CONSTRAINT 제약조건명] FOREIGN KEY(적용컬럼명) REFERENCES 참조테이블명[(참조 컬럼명)] [삭제 옵션]
  
  --★★★★★
  - 참조할 수 있는 컬럼은 PK 또는 UNIQUE 제약 조건이 설정된 컬럼만 지정할 수 있다.
  - 참조 컬럼명을 작성하지 않으면 참조 테이블의 PK 컬럼을 참조하게 됨\
*/

-- 1) FOREIGN KEY 제약 조건 설정

-- FOREIGN KEY 제약조건 확인을 위한 테이블, 샘플데이터
CREATE TABLE USER_GRADE(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);
INSERT INTO USER_GRADE VALUES (10, '일반회원');
INSERT INTO USER_GRADE VALUES (20, '우수회원');
INSERT INTO USER_GRADE VALUES (30, '특별회원');

SELECT * FROM USER_GRADE;


-- USER_GRADE테이블과 FOREIGN KEY 제약조건이 맺어진 테이블 생성
CREATE TABLE USER_FK(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER CHAR(3),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  GRADE_CODE NUMBER,
  CONSTRAINT GRADE_CODE_FK FOREIGN KEY(GRADE_CODE)
        REFERENCES USER_GRADE(GRADE_CODE)  
);



-- 순서 대로 삽입 하면서 확인
INSERT INTO USER_FK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr', 10);

INSERT INTO USER_FK
VALUES(2, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr', 10);

INSERT INTO USER_FK
VALUES(3, 'user03', 'pass03', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr', 30);

INSERT INTO USER_FK
VALUES(4, 'user04', 'pass04', '안중근', '남', '010-2222-1111', 'ahn123@kh.or.kr', null);

SELECT * FROM USER_FK;

INSERT INTO USER_FK
VALUES(5, 'user05', 'pass05', '윤봉길', '남', '010-6666-1234', 'yoon123@kh.or.kr', 50);
-- ORA-02291: integrity constraint (KH.GRADE_CODE_FK) violated - parent key not found


-- 설정된 제약조건 확인
SELECT UC.TABLE_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_NAME, UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON (UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UC.CONSTRAINT_NAME = 'GRADE_CODE_FK';


SELECT * FROM USER_FK
LEFT JOIN USER_GRADE USING(GRADE_CODE);
-- FK로 연결됨 == 두 테이블이 연결되어 관계가 형성됨 
-- == JOIN 가능(JOIN 시 FK에 사용된 컬럼을 작성하면 됨..


-- 2) FOREIGN KEY 삭제 옵션 
-- 부모테이블의 데이터 삭제시 자식 테이블의 데이터를 
-- 어떤식으로 처리할 지에 대한 내용을 설정할 수 있다.

-- 2-1) ON DELETE RESTRICTED(삭제 제한)로 기본 지정되어 있음
-- FOREIGN KEY로 지정된 컬럼에서 사용되고 있는 값일 경우
-- 제공하는 컬럼의 값은 삭제하지 못함

SELECT * FROM USER_GRADE;

SELECT * FROM USER_FK;

DELETE FROM USER_GRADE WHERE GRADE_CODE = 10; -- 삭제 구문
-- ORA-02292: integrity constraint (KH.GRADE_CODE_FK) violated - child record found

COMMIT;


DELETE FROM USER_GRADE WHERE GRADE_CODE = 20;
-- GRADE_CODE 중 20은 외래키로 참조되고 있지 않으므로 삭제가 가능함.

SELECT * FROM USER_GRADE;

ROLLBACK;

-- 2-2)ON DELETE SET NULL 
-- 부모 컬럼의 데이터가 삭제된 경우
-- 참조하던 자식의 컬럼 값을 NULL로 변경하는 옵션



-- 삭제 조건 확인을 위한 테이블, 샘플 데이터
CREATE TABLE USER_GRADE2(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE2
VALUES (10, '일반회원');
INSERT INTO USER_GRADE2
VALUES (20, '우수회원');
INSERT INTO USER_GRADE2
VALUES (30, '특별회원');

SELECT * FROM USER_GRADE2;


-- ON DELETE SET NULL 삭제 옵션을 추가한 테이블 생성
CREATE TABLE USER_FK2(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER CHAR(3),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  GRADE_CODE NUMBER,
  CONSTRAINT GRADE_CODE_FK2  FOREIGN KEY(GRADE_CODE)
        REFERENCES USER_GRADE2(GRADE_CODE) ON DELETE SET NULL
 );

-- 샘플데이터 삽입
INSERT INTO USER_FK2
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr', 10);

INSERT INTO USER_FK2
VALUES(2, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr', 10);

INSERT INTO USER_FK2
VALUES(3, 'user03', 'pass03', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr', 30);

INSERT INTO USER_FK2
VALUES(4, 'user04', 'pass04', '안중근', '남', '010-2222-1111', 'ahn123@kh.or.kr', null);

COMMIT;




SELECT * FROM USER_GRADE2;
SELECT * FROM USER_FK2;

-- USER_GRADE2 테이블에서 GRADE_COE =10 삭제
DELETE FROM USER_GRADE2
WHERE GRADE_CODE = 10;
--> ON DELETE SET NULL 옵션이 설정되어 있어 삭제가 이루어짐.


SELECT * FROM USER_GRADE2;
SELECT * FROM USER_FK2;


ROLLBACK;



-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


-- 2-3) ON DELETE CASCADE(종속)
-- 부모 컬럼 값을 삭제하는 경우
-- 해당 컬럼 값을 



-- 삭제 조건 확인을 위한 테이블, 샘플 데이터
CREATE TABLE USER_GRADE3(
  GRADE_CODE NUMBER PRIMARY KEY,
  GRADE_NAME VARCHAR2(30) NOT NULL
);
INSERT INTO USER_GRADE3
VALUES (10, '일반회원');
INSERT INTO USER_GRADE3
VALUES (20, '우수회원');
INSERT INTO USER_GRADE3
VALUES (30, '특별회원');



-- ON DELETE CASCADE 삭제 옵션을 추가한 테이블 생성
CREATE TABLE USER_FK3(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER CHAR(3),
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50),
  GRADE_CODE NUMBER,
  CONSTRAINT GRADE_CODE_FK3 FOREIGN KEY(GRADE_CODE)
   REFERENCES USER_GRADE3 (GRADE_CODE) ON DELETE CASCADE
);


-- 샘플데이터 삽입
INSERT INTO USER_FK3
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr', 10);

INSERT INTO USER_FK3
VALUES(2, 'user02', 'pass02', '이순신', '남', '010-5678-9012', 'lee123@kh.or.kr', 10);

INSERT INTO USER_FK3
VALUES(3, 'user03', 'pass03', '유관순', '여', '010-9999-3131', 'yoo123@kh.or.kr', 30);

INSERT INTO USER_FK3
VALUES(4, 'user04', 'pass04', '안중근', '남', '010-2222-1111', 'ahn123@kh.or.kr', null);

COMMIT;


SELECT * FROM USER_GRADE3;
SELECT * FROM USER_FK3;

DELETE FROM USER_GRADE3
WHERE GRADE_CODE = 10;

SELECT * FROM USER_GRADE3;
SELECT * FROM USER_FK3;
-- ON DELETE CASECADE 옵션으로 인해 참조키를 사용한 행이 삭제됨을 확인

ROLLBACK;



CHECK

해당 컬럼에 입력 되거나 수정되는 값을 체크하여 설정된 값 이외의 값이면 에러가 발생한다. 비교 연산자를 이용하여 조건을 설정하며 비교 값은 리터럴만 사용이 가능하고 변하는 값이나 함수 사용은 불가능하다.

-- CHECK 제약조건이 설정된 테이블 생성 
CREATE TABLE USER_CHECK(
  USER_NO NUMBER PRIMARY KEY,
  USER_ID VARCHAR2(20) UNIQUE,
  USER_PWD VARCHAR2(30) NOT NULL,
  USER_NAME VARCHAR2(30),
  GENDER CHAR(3) CHECK(GENDER IN('남', '여')) ,
  PHONE VARCHAR2(30),
  EMAIL VARCHAR2(50)
);


-- 순서 대로 삽입 하면서 확인
INSERT INTO USER_CHECK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');

INSERT INTO USER_CHECK
VALUES(2, 'user02', 'pass02', '홍길순', '여', '010-1234-5678', 'hong123@kh.or.kr');
-- ORA-12899: value too large for column "KH"."USER_CHECK"."GENDER" (actual: 6, maximum: 3) --> 데이터 크기 초과

-- ORA-02290: check constraint (KH.SYS_C007049) violated
--> 체크 제약 조건 위배

INSERT INTO USER_CHECK
VALUES(3, 'user03', 'pass03', '홍길순', NULL, '010-1234-5678', 'hong123@kh.or.kr');
-- NULL 가능함





---------------------


-- (참고) CHECK 제약 조건은 범위로도 설정 가능.
CREATE TABLE USER_CHECK2(
  TEST_NUMBER NUMBER,
  CONSTRAINT CK_TEST_NUMBER CHECK (TEST_NUMBER > 0)
);

INSERT INTO USER_CHECK2
VALUES (10);
INSERT INTO USER_CHECK2
VALUES (-10);
 

-- (참고) 복잡한 범위 설정 + 테이블 레벨로 CHECK 제약조건 추가
CREATE TABLE USER_CHECK3 (
    C_NAME VARCHAR2(15 CHAR),
    C_PRICE NUMBER,
    C_LEVEL CHAR(1),
    C_DATE DATE,
    CONSTRAINT TBCH3_NAME_PK PRIMARY KEY (C_NAME),
    CONSTRAINT TBCH3_PRICE_PK CHECK (C_PRICE >= 1 AND C_PRICE <= 99999),
    CONSTRAINT TBCH3_LEVEL_PK CHECK (C_LEVEL = 'A' OR C_LEVEL = 'B' OR C_LEVEL = 'C'),
    CONSTRAINT TBCH3_DATE_PK CHECK (C_DATE >= TO_DATE('2016/01/01', 'YYYY/MM/DD'))
);

실습 문제

-- [실습 문제]
-- 회원가입용 테이블 생성(USER_TEST)
-- 컬럼명 : USER_NO(회원번호) - 기본키(PK_USER_TEST), 
--         USER_ID(회원아이디) - 중복금지(UK_USER_ID),
--         USER_PWD(회원비밀번호) - NULL값 허용안함(NN_USER_PWD),
--         PNO(주민등록번호) - 중복금지(UK_PNO), NULL 허용안함(NN_PNO),
--         GENDER(성별) - '남' 혹은 '여'로 입력(CK_GENDER),
--         PHONE(연락처),
--         ADDRESS(주소),
--         STATUS(탈퇴여부) - NOT NULL(NN_STATUS), 'Y' 혹은 'N'으로 입력(CK_STATUS)
-- 각 컬럼의 제약조건에 이름 부여할 것
-- 5명 이상 INSERT할 것



CREATE TABLE USER_TEST (
    USER_NO NUMBER  CONSTRAINT PK_USER_TEST PRIMARY KEY,
    USER_ID VARCHAR(30) CONSTRAINT UK_USER_ID UNIQUE,
    USER_PWD VARCHAR(30)  CONSTRAINT NN_USER_PWD NOT NULL,
    PNO CHAR(14) CONSTRAINT NN_PNO NOT NULL,
   GENDER CHAR(3) CONSTRAINT CK_GENDER CHECK(GENDER IN('남', '여')),
   PHONE VARCHAR(20),
   ADDRESS VARCHAR(50),
   STATUS CHAR(1) CONSTRAINT NN_STATUS NOT NULL, 
    CONSTRAINT UK_PNO UNIQUE (PNO),
    CONSTRAINT CK_STATUS CHECK(STATUS IN('Y', 'N'))
);



COMMENT ON COLUMN USER_TEST.USER_NO IS ' 회원번호';
COMMENT ON COLUMN USER_TEST.USER_ID IS ' 회원아이디';
COMMENT ON COLUMN USER_TEST.USER_PWD IS ' 회원비밀번호';
COMMENT ON COLUMN USER_TEST.PNO IS ' 주민등록번호';
COMMENT ON COLUMN USER_TEST.GENDER IS ' 성별';
COMMENT ON COLUMN USER_TEST.PHONE IS ' 연락처';
COMMENT ON COLUMN USER_TEST.ADDRESS IS ' 주소';
COMMENT ON COLUMN USER_TEST.STATUS IS ' 탈퇴여부';


DROP TABLE USER_TEST;

INSERT INTO USER_TEST
VALUES (1, 'user01', 'pass01', '880122-1234567', '남', '010-1111-9999', '서울시 강남구 역삼동', 'N');
INSERT INTO USER_TEST
VALUES (2, 'user02', 'pass02', '890222-2234567', '여', '010-2222-9999', '서울시 강남구 삼성동', 'N');
INSERT INTO USER_TEST
VALUES (3, 'user03', 'pass03', '900322-2234567', '여', '010-3333-9999', '서울시 강남구 청담동', 'Y');
INSERT INTO USER_TEST
VALUES (4, 'user04', 'pass04', '910422-1234567', '남', '010-4444-9999', '서울시 강남구 도곡동', 'N');
INSERT INTO USER_TEST
VALUES (5, 'user05', 'pass05', '920522-2234567', '여', '010-5555-9999', '서울시 강남구 대치동', 'N');

SELECT * FROM USER_TEST;

COMMIT;

태그:

카테고리:

업데이트:

댓글남기기

관련 글 더보기