국비 교육 37일차 - DML, 제약조건

계속해서 오라클 배우는 중이다. 다행스럽세도 SQL문은 배울만해서 진도가 빨리 나가도 부담스럽지는 않다 

그럼 오늘도 다시 복습

 

-- 2021-02-17 DML (6장)
/*
     1. DML (데이터 조작언어) 
        = SELECT (데이터 검색)
        형식)
              SELECT * | column1,column2
              FROM table_name|view_name|SELECT~
              [
                 WHERE 컬럼명(함수) 연산자 값
                 GROUP BY 컬럼명(함수)
                 HAVING 그룹 조건
                 ORDER BY 컬럼명(함수명) ASC|DESC
              ]
        = INSERT (데이터 추가)
        형식)
              1) 컬럼 전체 값을 채우는 방식 
                 = 컬럼의 데이터형 (숫자:10,20..,문자:' ',날짜:'YY/MM/DD',SYSDATE) => '' ,' '
                                                              ===  ===
                                                              NULL 공백
                 INSERT INTO table_name(view_name)(*) VALUES(값1,값2,값3..:테이블이 가지고 있는 갯수만큼)
                                        
              2) 필요한 컬럼만 채우는 방식 
                 INSERT INTO table_name(컬럼1,컬럼2...) VALUES(컬럼값1,컬럼값2...)
        = UPDATE (데이터 수정)
        형식) 
              UPDATE table_name SET
              컬럼명=값,컬럼명=값,컬럼명=값... 
              ============================= 전체 데이터 변경 
              [WHERE 조건] ================ 수정이 필요한 부분 변경 
        = DELETE (데이터 삭제)
        형식) 
             DELETE FROM table_name
             [WHERE 조건]
        = 테이블은 기본적으로 중복이 없는 데이터를 가지고 있어야 한다 (데이터 무결성 : PRIMARY KEY)
                           =============== ID,그외는 숫자
          이상현상 : 수정,삭제 => 원하지 않는 데이터 변경
          
        1) 테이블 만들기 
           = 방법 
             테이블명 설정 
             1. 알파벳(한글)으로 시작한다 
             2. 숫자를 사용 할 수 있다 (앞에 사용 금지)
             3. 키워드는 사용이 가능(가급적이면 사용 금지 권장) : SELECT , FROM ,ORDER BY
             4. 특수문자 사용 가능( _ , $ , # ) => _ (임시테이블,두개이상 단어) 
                => 오라클은 대소문자 구분을 하지 않는다(단 저장된 데이터는 대소문자 구분)
                => 오라클에 테이블명은 자동으로 대문자로 저장된다 
             5. 테이블명,컬럼명은 30byte까지 사용이 가능 (한글은 15자) => 5~10
             데이터형 
             1. 문자형 ==> 자바(String)
                CHAR(1~2000byte) : 고정 바이트 
                VARCHAR2(1~4000byte) : 가변 바이트 
                CLOB 4GB : 가변 바이트 
             2. 숫자형
                NUMBER(1~38,~127)
                정수 : NUMBER , NUMBER(자리수) => int , long
                실수 : NUMBER(자리수,소수점)    => double
             3. 날짜형 ==> java.util.Date
                DATE : 시스템의 날짜,시간.. 
                TIMESTAMP : 기록 
             형식
             *** table_name은 유일값이다 (중복된 이름을 사용하면 않된다) => 한개의 데이터베이스안에서(XE)
             *** 테이블명 확인 
                 SELECT * FROM tab;
             *** 테이블 구조 확인 
                 DESC table_name
             *** 변경 , 삭제 => ALTER , DROP , 리팩토링:RENAME(컬럼명,테이블명)
                              ======
                              컬럼변경(MODIFY), 컬럼삭제(DROP COLUMN) ,컬럼 추가(ADD)
             *** 가상테이블 = WITH
             === 기존의 테이블을 재사용 
             CREATE TABLE table_name
             AS (SELECT ~ )
             CREATE TABLE table_name
             AS (SELECT ~ WHERE 1=2)
                                ==== false이면 테이블 구조
             === 새로운 테이블 제작
             CREATE TABLE table_name
             (
                 컬럼명 데이터형 [제약조건], => 컬럼레벨
                 컬럼명 데이터형 [제약조건],
                 컬럼명 데이터형 [제약조건],
                 컬럼명 데이터형 [제약조건],
                 컬럼명 데이터형 [제약조건],
                 ...,
                 [제약조건] => 테이블레벨
             );
  
*/
-- 테이블 생성 
/*
     게시판 
      = 게시물번호 (중복없는 데이터) => MAX , SEQUENCE  => NUMBER (자리수를 정하지 않는 경우 14)
      = 이름 : VARCHAR2(34)
      = 제목 : VARCHAR2(4000)
      = 내용 : CLOB
      = 비밀번호 : VARCHAR2(10)
      = 작성일 : DATE
      = 조회수 NUMBER(7)
      = 좋아요 : NUMBER(7)
*/
CREATE TABLE freeboard(
   no NUMBER,
   name VARCHAR2(34),
   subject VARCHAR2(4000),
   content CLOB,
   pwd VARCHAR2(10),
   regdate DATE DEFAULT SYSDATE,
   hit NUMBER(7) DEFAULT 0,
   like1 NUMBER(7) DEFAULT 0
);

