81 lines
2.6 KiB
PL/PgSQL
81 lines
2.6 KiB
PL/PgSQL
-- This script was generated by the ERD tool in pgAdmin 4.
|
|
-- Please log an issue at https://github.com/pgadmin-org/pgadmin4/issues/new/choose if you find any bugs, including reproduction steps.
|
|
BEGIN;
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS public.alt_subject
|
|
(
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
name character varying(255) COLLATE pg_catalog."default" NOT NULL,
|
|
subject_id uuid NOT NULL,
|
|
CONSTRAINT alt_subject_pkey PRIMARY KEY (id),
|
|
CONSTRAINT alt_subject_name_key UNIQUE (name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.entries
|
|
(
|
|
id text COLLATE pg_catalog."default" NOT NULL,
|
|
subject_id uuid NOT NULL,
|
|
title text COLLATE pg_catalog."default",
|
|
info_entry text COLLATE pg_catalog."default",
|
|
paragraph text COLLATE pg_catalog."default",
|
|
time_published timestamp without time zone NOT NULL,
|
|
filepath text COLLATE pg_catalog."default",
|
|
CONSTRAINT entries_pkey PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.subjects
|
|
(
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
code bigint,
|
|
name character varying(255) COLLATE pg_catalog."default" NOT NULL,
|
|
CONSTRAINT subjects_pkey PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public."user-subject"
|
|
(
|
|
user_id uuid NOT NULL,
|
|
subject_id uuid NOT NULL,
|
|
CONSTRAINT "user-subject_pkey" PRIMARY KEY (user_id, subject_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.users
|
|
(
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
email character varying(255) COLLATE pg_catalog."default" NOT NULL,
|
|
password character varying(255) COLLATE pg_catalog."default" NOT NULL,
|
|
reg_time timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
notification_type character varying(255) COLLATE pg_catalog."default" NOT NULL DEFAULT 'NO_NOTIFICATION'::character varying,
|
|
CONSTRAINT users_pkey PRIMARY KEY (id),
|
|
CONSTRAINT unique_email UNIQUE (email)
|
|
);
|
|
|
|
ALTER TABLE IF EXISTS public.alt_subject
|
|
ADD CONSTRAINT alt_subject_subject_id_fkey FOREIGN KEY (subject_id)
|
|
REFERENCES public.subjects (id) MATCH SIMPLE
|
|
ON UPDATE NO ACTION
|
|
ON DELETE CASCADE;
|
|
|
|
|
|
ALTER TABLE IF EXISTS public.entries
|
|
ADD CONSTRAINT subject_for_key FOREIGN KEY (subject_id)
|
|
REFERENCES public.subjects (id) MATCH SIMPLE
|
|
ON UPDATE NO ACTION
|
|
ON DELETE NO ACTION;
|
|
|
|
|
|
ALTER TABLE IF EXISTS public."user-subject"
|
|
ADD CONSTRAINT for_subject FOREIGN KEY (subject_id)
|
|
REFERENCES public.subjects (id) MATCH SIMPLE
|
|
ON UPDATE NO ACTION
|
|
ON DELETE CASCADE;
|
|
|
|
|
|
ALTER TABLE IF EXISTS public."user-subject"
|
|
ADD CONSTRAINT for_user FOREIGN KEY (user_id)
|
|
REFERENCES public.users (id) MATCH SIMPLE
|
|
ON UPDATE NO ACTION
|
|
ON DELETE CASCADE;
|
|
|
|
END;
|