



alter table OFFICE_WORKER drop constraint fk_OFFICE_WORKER_LNK_POST_ID;
alter table OFFICE_WORKER drop constraint fk_OFFICE_WORKER_LNK_TITLE_ID;
alter table OFFICE_WORKER drop constraint fk_OFFICE_WORKER_USER_ID;


alter table SCHEDULE drop constraint fk_SCHEDULE_WORKERID;



alter table SISETU_YOYAKU drop constraint fk_SISETU_YOYAKU_SISETU_ID;
alter table SISETU_YOYAKU drop constraint fk_SISETU_YOYAKU_WORKER_ID;



alter table WEBER_USER drop constraint fk_WEBER_USER_ROLE_ID;

alter table GOSTATUS drop constraint fk_GOSTATUS_WORKERID;


alter table KAISYU_DATA drop constraint fk_KAISYU_DATA_KINOU_ID;
alter table KAISYU_DATA drop constraint fk_KAISYU_DATA_JOKYO_ID;
alter table KAISYU_DATA drop constraint fk_KAISYU_DATA_GENIN_ID;
alter table KAISYU_DATA drop constraint fk_KAISYU_DATA_HAKKEN_ID;



DROP TABLE TAIO_JOKYUO;
DROP TABLE POST;
DROP TABLE OFFICIALTITLE;
DROP TABLE OFFICE_WORKER;
DROP TABLE GENIN;
DROP TABLE SCHEDULE;
DROP TABLE HAKKEN_FACE;
DROP TABLE KINOU;
DROP TABLE WEBER_AUTONUMBER;
DROP TABLE WEBER_ROLE;
DROP TABLE WEBER_USER;
DROP TABLE GOSTATUS;
DROP TABLE MESSAGE;
DROP TABLE KAISYU_DATA;
DROP TABLE SISETU;
DROP TABLE SISETU_YOYAKU;

CREATE TABLE TAIO_JOKYUO
(
	ID Integer ,
	KB_NAME VARCHAR (128) NOT NULL ,

primary key(
	ID 
)
);
CREATE TABLE POST
(
	ID Integer ,
	NAME VARCHAR (128) ,
	TEL VARCHAR (128) ,
	TEL2 VARCHAR (128) ,
	POST_ID VARCHAR (128) ,

primary key(
	ID 
)
);
CREATE TABLE OFFICIALTITLE
(
	ID Integer ,
	TITLE_ID VARCHAR (128) ,
	NAME VARCHAR (128) ,

primary key(
	ID 
)
);
CREATE TABLE OFFICE_WORKER
(
	ID Integer ,
	WORKERID VARCHAR (128) DEFAULT '----',
	PASSWORD VARCHAR (128) DEFAULT '---- ',
	F_NAME VARCHAR (255) DEFAULT '---- ',
	S_NAME VARCHAR (255) DEFAULT '----  ',
	F_NAME_KANA VARCHAR (255) DEFAULT '----   ',
	S_NAME_KANA VARCHAR (255) DEFAULT '----',
	ADDR VARCHAR (255) DEFAULT '----',
	TEL VARCHAR (255) DEFAULT '----',
	LNK_POST_ID Integer NOT NULL ,
	LNK_TITLE_ID Integer NOT NULL ,
	MAILADDR VARCHAR (255) DEFAULT '----    ',
	USER_ID Integer NOT NULL ,

primary key(
	ID 
)
);
CREATE TABLE GENIN
(
	ID Integer ,
	KB_NAME VARCHAR (128) ,

primary key(
	ID 
)
);
CREATE TABLE SCHEDULE
(
	ID Integer ,
	WORKERID Integer NOT NULL ,
	YEAR Integer ,
	MONTH Integer ,
	DAY Integer ,
	CONTENTS VARCHAR (255) ,
	NOTE VARCHAR (255) ,
	STARTTIME Integer ,
	STARTMINUTE Integer ,
	ENDTIME Integer ,
	ENDMINUTE Integer ,

primary key(
	ID 
)
);
CREATE TABLE HAKKEN_FACE
(
	ID Integer ,
	FASE VARCHAR (128) NOT NULL ,

primary key(
	ID 
)
);
CREATE TABLE KINOU
(
	ID Integer ,
	KINOU VARCHAR (128) NOT NULL ,

primary key(
	ID 
)
);
CREATE TABLE SISETU_YOYAKU
(
	ID Integer ,
	SISETU_ID Integer NOT NULL              ,
	WORKER_ID Integer NOT NULL              ,
	YEAR Integer ,
	MONTH Integer ,
	DAY Integer ,
	START_HOUR Integer ,
	START_MINUTE Integer ,
	END_HOUR Integer ,
	END_MINUTE Integer ,
	MEMO VARCHAR (256) ,

primary key(
	ID 
)
);
CREATE TABLE WEBER_AUTONUMBER
(
	TABLE_NAME VARCHAR ,
	NEXT_NUMBER Integer NOT NULL ,

primary key(
	TABLE_NAME 
)
);
CREATE TABLE WEBER_ROLE
(
	ID Integer ,
	NAME VARCHAR NOT NULL ,

primary key(
	ID 
)
);
CREATE TABLE WEBER_USER
(
	ID Integer ,
	NAME VARCHAR NOT NULL ,
	PASSWORD VARCHAR NOT NULL ,
	ROLE_ID Integer NOT NULL ,

primary key(
	ID 
)
);
CREATE TABLE GOSTATUS
(
	ID Integer ,
	WORKERID Integer NOT NULL ,
	STATUS VARCHAR (128) ,
	ETC VARCHAR (255) ,

primary key(
	ID 
)
);
CREATE TABLE MESSAGE
(
	ID Integer ,
	USER_ID VARCHAR (128) ,
	SUBJECT VARCHAR (128) ,
	CONTENTS VARCHAR (255) ,

primary key(
	ID 
)
);
CREATE TABLE KAISYU_DATA
(
	ID Integer ,
	KINOU_ID Integer NOT NULL ,
	TITLE VARCHAR (64) ,
	CONTENTS VARCHAR (1024) ,
	GENIN VARCHAR (1024) ,
	TAISYO VARCHAR (1024) ,
	TOROKU_ID Integer ,
	TOROKU_DM VARCHAR (8) ,
	TAIO_ID Integer ,
	TAIO_DM VARCHAR (8) ,
	SYONIN_ID Integer ,
	SYONIN_DM VARCHAR (8) ,
	JOKYO_ID Integer ,
	GENIN_ID Integer ,
	HAKKEN_ID Integer ,

primary key(
	ID 
)
);
CREATE TABLE SISETU
(
	ID Integer ,
	SISETU_ID VARCHAR (128) ,
	NAME VARCHAR (128) ,
	PLACE VARCHAR (128) ,
	MEMO VARCHAR (128) ,

primary key(
	ID 
)
);




