20201027_ 23 문제 및 해결2

1

CREATE USER test02 IDENTIFIED 1234; 

원인 : by누락
조치

CREATE USER test02 IDENTIFIED BY 1234; 

2

GRANT CONNECT,RESOURCE FROM test02; 

원인 : FROM을 TO로 바꿔야한다
조치

GRANT CONNECT,RESOURCE TO test02; 

3
BOARD_NO 컬럼에 PRIMARY KEY 제약조건 설정.

CREATE TABLE MEMBERS ( 
    MEMBER_ID VARCHAR2(20) PRIMARY KEY, 
    MEMBER_PWD VARCHAR2(20) NOT NULL, 
    MEMBER_NAME VARCHAR2(30), 
    MEMBER_AGE NUMBER, 
    MEMBER_EMAIL VARCHAR2(50), 
    CONSTRAINT   UNIQUEMEM_EMAIL_UNQ (MEMBER_EMAIL) 
); 

원인 : 제약조건이름은 CONSTRAINT다음에 와야함
조치

    CONSTRAINT  MEM_EMAIL_UNQ UNIQUE(MEMBER_EMAIL) 

4
BOARD_WRITER 컬럼에 MEMERS 테이블의 MEMBER_ID를 참조하는 FOREIGN KEY 제약조건을 설정하되 부모 테이블 데이터 삭제 시 참조하고 있는 자식 테이블의 데이터도 삭제하는 옵션 추가.

CREATE TABLE BOARDS ( 
    BOARD_NO NUMBER PRIMARY KEY, 
    BOARD_TITLE VARCHAR2(100), 
    BOARD_CONTENT VARCHAR2(5000), 
    BOARD_WRITER VARCHAR2(20) REFERENCES MEMBERS(MEMBER_ID) , 
    BOARD_REG_DATE DATE DEFAULT SYSDATE 
); 

원인 : 삭제정책안달아둠, VARCHAR2 크기가 4000초과
조치
   

BOARD_WRITER VARCHAR2(20) REFERENCES MEMBERS(MEMBER_ID) ON DELETE CASCADE, 
BOARD_CONTENT VARCHAR2(455), 

---

1

CREATE USER IDENTIFIED BY test03,1234; 

원인 : 구문지정 오류 USER뒤에 계정명이 와야함
조치

CREATE USER test03 IDENTIFIED BY 1234; 

2

REVOKE CONNECT,RESOURCE TO test03; 

원인 : 권한을 부여하려면 GRANT를 사용해야함
조치

GRANT CONNECT,RESOURCE TO test03; 

3

CREATE TABLE MEMBERS( 
    MEMBER_ID VARCHAR2(20) PRIMARY KEY, 
    MEMBER_PWD VARCHAR2(20) NOT NULL, 
    MEMBER_NAME VARCHAR2(30) UNIQUE, 
    MEMBER_AGE NUMBER, 
    MEMBER_EMAIL VARCHAR2(50) UNIQUE, 
); 

원인 : UNIQUE 복합키 설정을 테이블 레벨에서 해줘야함
조치

CREATE TABLE MEMBERS( 
    MEMBER_ID VARCHAR2(20) PRIMARY KEY, 
    MEMBER_PWD VARCHAR2(20) NOT NULL, 
    MEMBER_NAME VARCHAR2(30), 
    MEMBER_AGE NUMBER, 
    MEMBER_EMAIL VARCHAR2(50) , 
    UNIQUE (MEMBER_NAME, MEMBER_EMAIL) 
); 

4

CREATE TABLE BOARDS( 
    BOARD_NO NUMBER PRIMARY KEY, 
    BOARD_TITLE VARCHAR2(100), 
    BOARD_CONTENT VARCHAR2(4000), 
    BOARD_WRITER VARCHAR2(20) REFERENCES MEMBERS(MEMBER_ID) ON DELETE CASCADE NULL, 
    BOARD_REG_DATE VARCHAR2(30) DEFAULT SYSDATE 
); 

원인 :ON DELETE CASCADE NULL를 ON DELETE SET NULL로
조치
 

 BOARD_WRITER VARCHAR2(20) REFERENCES MEMBERS(MEMBER_ID) ON DELETE SET NULL, 

원인 : VARCHAR2(20) 을 DATE로 바꿔야함
조치

    BOARD_REG_DATE DATE(30) DEFAULT SYSDATE