-- 구조 확인 
DESC freeboard;
-- 데이터 추가(전체값 추가)
INSERT INTO freeboard VALUES(1,'홍길동','DML의 INSERT수업중...','데이터 추가하는 연습..','1234',SYSDATE,0,0);
COMMIT; -- 실제로 메모리에 저장 (확정)
SELECT * FROM freeboard;
-- 데이터 일부만 추가 => DEFAULT가 적용 
INSERT INTO freeboard(no,name,subject,content,pwd) 
VALUES(2,'심청이','DEFAULT 적용..','DEFAULT는 컬럼을 지정해야 적용','1234');
COMMIT;
-- 3 , 4 
INSERT INTO freeboard(no,name,subject,content,pwd)
VALUES((SELECT NVL(MAX(no)+1,1) FROM freeboard),'박문수','자동 증가 번호 이용','MAX()+1=SEQUENCE','1234');
COMMIT;
-- 자동 증가 번호 생성 
-- 첫번째 데이터 없는 경우  => no : NULL 
-- SELECT NVL(MAX(no)+1,1) FROM freeboard;
-- 반드시 (DML을 사용시에는 COMMIT을 사용한다) => COMMIT이 없는 상태에서는 임시기억 
-- INSERT 방식 , INSERT , INSERT ALL
-- CREATE , TRUNCATE , DROP , ALTER , RENAME => 자동 저장 (COMMIT을 사용할 필요가 없다)
-- INSERT , UPDATE , DELETE => COMMIT을 수행 
CREATE TABLE emp_insert
AS SELECT empno,ename,job,hiredate,sal 
   FROM emp
   WHERE 1=2;

INSERT INTO emp_insert
SELECT empno,ename,job,hiredate,sal FROM emp;

SELECT * FROM emp_insert;

CREATE TABLE emp_10
AS 
  SELECT * FROM emp WHERE 1=2;
  
CREATE TABLE emp_20
AS
  SELECT * FROM emp WHERE 2=3;

CREATE TABLE emp_30
AS
  SELECT * FROM emp WHERE 100=200;

-- 분기별 실적 
INSERT ALL
  WHEN deptno=10 THEN
   INTO emp_10 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
  WHEN deptno=20 THEN
   INTO emp_20 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
  WHEN deptno=30 THEN
   INTO emp_30 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
SELECT * FROM emp;

SELECT * FROM emp_10;
SELECT * FROM emp_20;
SELECT * FROM emp_30;

DROP TABLE emp_insert;
DROP TABLE emp_10;
DROP TABLE emp_20;
DROP TABLE emp_30;
DROP TABLE emp1;
DROP TABLE op1;
DROP TABLE op2;
DROP TABLE table1;
DROP TABLE table2;
DROP TABLE zipcode1;

-- genie_music ==> 두개를 따로 저장  music_top200 , music_genre => INSERT ALL
DESC genie_music;
-- 구조
CREATE TABLE music_top200
AS
 SELECT no,title,singer,album,poster,state,idcrement 
 FROM genie_music 
 WHERE 1=2;
 
CREATE TABLE music_genre
AS
 SELECT no,title,singer,album,poster,state,idcrement 
 FROM genie_music 
 WHERE 1=2;

-- 값을 채운다 
INSERT ALL
  WHEN cno=1 THEN
   INTO music_top200 VALUES(no,title,singer,album,poster,state,idcrement)
  WHEN cno=2 THEN
   INTO music_genre VALUES(no,title,singer,album,poster,state,idcrement)
