table.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.2
-- Dumped by pg_dump version 15.2

-- Started on 2023-04-17 17:39:44 KST

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 215 (class 1259 OID 16407)
-- Name: User; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public."User" (
    id integer NOT NULL,
    intraid character varying(50) NOT NULL,
    rating integer NOT NULL,
    wincount integer DEFAULT 0 NOT NULL,
    losecount integer DEFAULT 0 NOT NULL,
    email character varying(50) NOT NULL,
    isotp boolean DEFAULT false NOT NULL,
    avatar character varying(1000),
    nickname character varying(100)
);


ALTER TABLE public."User" OWNER TO postgres;

--
-- TOC entry 3648 (class 0 OID 0)
-- Dependencies: 215
-- Name: TABLE "User"; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public."User" IS '유저';


--
-- TOC entry 214 (class 1259 OID 16406)
-- Name: User_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public."User" ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public."User_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 217 (class 1259 OID 16416)
-- Name: channel; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.channel (
    id integer NOT NULL,
    kind integer,
    owner integer,
    roomname character varying(50),
    roompassword character varying(60)
);


ALTER TABLE public.channel OWNER TO postgres;

--
-- TOC entry 216 (class 1259 OID 16415)
-- Name: channel_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.channel ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.channel_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 220 (class 1259 OID 16442)
-- Name: channelblacklist; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.channelblacklist (
    "channelId" integer NOT NULL,
    userid integer NOT NULL
);


ALTER TABLE public.channelblacklist OWNER TO postgres;

--
-- TOC entry 221 (class 1259 OID 16457)
-- Name: channelinfo; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.channelinfo (
    chid integer NOT NULL,
    userid integer NOT NULL,
    isowner boolean NOT NULL,
    isadmin boolean NOT NULL
);


ALTER TABLE public.channelinfo OWNER TO postgres;

--
-- TOC entry 222 (class 1259 OID 16472)
-- Name: friendlist; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.friendlist (
    "userId1" integer NOT NULL,
    "userId2" integer NOT NULL
);


ALTER TABLE public.friendlist OWNER TO postgres;

--
-- TOC entry 219 (class 1259 OID 16427)
-- Name: matchhistory; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.matchhistory (
    id integer NOT NULL,
    winnerid integer,
    loserid integer,
    status integer,
    mapnumber integer,
    winscore integer,
    losescore integer
);


ALTER TABLE public.matchhistory OWNER TO postgres;

--
-- TOC entry 3649 (class 0 OID 0)
-- Dependencies: 219
-- Name: TABLE matchhistory; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.matchhistory IS '매칭 전적';


--
-- TOC entry 218 (class 1259 OID 16426)
-- Name: matchhistory_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.matchhistory ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.matchhistory_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 223 (class 1259 OID 16490)
-- Name: userblacklist; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.userblacklist (
    "userId1" integer NOT NULL,
    "userId2" integer NOT NULL
);


ALTER TABLE public.userblacklist OWNER TO postgres;

--
-- TOC entry 3469 (class 2606 OID 16414)
-- Name: User User_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public."User"
    ADD CONSTRAINT "User_pkey" PRIMARY KEY (id);


--
-- TOC entry 3472 (class 2606 OID 16420)
-- Name: channel channel_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.channel
    ADD CONSTRAINT channel_pkey PRIMARY KEY (id);


--
-- TOC entry 3480 (class 2606 OID 16446)
-- Name: channelblacklist channelblacklist_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.channelblacklist
    ADD CONSTRAINT channelblacklist_pkey PRIMARY KEY ("channelId", userid);


--
-- TOC entry 3482 (class 2606 OID 16461)
-- Name: channelinfo channelinfo_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.channelinfo
    ADD CONSTRAINT channelinfo_pkey PRIMARY KEY (chid, userid);


--
-- TOC entry 3475 (class 2606 OID 16431)
-- Name: matchhistory matchhistory_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.matchhistory
    ADD CONSTRAINT matchhistory_pkey PRIMARY KEY (id);


--
-- TOC entry 3485 (class 2606 OID 16476)
-- Name: friendlist user_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.friendlist
    ADD CONSTRAINT user_pkey PRIMARY KEY ("userId1", "userId2");


--
-- TOC entry 3488 (class 2606 OID 16494)
-- Name: userblacklist userblacklist_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.userblacklist
    ADD CONSTRAINT userblacklist_pkey PRIMARY KEY ("userId1", "userId2");


--
-- TOC entry 3477 (class 1259 OID 16542)
-- Name: IDX_3ec99dcca8329e104d570ed621; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX "IDX_3ec99dcca8329e104d570ed621" ON public.channelblacklist USING btree ("channelId");


