cmd에서 테이블 스페이스 추가

> sqlplus

> system/1234 => 시스템 계정으로 로그인
> create user c##cat identified by 1234;
> grant connect, resource to c##cat;

❗❗❗이후 SQL Developer, system계정으로 로그인하여 다음 코드를 입력 해줘야 해주어야 한다.

ALTER USER C##cat DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS; commit;

oracle에서 테스트

CREATE TABLE MEMBER (
    ID VARCHAR2(50) PRIMARY KEY,
    NAME VARCHAR2(50) UNIQUE,
    PW VARCHAR2(200) NOT NULL,
    EMAIL VARCHAR2(200) NOT NULL,
    AUTH NUMBER DEFAULT 0,
    IMGO VARCHAR2(200) DEFAULT 'default.png',
    IMGS VARCHAR2(200) DEFAULT 'default.png'
);

CREATE TABLE BOARD (
    NO NUMBER PRIMARY KEY,
    NAME VARCHAR2(50),
    CATE VARCHAR2(50),
    TITLE VARCHAR2(500),
    CONTENT VARCHAR2(2000),
    REGDATE TIMESTAMP,
    VIEWS NUMBER DEFAULT 0,
    LIKES NUMBER DEFAULT 0,
    IMGO VARCHAR2(200) NOT NULL,
    IMGS VARCHAR2(200) NOT NULL,
    REPLYS NUMBER DEFAULT 0
    );
alter table board modify imgo default 'default.png';
alter table board modify imgs default 'default.png';
    
ALTER TABLE BOARD ADD CONSTRAINT BK
    FOREIGN KEY(NAME)
        REFERENCES MEMBER(NAME) ON DELETE CASCADE;
        
        
CREATE TABLE BOARD_REPLY (
    NO NUMBER,
    NAME VARCHAR2(50),
    CONTENT VARCHAR2(1000),
    REGDATE TIMESTAMP,
    REF NUMBER NOT NULL,
    STEP NUMBER DEFAULT 0,
    DEPTH NUMBER DEFAULT 0
);

ALTER TABLE BOARD_REPLY ADD CONSTRAINT RK
    FOREIGN KEY(NO)
        REFERENCES BOARD(NO) ON DELETE CASCADE;
        

CREATE TABLE LIKE_BOARD (
    NO NUMBER,
    MARKER VARCHAR2(50)
);
ALTER TABLE LIKE_BOARD ADD CONSTRAINT LBK_NO
    FOREIGN KEY(NO)
        REFERENCES BOARD(NO) ON DELETE CASCADE;
        
ALTER TABLE LIKE_BOARD ADD CONSTRAINT LBK_MARKER
    FOREIGN KEY(MARKER)
        REFERENCES MEMBER(NAME) ON DELETE CASCADE;

CREATE TABLE LIKE_CONTENT (
    MARKER VARCHAR2(50),
    ID VARCHAR2(50),
    CODE NUMBER DEFAULT 0
    );

ALTER TABLE LIKE_CONTENT ADD CONSTRAINT LCK
    FOREIGN KEY(MARKER)
        REFERENCES MEMBER(NAME) ON DELETE CASCADE;
        
CREATE TABLE CONTENTREPLY(
    ID VARCHAR2(50),
    WRITER VARCHAR2(50),
    NO NUMBER,
    CONTENT VARCHAR2(1000),
    REGDATE TIMESTAMP,
    REF NUMBER NOT NULL,
    STEP NUMBER DEFAULT 0,
    DEPTH NUMBER DEFAULT 0
);

ALTER TABLE CONTENTREPLY ADD CONSTRAINT CRK
    FOREIGN KEY(WRITER)
        REFERENCES MEMBER(NAME) ON DELETE CASCADE;
        
CREATE TABLE ALERT (
    NAME VARCHAR2(50),
    CODE NUMBER DEFAULT 0,
    CODEE NUMBER DEFAULT 0,
    URL VARCHAR2(200)
);

ALTER TABLE ALERT ADD CONSTRAINT AK
    FOREIGN KEY(NAME)
        REFERENCES MEMBER(NAME) ON DELETE CASCADE;

CREATE TABLE VISITOR (
	  TODAY NUMBER DEFAULT 0,
		TOTAL NUMBER DEFAULT 0);
        
CREATE SEQUENCE SEQ_BOARD NOCACHE; --게시판 번호 시퀀스

CREATE SEQUENCE B_REPLY NOCACHE;  --게시판 댓글 번호, 부모댓글(REF), 깊이(DEPTH) 시퀀스
CREATE SEQUENCE B_REF NOCACHE;
CREATE SEQUENCE B_DEPTH NOCACHE;

CREATE SEQUENCE C_REPLY NOCACHE;  --미디어 리뷰 댓글 번호, 부모댓글(PREP), 깊이(DEPTH) 시퀀스
CREATE SEQUENCE C_REF NOCACHE;
CREATE SEQUENCE C_DEPTH NOCACHE;

CREATE SEQUENCE SEQ_ALERT NOCACHE; -- 알림테이블 시퀀스

INSERT INTO MEMBER VALUES (
    'admin','admin','1234','[email protected]',
    0,'default.png','default.png');
    
COMMIT;

ERD

USE CASE