29 lines
1.1 KiB
SQL
29 lines
1.1 KiB
SQL
DROP TABLE IF EXISTS comment;
|
|
|
|
-- DROP SEQUENCE IF EXISTS comment_comment_id_seq;
|
|
-- CREATE SEQUENCE comment_comment_id_seq;
|
|
|
|
|
|
CREATE TABLE comment (
|
|
"comment_id" int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, -- SERIAL PRIMARY KEY
|
|
"post_id" int check ("post_id" > 0) NOT NULL DEFAULT 0,
|
|
"user_id" int check ("user_id" >= 0) NOT NULL DEFAULT 0,
|
|
"author" varchar(100) NOT NULL,
|
|
"email" varchar(100) NOT NULL DEFAULT '',
|
|
"url" varchar(200) NOT NULL DEFAULT '',
|
|
"ip" varchar(100) NOT NULL DEFAULT '',
|
|
"content" text NOT NULL,
|
|
"status" smallint check ("status" >= 0) NOT NULL DEFAULT 0,
|
|
"votes" smallint check ("votes" >= 0) NOT NULL DEFAULT 0,
|
|
"type" varchar(20) NOT NULL DEFAULT '',
|
|
"parent_id" int check ("parent_id" >= 0) NOT NULL DEFAULT 0,
|
|
"created_at" timestamp(0) NOT NULL DEFAULT now(),
|
|
"updated_at" timestamp(0) NOT NULL DEFAULT now()
|
|
-- PRIMARY KEY("comment_id")
|
|
);
|
|
|
|
CREATE INDEX "post_id" ON comment ("post_id","status");
|
|
CREATE INDEX "comment_parent" ON comment ("parent_id");
|
|
CREATE INDEX "comment_email" ON comment ("email");
|
|
|
|
SELECT setval('comment_comment_id_seq', 4, true); -- last inserted id by sample data |