Notes/sql/0001.sql

29 lines
1.1 KiB
MySQL
Raw Permalink Normal View History

2023-06-17 09:11:14 +02:00
CREATE TABLE public."user" (
id SERIAL NOT NULL,
username VARCHAR(64) NOT NULL DEFAULT ''::character varying,
"password" VARCHAR(64) NOT NULL DEFAULT ''::character varying,
last_login TIMESTAMP NOT NULL DEFAULT now(),
CONSTRAINT newtable_pk PRIMARY KEY (id)
);
CREATE TABLE public."session" (
uuid UUID NOT NULL,
user_id INT4 NULL,
created TIMESTAMP NOT NULL DEFAULT now(),
CONSTRAINT session_pk PRIMARY KEY (uuid),
CONSTRAINT user_session_fk FOREIGN KEY (user_id) REFERENCES public."user"(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE public.node (
id SERIAL NOT NULL,
user_id INT4 NOT NULL,
parent_id INT4 NULL,
"name" VARCHAR(256) NOT NULL DEFAULT '',
"content" TEXT NOT NULL DEFAULT '',
CONSTRAINT name_length CHECK (LENGTH(TRIM(name)) > 0),
CONSTRAINT node_pk PRIMARY KEY (id),
CONSTRAINT user_fk FOREIGN KEY (user_id) REFERENCES public."user"(id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT node_fk FOREIGN KEY (parent_id) REFERENCES public.node(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);