오라클 DB 설계 및 활용 2일째 2번째 내용

*** SQL문 기능에 따른 분류
DML문: SELECT, INSERT, UPDATE, DELETE....
DDL문: CREATE, ALTER, DROP....
DCL문: GRANT, REVOKE.....

*** 무결성 제약 조건: 잘못된 자료의 입력을 막고자 다양한 입력 제한 조건을 줄 수 있다.
- DOMAIN 제약조건(INTEGRATY): 레코드 생성 시 각 컬럼의 이름과 성격, 크기, NULL
- 기본키 제약 조건: PRIMARY KEY
- 사용자 정의 제약 조건: UNIQUE, CHECK, FOREIGN KEY...

*** 기본키(PK) 제약조건: 중복 레코드 입력 방지가 목적, NULL을 허용하지 않는다.
방법1) 컬럼 레벨
CREATE TABLE AA(BUN NUMBER PRIMARY KEY, IRUM CHAR(10));

* 제약조건을 확인하기 위한 쿼리문
SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='AA';
(오라클에서만 쓰인다)

DROP TABLE AA; AA테이블을 지웠습니다.

방법2) 이름을 직접 부여한 컬럼 레벨
CREATE TABLE AA(BUN NUMBER CONSTRAINT AA_BUN_PK PRIMARY , IRUM CHAR(10));
(테이블을 만드는 사람이 제약 조건을 만들어 주었음)

방법3) 테이블 레벨
CREATE TABLE AA(BUN NUMBER , IRUM CHAR(10), CONSTRAINT AA_BUN_PK PRIMARY KEY(BUN));
(테이블을 만드는 사람이 제약 조건을 만들어 주었음)

방법4) 제거 및 추가
ALTER TABLE AA DROP CONSTRAINT AA_BUN_PK; <-- 제약 조건 제거
만약 테이블 작성시 제약 조건을 주지 않은 경우 나중에 추가
ALTER TABLE AA ADD CONSTRAINT AA_BUN_PK; <-- 제약 조건 추가

-- 컬럼의 비활성화 및 활성화
ALTER TABLE 테이블명 DISABLE [ENABLE] CONSTRAINT 제약조건명
DROP TABLE AA;

-- CHECK 제약조건: 입력받는 자료의 특정 칼럼값 검사.
CREATE TABLE AA(BUN NUMBER PRIMARY KEY, IRUM CHAR(10) NOT NULL, NAI NUMBER(2) CHECK(NAI>=20));

-- 확인해 봅시다 --
SELECT TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='AA';

INSERT INTO AA VALUES(100,'홍길동',32); <-자료가 들어감
INSERT INTO AA VALUES(101,'변순규',12); <-나이 제약에 걸림 자료가 안 들어감
DROP TABLE AA;