SELECT no,cno,title,singer,album,poster,state,idcrement 
FROM genie_music;

SELECT * FROM music_top200;
SELECT * FROM music_genre;

-- emp에서 입사년도별로 따로 저장 
DESC emp;
SELECT DISTINCT TO_CHAR(hiredate,'YYYY') FROM emp;

-- emp1980 , emp1981 , emp1982 , emp1983
CREATE TABLE emp1980
AS
 SELECT * FROM emp WHERE 1=2;
CREATE TABLE emp1981
AS
 SELECT * FROM emp WHERE 1=2;
CREATE TABLE emp1982
AS
 SELECT * FROM emp WHERE 1=2;
CREATE TABLE emp1983
AS
 SELECT * FROM emp WHERE 1=2;
 
-- 데이터 첨부 ==> 
INSERT ALL
 WHEN TO_CHAR(hiredate,'YYYY')=1980 THEN
   INTO emp1980 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
 WHEN TO_CHAR(hiredate,'YYYY')=1981 THEN
   INTO emp1981 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
 WHEN TO_CHAR(hiredate,'YYYY')=1982 THEN
   INTO emp1982 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
 WHEN TO_CHAR(hiredate,'YYYY')=1983 THEN
   INTO emp1983 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
SELECT * FROM emp;
SELECT * FROM emp1983;

CREATE TABLE empTotal
AS
 SELECT * FROM emp WHERE 1=2;
DESC empTotal;
MERGE INTO empTotal et
USING emp1983 e1
ON (et.empno=e1.empno)
WHEN MATCHED THEN
  UPDATE SET et.ename=e1.ename
WHEN NOT MATCHED THEN
INSERT VALUES(e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.comm,e1.deptno);

SELECT * FROM empTotal;

 INSERT, UPDATE, SELECT, DELETE와 같은 데이터를 변경해주는 언어를 DML이라고 한다. SELECT는 사실 이전부터 많이 연습해왔기 때문에 오늘 많이 배운건 INSERT, DELETE 위주였다. 

 

INSERT는 뒤에 첨부해줘야할 데이터들을 컬럼 순서에 맞게 작성해줘야 한다. 단 DEFAULT값이 있어서 따로 적어두지 않아도 되는 부분은 테이블을 생성할때 DEFAULT로 설정해두고 생성하면 된다. 

 

INSERT INTO freeboard(no,name,subject,content,pwd)
VALUES((SELECT NVL(MAX(no)+1,1) FROM freeboard),'박문수','자동 증가 번호 이용','MAX()+1=SEQUENCE','1234');
COMMIT;

위 코드 처럼 필요한 부분만 집어 넣어주겠다고 미리 선언 한 뒤,  VALUES 뒤에 데이터들을 채워 넣어주면 된다 

참고로 가장 첫번째 코드는 자동증가 번호 생성 코드이다. 

SELECT NVL(MAX(no)+1, 1)이라고 써주면 입력되어 있는 no중에서 가장 큰값에 항상 1을 더해주고,

NULL이면 1을 채워주는 NVL코드를 사용해서 만들어주면 된다. 

 

-- UPDATE (데이터 수정)
/*
     형식) 
          UPDATE table_name SET
          컬럼명=값,컬럼명=값...
          [WHERE 조건]
*/
-- View 제작 (가상테이블)
CREATE TABLE student(
   name VARCHAR2(34),
   kor NUMBER(3),
   eng NUMBER(3),
   math NUMBER(3),
   hap NUMBER(3) GENERATED ALWAYS AS(kor+eng+math) virtual,
   avg NUMBER(5,2) GENERATED ALWAYS AS((kor+eng+math)/3) virtual
);
-- 데이터 추가
INSERT INTO student(name,kor,eng,math) VALUES('홍길동',89,78,90);
INSERT INTO student(name,kor,eng,math) VALUES('심청이',80,90,60);
INSERT INTO student(name,kor,eng,math) VALUES('박문수',70,88,95);
INSERT INTO student(name,kor,eng,math) VALUES('박문수',75,90,78);
COMMIT;
-- 출력 이름,국어,영어,수학,총점,평균(소수점 2자리),등수
SELECT student.*,(kor+eng+math) "total",ROUND((kor+eng+math)/3,2) "avg",
       RANK() OVER(ORDER BY (kor+eng+math) DESC) "rank"