alter table OFFICE_WORKER add constraint fk_OFFICE_WORKER_LNK_POST_ID foreign key (LNK_POST_ID) references POST (ID);
alter table OFFICE_WORKER add constraint fk_OFFICE_WORKER_LNK_TITLE_ID foreign key (LNK_TITLE_ID) references OFFICIALTITLE (ID);
alter table OFFICE_WORKER add constraint fk_OFFICE_WORKER_USER_ID foreign key (USER_ID) references WEBER_USER (ID);


alter table SCHEDULE add constraint fk_SCHEDULE_WORKERID foreign key (WORKERID) references OFFICE_WORKER (ID);



alter table SISETU_YOYAKU add constraint fk_SISETU_YOYAKU_SISETU_ID foreign key (SISETU_ID) references SISETU (ID);
alter table SISETU_YOYAKU add constraint fk_SISETU_YOYAKU_WORKER_ID foreign key (WORKER_ID) references OFFICE_WORKER (ID);



alter table WEBER_USER add constraint fk_WEBER_USER_ROLE_ID foreign key (ROLE_ID) references WEBER_ROLE (ID);

alter table GOSTATUS add constraint fk_GOSTATUS_WORKERID foreign key (WORKERID) references OFFICE_WORKER (ID);


alter table KAISYU_DATA add constraint fk_KAISYU_DATA_KINOU_ID foreign key (KINOU_ID) references KINOU (ID);
alter table KAISYU_DATA add constraint fk_KAISYU_DATA_JOKYO_ID foreign key (JOKYO_ID) references TAIO_JOKYUO (ID);
alter table KAISYU_DATA add constraint fk_KAISYU_DATA_GENIN_ID foreign key (GENIN_ID) references GENIN (ID);
alter table KAISYU_DATA add constraint fk_KAISYU_DATA_HAKKEN_ID foreign key (HAKKEN_ID) references HAKKEN_FACE (ID);



