AlbaProject1.sql

CREATE TABLE APPLY (
  APPLY_ID    NUMBER PRIMARY KEY,
  USER_NO     NUMBER NOT NULL,
  POST_ID     NUMBER NOT NULL,
  STATUS      VARCHAR2(20) DEFAULT '접수' NOT NULL
                CHECK (STATUS IN ('접수', '검토 중', '합격', '불합격')),
  APPLY_DATE  DATE DEFAULT SYSDATE NOT NULL,

  CONSTRAINT FK_APPLY_USER FOREIGN KEY (USER_NO)
    REFERENCES USER_TABLE(USER_NO),

  CONSTRAINT FK_APPLY_POST FOREIGN KEY (POST_ID)
    REFERENCES JOB_POST(POST_ID),

  -- 중복 지원 방지
  CONSTRAINT UQ_APPLY_POST_USER UNIQUE (POST_ID, USER_NO)
);

-- FK 인덱스
CREATE INDEX IDX_APPLY_USER_NO ON APPLY(USER_NO);
CREATE INDEX IDX_APPLY_POST_ID ON APPLY(POST_ID);

-- PK 자동 발번 (12c+면 IDENTITY 써도 됨)
CREATE SEQUENCE APPLY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;

-- 1) 시퀀스 생성
CREATE SEQUENCE APPLY_SEQ START WITH 1 INCREMENT BY 1 NOCACHE;

-- 2) 트리거 생성
CREATE OR REPLACE TRIGGER TRG_APPLY_BI
BEFORE INSERT ON APPLY
FOR EACH ROW
BEGIN
  IF :NEW.APPLY_ID IS NULL THEN
    :NEW.APPLY_ID := APPLY_SEQ.NEXTVAL;
  END IF;
END;
/

-- 공통 계정
CREATE TABLE USER_TABLE (
  user_no     NUMBER PRIMARY KEY,                               -- 회원 번호 (시퀀스 사용)
  user_id     VARCHAR2(30) UNIQUE NOT NULL,                     -- 아이디
  password    VARCHAR2(100)       NOT NULL,                     -- 비밀번호
  name        VARCHAR2(50)        NOT NULL,                     -- 이름
  nickname    VARCHAR2(50)        NOT NULL,                     -- 닉네임
  user_type   CHAR(1)             NOT NULL                      -- 회원 유형: U=개인, C=기업, A=관리자
               CHECK (user_type IN ('U','C','A')),
  active_yn   CHAR(1) DEFAULT 'Y' NOT NULL                      -- 활성 여부: Y/N
               CHECK (active_yn IN ('Y','N')),
  reg_date    DATE DEFAULT SYSDATE NOT NULL                      -- 가입일
);


DROP TABLE user_table CASCADE CONSTRAINTS;

-- 개인 프로필
CREATE TABLE USER_PROFILE (
  user_no             NUMBER PRIMARY KEY
    REFERENCES USER_TABLE(user_no) ON DELETE CASCADE,
  resume_job_title    VARCHAR2(100),
  resume_location     VARCHAR2(100),
  resume_has_project  VARCHAR2(10),
  resume_project      VARCHAR2(300),
  resume_education    VARCHAR2(500)
);

-- 기업 프로필
CREATE TABLE COMPANY_PROFILE (
  user_no          NUMBER PRIMARY KEY
    REFERENCES USER_TABLE(user_no) ON DELETE CASCADE,
  company_name     VARCHAR2(100),
  manager_name     VARCHAR2(100),
  business_number  VARCHAR2(30) UNIQUE
);

CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1 NOCACHE;


BEGIN EXECUTE IMMEDIATE 'DROP TABLE APPLY CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE JOB_POST CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE MEMBER CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE COMPANY CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_COMPANY'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_MEMBER'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_JOB_POST'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_APPLY'; EXCEPTION WHEN OTHERS THEN NULL; END;
/


-- 1) 회사 ---------------------------------------------------------------
CREATE TABLE COMPANY (
  COMPANY_ID   NUMBER       NOT NULL,
  NAME         VARCHAR2(100) NOT NULL,
  BIZ_NO       VARCHAR2(20)  NOT NULL,  -- 사업자번호(형식검증은 앱에서)
  CREATED_AT   DATE DEFAULT SYSDATE,
  CONSTRAINT PK_COMPANY PRIMARY KEY (COMPANY_ID),
  CONSTRAINT UQ_COMPANY_BIZ UNIQUE (BIZ_NO)
);

CREATE SEQUENCE SEQ_COMPANY START WITH 1 NOCACHE;


commit;