FROM student;
-- 수정 
UPDATE student SET
kor=80,eng=78,math=90;
ROLLBACK; -- UPDATE명령어 취소 
UPDATE student SET
kor=80,eng=78,math=90
WHERE name='심청이';
COMMIT;
-- 테이블 => 중복이 없는 데이터가 반드시 필요
UPDATE student SET
kor=90,eng=90,math=60 -- =는 대입연산자
WHERE name='박문수'; -- =비교연산자
ROLLBACK;

DROP TABLE student;


-- DELETE (데이터 삭제) => COMMIT(정상 저장) , ROLLBACK (명령어 취소)
/*
    형식) 
       DELETE FROM table_name -- 데이터 전체 삭제 => TRUNCATE(COMMIT을 포함)
       [WHERE 조건] -- 원하는 데이터만 삭제
       
       => INSERT => COMMIT
       => DELETE => ROLLBACK
       => SELECT
*/
DELETE FROM student;
SELECT * FROM student;
ROLLBACK;

-- 홍길동 삭제
DELETE FROM student
WHERE name='홍길동';
COMMIT;
-- 280 page (read only) : 일기 전용 테이블 (INSERT,UPDATE,DELETE는 사용 금지 , SELECT만 사용이 가능)
-- 282 page (가상 컬럼) : hap NUMBER(3) GENERATED ALWAYS AS(kor+eng+math) virtual,
-- 296 page (INSERT) : 전체 table_name, 필요한 데이터 첨부 table_name(컬럼...)
-- 301 page : 서브쿼리를 이용해서 데이터 첨부 
/*
     INSERT INTO emp1
     SELECT ~
*/
-- 302 page : 테이블을 나눠서 처리  INSERT ALL
-- 305 page : UPDATE 
-- 306 page : DELETE
-- 문법 형식 
-- INSERT : 1. 날짜 , 2. NULL값 첨부 
-- 날짜 : SYSDATE => '21/02/18'
CREATE TABLE reserve(
   id VARCHAR2(10),
   regdate DATE,
   seat VARCHAR2(10)
);

INSERT INTO reserve VALUES('hong',SYSDATE,NULL);

INSERT INTO reserve VALUES('shim',SYSDATE,'');

INSERT INTO reserve VALUES('park','21/02/20',NULL);
SELECT * FROM reserve;

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
ALTER SESSION SET NLS_DATE_FORMAT='YY/MM/DD';
SELECT * FROM emp;

UPDATE는 테이블 내의 데이터를 수정해줄 수 있다. 

UPDATE student SET
kor=80,eng=78,math=90;

 근데 이렇게 써주면 테이블에 있는 모든 ROW의 데이터들이 바뀌게 된다. 

그래서 꼭 WHERE 조건절을 걸어줘서, 필요한 데이터만 변경할 수 있도록 해야한다.

UPDATE student SET
kor=80,eng=78,math=90
WHERE name='심청이';

DELETE역시 마찬가지이다. UPDATE와 DELETE모두 COMMIT을 하기 전에는 ROLLBACK이 가능하기 때문에 이것을 잘 생각해서 코딩해주면 된다. 

 

그 다음에 배운 것은 제약조건이다. 

/*
    DML (CRUD)
      = INSERT
        형식)
             INSERT INTO table_name VALUES(값1....) => 모든 컬럼에 값을 설정
             INSERT INTO table_name(컬럼명...) VALUES(설정된 컬럼의 갯수만큼) => 필요한 데이터 추가
             *** NULL값을 설정 (NULL,'')
             *** 문자열,날짜일 경우 : '' , 숫자 : 일반
      = UPDATE
        형식) 
             ==============================
             UPDATE table_name SET
             컬럼명=값대입,컬럼명=값대입...
             ============================== 전체 수정 (동일한 급여 인상)
             [WHERE 조건]      ============= 필요한 데이터 변경 
      = DELETE
        형식)
             DELETE FROM table_name
             [WHERE 조건]
      = MERGE (병합)
*/
CREATE TABLE emp10
AS
  SELECT * FROM emp WHERE 1=2;

CREATE TABLE emp20
AS
  SELECT * FROM emp WHERE 2=3;

CREATE TABLE emp30
AS
  SELECT * FROM emp WHERE 3=4;
  
