MiniProject/test.sql at master · jeongin9/MiniProject · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
select * from tab;


CREATE TABLE  users  (
	 id 	varchar2(20)	NOT NULL,
	 name 	varchar2(30)	NULL,
	 pass 	number(16)	not NULL,
	 phone 	number(14) not null,
	 nickname 	varchar2(20)NOT NULL
);

CREATE TABLE  story  (
	 story 	number	NOT NULL,
	 place 	varchar2(1000)	NULL,
	 food 	varchar2(1000)	NULL,
	 regidate 	date default sysdate not null,
	 visitcount 	number	NULL,
	 story_photo 	varchar2(100)	NOT NULL,
	 story_content 	varchar2(1000)	NULL,
	 story_title 	varchar2(40)	NULL
);

CREATE TABLE  notice  (
	 notice 	number	NOT NULL,
	 notice_content 	varchar2(1000)	NULL,
	 regidate 	date default sysdate not null,
	 notice_title 	varchar2(40)	NOT NULL
);

CREATE TABLE  mainpage  (
     main number not null,
	 main_title 	varchar2(1000)	NOT NULL,
	 admin_id 	varchar2(20)	NOT NULL,
	 main_content 	varchar2(1000)	NULL,
	 magazine 	number	NOT NULL,
	 story 	number	NOT NULL,
	 food 	number	NOT NULL,
	 place 	number	NOT NULL,
	 notice 	number	NOT NULL,
	 event 	number	NOT NULL
);

CREATE TABLE  place  (
	 place 	number	NOT NULL,
	 regidate 	date default sysdate not null,
	 visitcount 	number	NULL,
	 place_photo 	varchar2(100)	NOT NULL,
	 place_content 	varchar2(1000)	NULL,
	 place_title 	varchar2(40)	NOT NULL
);

CREATE TABLE  food  (
	 food 	number	NOT NULL,
	 regidate 	date default sysdate not null,
	 visitcount 	number	NULL,
	 food_photo 	varchar2(100)	NOT NULL,
	 food_content 	varchar2(1000)	NULL,
	 food_title 	varchar2(40)	NULL
);


CREATE TABLE  admin  (
	 admin_id 	varchar2(20)	NOT NULL,
	 admin_name 	varchar2(30)	NOT NULL,
	 admin_pass 	number(16)	NOT NULL,
	 admin_nickname 	varchar2(20)	NOT NULL
);

CREATE TABLE  event  (
	 event 	number	NOT NULL,
	 regidate 	date	NULL,
	 visitcount 	number	NULL,
	 photo 	varchar2(100)	NOT NULL,
	 event_content 	varchar2(1000)	NULL,
	 event_title 	varchar2(40)	NULL
);


CREATE TABLE  travlemagazine  (
	 magazine 	number	NOT NULL,
	 place 	varchar2(1000)	NULL,
	 food 	varchar2(1000)	NULL,
	 regidate 	date default sysdate not null,
	 visitcount 	number	NULL,
	 photo 	varchar2(100)	NOT NULL,
	 magazine_content 	varchar2(1000)	NULL,
	 magazine_title 	varchar2(40)	NULL
);

ALTER TABLE  users  ADD CONSTRAINT  PK_users  PRIMARY KEY (
	 id
);

ALTER TABLE  story  ADD CONSTRAINT  PK_story  PRIMARY KEY (
	 story
);

ALTER TABLE  notice  ADD CONSTRAINT  PK_notice  PRIMARY KEY (
	 notice
);

ALTER TABLE  mainpage  ADD CONSTRAINT  PK_mainpage  PRIMARY KEY (
    main
);

ALTER TABLE  place  ADD CONSTRAINT  PK_place  PRIMARY KEY (
	 place
);

ALTER TABLE  food  ADD CONSTRAINT  PK_food  PRIMARY KEY (
	 food
);

ALTER TABLE  admin  ADD CONSTRAINT  PK_admin  PRIMARY KEY (
	 admin_id
);

ALTER TABLE  event  ADD CONSTRAINT  PK_event  PRIMARY KEY (
	 event
);

ALTER TABLE  travlemagazine  ADD CONSTRAINT  PK_travlemagazine  PRIMARY KEY (
	 magazine
);

ALTER TABLE  mainpage  ADD CONSTRAINT  FK_admin_TO_mainpage  FOREIGN KEY (
	 admin_id
)
REFERENCES  admin  (
	 admin_id
);