-- UNIQUE 제약 조건: 특정 칼럼의 동일한 값에 대한 입력 불허(보조식별자)
(( 주 식별자 = PRIMARY KEY, 보조식별자 = UNIQUE)
CREATE TABLE AA(BUN NUMBER PRIMARY KEY, IRUM CHAR(10) NOT NULL UNIQUE);

INSERT INTO AA VALUES(10,'한국인');
INSERT INTO AA VALUES(20,'한국인'); -- 오류

-- 참조키(외부키, FOREIGN KEY, FK) 제약 조건: 다른 테이블의 컬럼값을 참조(FK의 대상컬럼은 PK) -> 오라클 9i부터 된다. (권장 안함)
- ON DELETE CASCADE: 부모테이블의 관련행이 삭제 되면, 자식행의 자료도 같이 삭제(권장x)
- ON UPDATE CASCADE: 부모테이블의 관련행이 수정되면, 자식행의 자료도 같이 수정(권장x)

CREATE TABLE JIKWON(BUN NUMBER PRIMARY KEY,IRUM VARCHAR2(10), BUSER CHAR(10));
INSERT INTO JIKWON VALUES(1,'한송이','인사과');
INSERT INTO JIKWON VALUES(2,'박치기','인사과');

JOIN 을 이용해서 나중에 직원과 가족을 묶어서 보고 싶을 때 서로에 공통된 자료가 없다면 묶을 수가 없다.

CREATE TABLE GAJOK(CODE NUMBER PRIMARY KEY, NAME VARCHAR2(10), BIRTH DATE, JIKWON_BUN NUMBER REFERENCES JIKWON(BUN));

INSERT INTO GAJOK VALUES(10,'한국인','2000-05-01',1);
INSERT INTO GAJOK VALUES(11,'신기해','1980-02-01',2);

SELECT * FROM GAJOK;
INSERT INTO GAJOK VALUES(12,'신선한','2001-02-01',5); -- FK에 관한 오류가 생긴다.

DELETE FROM GAJOK WHERE CODE=11; <-11번 자료를 지운다.
DELETE FROM JIKWON WHERE BUN=2; <- 2번 자료는 가족자료가 없어서 지워짐.
DELETE FROM JIKWON WHERE BUN=1; <- 가족 자료가 남아 있어서 지우지 못한다. 오류임!!

2차는 여기 까지 하게 되었네요
***********************************************************

-- 다른 방법 : 테이블 수준
CREATE TABLE GAJOK(CODE NUMBER, NAME VARCHAR2(10), BIRTH DATE, JIKWON_BUN NUMBER, CONSTRAINT GAJOK_CODE_PK PRIMARY KEY(CODE), CONSTRAINT GAJOK_JIKWON_BUN_FK FOREIGN KEY(JIKWON_BUN) REFERENCES JIKWON(BUN));


그리고 오늘의 문제

교수 -> 과목 -> 학생 의 테이블을 만들어 각각 지정하고 자료를 입력하라.

CREATE TABLE GYOSU(GCODE NUMBER PRIMARY KEY, GNAME VARCHAR2(10), GEDU VARCHAR2(6));
GYOSU 라는 테이블을 만들었다. GCODE(PK), GNAME, GEDU 생성
CREATE TABLE CLASSS(CCODE NUMBER PRIMARY KEY, CNAME VARCHAR2(10), CBOOK VARCHAR2(6), GCODE NUMBER REFERENCES GYOSU(GCODE));
CLASSS 라는 테이블을 만들었다. CCODE(PK), CNAME, CBOOK, GCODE(FK) 생성
CREATE TABLE HAKSENG(HCODE NUMBER PRIMARY KEY, HNAME VARCHAR2(10), CCODE NUMBER REFERENCES CLASSS(CCODE));
HAKSENG 라는 테이블을 만들었다. HCODE(PK), HNAME, CCODE(FK) 생성

순서에 맞게 자료를 입력 하겠습니다. GYOSU 자료 입력
INSERT INTO GYOSU VALUES(1,'일국인','신1호');
INSERT INTO GYOSU VALUES(2,'이국인','신2호');
INSERT INTO GYOSU VALUES(3,'삼국인','신3호');
INSERT INTO GYOSU VALUES(4,'사국인','신4호');
순서에 맞게 자료를 입력 하겠습니다. CLASSS 자료 입력
INSERT INTO CLASSS VALUES(1,'일어','일어란', 4);
INSERT INTO CLASSS VALUES(2,'영어','영어란', 3);
INSERT INTO CLASSS VALUES(3,'IT','IT란', 2);
INSERT INTO CLASSS VALUES(4,'문학','문학란', 1);
순서에 맞게 자료를 입력 하겠습니다. HAKSENG 자료 입력
INSERT INTO HAKSENG VALUES(100,'일학생',3);
INSERT INTO HAKSENG VALUES(101,'이학생',2);
INSERT INTO HAKSENG VALUES(102,'삼학생',1);
INSERT INTO HAKSENG VALUES(103,'사학생',4);

이 정도 하게 되면 오늘한 것 까지는 됐네요.
이제 추가적으로 JOIN 이라는 것 배워서 합치는 법 배우면 되겠네요.^^
오츠까레 사마 데시따..

*** 3차는 여기 까지 입니다. 둘째날은 끝났습니다.ㅁ^^