CREATE TABLE empAll
AS
  SELECT * FROM emp WHERE 4=5;
  
-- 값을 채운다 (입고,출고,재고) => 파티션 테이블 
INSERT ALL
 WHEN deptno=10 THEN
  INTO emp10 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
 WHEN deptno=20 THEN
  INTO emp20 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
 WHEN deptno=30 THEN
  INTO emp30 VALUES(empno,ename,job,mgr,hiredate,sal,comm,deptno)
SELECT * FROM emp;

SELECT * FROM emp10;
SELECT * FROM emp20;
SELECT * FROM emp30;
/*
   병합 (ERP) : 관리 (인사관리,급여관리,물류관리...매장관리)
   형식)
        MERGE INTO table1 별칭 => 저장할 테이블 
        USING table2 별칭 => 데이터를 가지고 있는 테이블 
        ON(조건)
        WHEN MATCHED THEN (조건이 TRUE처리)
          UPDATE,DELETE
        WHEN NOT MATCHED THEN (조건이 FALSE처리)
          INSERT
*/
 MERGE INTO empAll ea
 USING emp30 e10
 ON(ea.empno=e10.empno)
 WHEN MATCHED THEN
   UPDATE SET ea.ename=e10.ename
 WHEN NOT MATCHED THEN
   INSERT VALUES(e10.empno,e10.ename,e10.job,e10.mgr,e10.hiredate,e10.sal,e10.comm,e10.deptno);
   
SELECT * FROM empAll;

-- 2021.02.17 제약조건 (basic)
/*
     제약조건의 종류 : 데이터를 제어 필요한 데이터만 저장할 수 있게 만드는 과정 
     ======= 정형화된 데이터 
       *** 데이터 종류
       1. 정형화 데이터 (구분이 되어 있다 , 필요한 데이터만 저장이 되어 있다) : RDBMS
       2. 반정형화 데이터 (구분이 되어 있다) : HTML,XML,JSON
       3. 비정형화 데이터 : txt , twitter 댓글 ㅎㅎ ,ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ
          =============
            필요한 데이터를 추출 => 정형화된 데이터로 변경 ==> 컴퓨터에 인식 (AI)
    1) 제약조건의 종류
       = NOT NULL : NULL을 허용하지 않는다 (빨간색 * 필수입력, 다이얼로그) => nn
       = UNIQUE : 중복이 없는 데이터 설정 (단점: NULL값을 허용) => 후보키(ID를 잃어버렸을 경우 대체) => uk
                이메일 , 전화번호 
       = PRIMARY KEY : NOT NULL + UNIQUE (기본키) => 모든 테이블에 기본키는 반드시 설정을 권장
                       여러개 설정이 가능 (기본 1개)
       = FOREIGN KEY : 참조키 (외래키) 다른 테이블의 값을 사용하기 위한 키 
       = CHECK : 필요한 데이터만 사용이 가능하게 지정  sex:남자,여자 , 부서명 ,근무지 (라디오,콤보)
       = DEFAULT : INSERT시에 자동으로 첨부되는 값 
    2) 제약조건 설정 방법 
       = 컬럼 뒤에 설정 
       = 테이블 뒤에 설정 
       CREATE TABLE table_name
       (
           컬럼명 데이터형 [제약조건] => 여러개를 설정 할 수 있다 
           컬럼명 데이터형 [제약조건]
           컬럼명 데이터형 (NULL을 허용),
           [제약조건]
       )
       ==> NOT NULL이나 DEFAULT는 컬럼 뒤에 설정 
       ==> 나머지 조건은 컬럼 생성후에 나중에 제약조건을 설정 할 수 있다
       1. NOT NULL 
          컬럼명 데이터형 NOT NULL
          컬럼명 데이터형 CONSTRAINT 제약조건명 NOT NULL => 수정 권장
          name VARCHAR2(34) CONSTRAINT member_name_nn NOT NULL
                                       ====== ==== ==
                                       테이블명 컬럼명 속성  ==> 같은 데이터베이스안에서는 
                                                          같은 제약조건명을 사용할 수 없다
        2. UNIQUE 
           컬럼명 데이터형 UNIQUE
           컬럼명 데이터형,
           CONSTRAINT member_tel_uk UNIQUE(tel)  => tel이 ID를 잃어버리면 ID찾는 컬럼(후보키)
           
        3. PRIMARY KEY 
           컬럼명 데이터형 PRIMARY KEY
           컬럼명 데이터형,
           CONSTRAINT member_id_pk PRIMARY KEY(id)
           
        4. FOREIGN KEY 
           컬럼명 데이터형,
           CONSTRAINT board_name_fk FOREIGN KEY(name)
           REFERENCES member(name)
        
        5. CHECK 
           컬럼명 데이터형 CHECK(sex IN('남자','여자'))
           컬럼명 데이터형,
           CONSTRAINT member_sex_ck CHECK(sex IN('M','W'))
           
        6. DEFAULT 
           컬럼명 테이터형 DEFAULT 값(데이터형을 맞게 설정)
           
        약자 
          PK(Primary Key)
          FK(Foreign Key)
          CK(CHECK)
          UK(UNIQUE)
          NN(NOT NULL)
    3) 제약조건 수정  
*/
/*
     댓글형 게시판 
     ===========
     
     게시판       
      = 번호 PK
      = 이름 NN
      = 제목 NN
      = 내용 NN
      = 비밀번호 NN (본인 여부 확인)
      = 등록일 DEFAULT 
      = 조회수 DEFAULT
      
     댓글
      = 번호 PK
      = 게시판번호 FK
      = ID NN
      = 이름 NN 
      = 댓글내용 NN
      = 작성일 DEFAULT 
*/
-- NOT NULL은 중복허용(X)
CREATE TABLE replyBoard(
   no NUMBER PRIMARY KEY,
   name VARCHAR2(34) NOT NULL,
   subject VARCHAR2(2000) NOT NULL,
   content CLOB NOT NULL,
   pwd VARCHAR2(10) NOT NULL,
   regdate DATE DEFAULT SYSDATE,
   hit NUMBER DEFAULT 0
);
CREATE TABLE reply(
   no NUMBER PRIMARY KEY,
   bno NUMBER REFERENCES replyBoard(no),
   id VARCHAR2(20) NOT NULL,
   name VARCHAR2(34) NOT NULL,
   msg CLOB NOT NULL,
   regdate DATE DEFAULT SYSDATE
);
-- 데이터 추가 
-- 게시판 
INSERT INTO replyBoard(no,name,subject,content,pwd) VALUES(2,'','제약조건 수업중',
   '제약조건을 만드는 이유는 필요한 데이터만 입력을 받기 위한 방법','1234');