--
-- TOC entry 3478 (class 1259 OID 16543)
-- Name: IDX_a8bbfacb9bc35b477e6029fb1e; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX "IDX_a8bbfacb9bc35b477e6029fb1e" ON public.channelblacklist USING btree (userid);


--
-- TOC entry 3470 (class 1259 OID 16540)
-- Name: User_pkey2; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX "User_pkey2" ON public."User" USING btree (id);


--
-- TOC entry 3473 (class 1259 OID 16541)
-- Name: channel_pkey2; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX channel_pkey2 ON public.channel USING btree (id);


--
-- TOC entry 3483 (class 1259 OID 16536)
-- Name: channelinfo_pkey2; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX channelinfo_pkey2 ON public.channelinfo USING btree (chid, userid);


--
-- TOC entry 3476 (class 1259 OID 16538)
-- Name: matchhistory_pkey2; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX matchhistory_pkey2 ON public.matchhistory USING btree (id);


--
-- TOC entry 3486 (class 1259 OID 16537)
-- Name: user_pkey2; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX user_pkey2 ON public.friendlist USING btree ("userId1", "userId2");


--
-- TOC entry 3489 (class 1259 OID 16539)
-- Name: userblacklist_pkey3; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX userblacklist_pkey3 ON public.userblacklist USING btree ("userId1", "userId2");


--
-- TOC entry 3497 (class 2606 OID 16554)
-- Name: friendlist FK_2b6064e6cacb092ce78da35d7d9; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.friendlist
    ADD CONSTRAINT "FK_2b6064e6cacb092ce78da35d7d9" FOREIGN KEY ("userId1") REFERENCES public."User"(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 3493 (class 2606 OID 16589)
-- Name: channelblacklist FK_3ec99dcca8329e104d570ed6210; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.channelblacklist
    ADD CONSTRAINT "FK_3ec99dcca8329e104d570ed6210" FOREIGN KEY ("channelId") REFERENCES public.channel(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 3490 (class 2606 OID 16584)
-- Name: channel FK_43b026a9ae31a105029f4a12580; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.channel
    ADD CONSTRAINT "FK_43b026a9ae31a105029f4a12580" FOREIGN KEY (owner) REFERENCES public."User"(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 3499 (class 2606 OID 16574)
-- Name: userblacklist FK_61fd6b493f2ee7489efdf07de83; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.userblacklist
    ADD CONSTRAINT "FK_61fd6b493f2ee7489efdf07de83" FOREIGN KEY ("userId1") REFERENCES public."User"(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 3498 (class 2606 OID 16559)
-- Name: friendlist FK_8250c9296ef141482b2f74cd466; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.friendlist
    ADD CONSTRAINT "FK_8250c9296ef141482b2f74cd466" FOREIGN KEY ("userId2") REFERENCES public."User"(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 3491 (class 2606 OID 16564)
-- Name: matchhistory FK_9558dc78fb5306eebe4361304b2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.matchhistory
    ADD CONSTRAINT "FK_9558dc78fb5306eebe4361304b2" FOREIGN KEY (loserid) REFERENCES public."User"(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 3494 (class 2606 OID 16594)
-- Name: channelblacklist FK_a8bbfacb9bc35b477e6029fb1e5; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.channelblacklist
    ADD CONSTRAINT "FK_a8bbfacb9bc35b477e6029fb1e5" FOREIGN KEY (userid) REFERENCES public."User"(id);


--
-- TOC entry 3492 (class 2606 OID 16569)
-- Name: matchhistory FK_bdc276b321dadf3efea14463adb; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.matchhistory
    ADD CONSTRAINT "FK_bdc276b321dadf3efea14463adb" FOREIGN KEY (winnerid) REFERENCES public."User"(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 3500 (class 2606 OID 16579)
-- Name: userblacklist FK_cacfa8569f2be2ebe97f2572cd2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.userblacklist
    ADD CONSTRAINT "FK_cacfa8569f2be2ebe97f2572cd2" FOREIGN KEY ("userId2") REFERENCES public."User"(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 3495 (class 2606 OID 16549)
-- Name: channelinfo FK_e8383ffe965934922ac82c70c52; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.channelinfo
    ADD CONSTRAINT "FK_e8383ffe965934922ac82c70c52" FOREIGN KEY (userid) REFERENCES public."User"(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 3496 (class 2606 OID 16544)
-- Name: channelinfo FK_f38ea7cc1ef2d5bacd45fd2230a; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.channelinfo
    ADD CONSTRAINT "FK_f38ea7cc1ef2d5bacd45fd2230a" FOREIGN KEY (chid) REFERENCES public.channel(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


-- Completed on 2023-04-17 17:39:44 KST

--
-- PostgreSQL database dump complete
--