COMMIT;
SELECT * FROM replyBoard;
INSERT INTO reply VALUES(2,1,'hong','홍길동','댓글 등록',SYSDATE);
COMMIT;
SELECT * FROM reply;
DROP TABLE reply;
DROP TABLE replyBoard;
-- 제약조건의 이름을 부여 (권장)
-- DEFAULT , NOT NULL : 컬럼뒤에 설정 , 나머지 제약조건은 컬럼명 전체 선언후에 나중에 설정 
-- PRIMARY KEY는 한개만 설정할 수 있다 
-- table can have only one primary key
/*
DROP TABLE test;
CREATE TABLE test(
  no NUMBER,
  id VARCHAR2(20),
  CONSTRAINT test_no_id_pk PRIMARY KEY(no,id)
);
*/
CREATE TABLE replyBoard(
   no NUMBER,
   name VARCHAR2(34) CONSTRAINT rb_name_nn NOT NULL,
   subject VARCHAR2(2000) CONSTRAINT rb_subject_nn NOT NULL,
   content CLOB CONSTRAINT rb_content_nn NOT NULL,
   pwd VARCHAR2(10) CONSTRAINT rb_pwd_nn NOT NULL,
   regdate DATE DEFAULT SYSDATE,
   hit NUMBER DEFAULT 0,
   CONSTRAINT rb_no_pk PRIMARY KEY(no)
);
CREATE TABLE reply(
   no NUMBER,
   bno NUMBER,
   id VARCHAR2(20) CONSTRAINT reply_id_nn NOT NULL,
   name VARCHAR2(34) CONSTRAINT reply_name_nn NOT NULL,
   msg CLOB CONSTRAINT reply_msg_nn NOT NULL,
   regdate DATE DEFAULT SYSDATE,
   CONSTRAINT reply_no_pk PRIMARY KEY(no),
   CONSTRAINT reply_bno_fk FOREIGN KEY(bno)
   REFERENCES replyBoard(no)
);
/*
    타입 
    C : NOT NULL, CHECK
    P : PRIAMRY KEY
    R : FOREIGN KEY
    U : UNIQUE
*/
SELECT owner,constraint_name,constraint_type,status
FROM user_constraints
WHERE table_name='REPLY';

SELECT owner,constraint_name,table_name,column_name
FROM user_cons_columns
WHERE table_name='REPLYBOARD';

CREATE TABLE uniqueCheck(
   id VARCHAR2(20),
   sex VARCHAR2(10),
   email VARCHAR2(100),
   CONSTRAINT uc_id_pk PRIMARY KEY(id), -- NOT NULL+UNIQUE
   CONSTRAINT uc_sex_ck CHECK(sex IN('남자','여자')),
   CONSTRAINT uc_email_uk UNIQUE(email) -- NULL 허용 
);

INSERT INTO uniquecheck VALUES('hong','남자','');
-- 제약조건을 추가 
ALTER TABLE dept ADD CONSTRAINT dept_dpetno_pk PRIMARY KEY(deptno);
DROP TABLE tcons;
-- FOREIGN KEY => 참조시에 반드시 PRIMARY KEY를 참조 
CREATE TABLE tcons(
  no NUMBER(5),
  name VARCHAR2(20) CONSTRAINT tcons_name_nn NOT NULL,
  jumin VARCHAR2(13) CONSTRAINT tcons_jumin_nn NOT NULL,
  area NUMBER(1),
  deptno NUMBER(2),
  CONSTRAINT tcons_no_pk PRIMARY KEY(no),
  CONSTRAINT tcons_jumin_uk UNIQUE(jumin),
  CONSTRAINT tcons_area_ck CHECK(area BETWEEN 1 AND 4), -- CHECK(area IN(1,2,3,4))
  CONSTRAINT tcons_deptno_fk FOREIGN KEY(deptno)
  REFERENCES dept(deptno)
);
DROP TABLE member;

CREATE TABLE member(
   id VARCHAR2(20),
   pwd VARCHAR2(10) CONSTRAINT member_pwd_nn NOT NULL,
   name VARCHAR2(34) CONSTRAINT member_name_nn NOT NULL,
   sex VARCHAR2(6),
   email VARCHAR2(200),
   addr1 VARCHAR2(100) CONSTRAINT member_addr1_nn NOT NULL,
   addr2 VARCHAR2(100),
   tel1 VARCHAR2(10),
   tel2 VARCHAR2(20),
   interest VARCHAR2(100),
   content CLOB CONSTRAINT member_content_nn NOT NULL,
   CONSTRAINT member_id_pk PRIMARY KEY(id),
   CONSTRAINT member_sex_ck CHECK(sex IN('남자','여자')),
   CONSTRAINT member_email_uk UNIQUE(email),
   CONSTRAINT member_tel1_ck CHECK(tel1 IN('SK','KT','LG'))
);

 제약조건 자체를 걸어주는 방법은 그렇게 어렵지는 않았는데, PRIMARY KEY, FOREIGM KEY, UNIQUE, NOT NULL, DEFAULT가 각각 언제 사용되는지는 암기하고 있어야 데이터에 맞게 테이블을 짜줄 수 있었다. 

 

단,

CREATE TABLE member(
   id VARCHAR2(20),
   pwd VARCHAR2(10) CONSTRAINT member_pwd_nn NOT NULL,
   name VARCHAR2(34) CONSTRAINT member_name_nn NOT NULL,
   sex VARCHAR2(6),
   email VARCHAR2(200),
   addr1 VARCHAR2(100) CONSTRAINT member_addr1_nn NOT NULL,
   addr2 VARCHAR2(100),
   tel1 VARCHAR2(10),
   tel2 VARCHAR2(20),
   interest VARCHAR2(100),
   content CLOB CONSTRAINT member_content_nn NOT NULL,
   CONSTRAINT member_id_pk PRIMARY KEY(id),
   CONSTRAINT member_sex_ck CHECK(sex IN('남자','여자')),
   CONSTRAINT member_email_uk UNIQUE(email),
   CONSTRAINT member_tel1_ck CHECK(tel1 IN('SK','KT','LG'))
);

  이런식으로 CONSTRAINT를 써줘야 제약조건을 나중에 지워줄 수 있다. 

오늘의 복습 끝