- Question
Will not create using the followings,
CREATE TABLE blog.post_comment (
id SERIAL PRIMARY KEY,
post_id BIGINT NOT NULL,
author_id BIGINT,
parent_id BIGINT,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP,
content TEXT,
CONSTRAINT fk_comment_post FOREIGN KEY (post_id) REFERENCES post(id),
CONSTRAINT fk_comment_user FOREIGN KEY (author_id) REFERENCES user(id),
CONSTRAINT fk_comment_parent FOREIGN KEY (parent_id) REFERENCES post_comment(id)
);
Error messages,
ERROR: syntax error at or near "user"
LINE 10: ...T fk_comment_user FOREIGN KEY (user_id) REFERENCES user(id),
- Question
I I changed
- old
--
-- Table structure for table `post_tag`
--
DROP TABLE IF EXISTS "post_tag";
CREATE TABLE "post_tag" (
"postId" bigint NOT NULL,
"tagId" bigint NOT NULL,
PRIMARY KEY ("postId","tagId"),
CONSTRAINT "fk_pt_post" FOREIGN KEY ("postId") REFERENCES "post" ("id"),
CONSTRAINT "fk_pt_tag" FOREIGN KEY ("tagId") REFERENCES "tag" ("id")
);
CREATE INDEX "idx_pt_tag" ON "post_tag" ("tagId");
CREATE INDEX "idx_pt_post" ON "post_tag" ("postId");
``
- New
```sql
--
-- Table structure for table `post_tag`
--
DROP TABLE IF EXISTS post_tag;
CREATE TABLE post_tag (
post_id bigint NOT NULL,
tag_id bigint NOT NULL,
PRIMARY KEY (post_id,tag_id),
CONSTRAINT "fk_pt_post" FOREIGN KEY (post_id) REFERENCES post(id),
CONSTRAINT "fk_pt_tag" FOREIGN KEY (tag_id) REFERENCES tag(id)
);
CREATE INDEX "idx_pt_tag" ON post_tag(tag_id);
CREATE INDEX "idx_pt_post" ON post_tag(post_id);
- Question
If I change
- old
--
-- Table structure for table "tag"
--
DROP TABLE IF EXISTS "tag";
CREATE TABLE "tag" (
"id" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
"title" VARCHAR(75) COLLATE "pg_catalog"."default" NOT NULL,
"metaTitle" VARCHAR(100) COLLATE "pg_catalog"."default",
"slug" VARCHAR(100) COLLATE "pg_catalog"."default" NOT NULL,
"content" TEXT COLLATE "pg_catalog"."default",
PRIMARY KEY ("id")
) ;
- new
--
-- Table structure for table "tag"
--
DROP TABLE IF EXISTS "tag";
CREATE TABLE "tag" (
id SERIAL PRIMARY KEY,
title VARCHAR(75) COLLATE "pg_catalog"."default" NOT NULL,
metaTitle VARCHAR(100) COLLATE "pg_catalog"."default",
slug VARCHAR(100) COLLATE "pg_catalog"."default" NOT NULL,
content TEXT COLLATE "pg_catalog"."default"
) ;
- Answer
The changes you proposed for the tag table look fine. The primary key column id is changed to use the SERIAL data type, which is equivalent to BIGINT GENERATED ALWAYS AS IDENTITY, but is more concise and readable. The other columns remain the same.
- Question
If I change
- old
--
-- Table structure for table `post_category`
--
DROP TABLE IF EXISTS post_category;
CREATE TABLE post_category (
post_id BIGINT NOT NULL,
category_id BIGINT NOT NULL,
PRIMARY KEY (post_id, category_id),
CONSTRAINT fk_pc_post FOREIGN KEY (post_id) REFERENCES post(id),
CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES category(id)
);
- New
--
-- Table structure for table `post_category`
--
DROP TABLE IF EXISTS post_category;
CREATE TABLE post_category (
post_id SERIAL PRIMARY KEY,
category_id SERIAL PRIMARY KEY,
PRIMARY KEY (post_id, category_id),
CONSTRAINT fk_pc_post FOREIGN KEY (post_id) REFERENCES post(id),
CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES category(id)
);
- Answer
he new SQL statement has a syntax error. You cannot have two PRIMARY KEY constraints in one table, and you also cannot use SERIAL as the primary key data type.
If you want to use a composite primary key in post_category table, you can do something like this:
--
-- Table structure for table `post_category`
--
DROP TABLE IF EXISTS post_category;
CREATE TABLE post_category (
post_id BIGINT NOT NULL,
category_id BIGINT NOT NULL,
PRIMARY KEY (post_id, category_id),
CONSTRAINT fk_pc_post FOREIGN KEY (post_id) REFERENCES post(id),
CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES category(id)
);
Note that you do not need to change the data type of the foreign key columns (post_id and category_id) when you change the primary key to a composite key.
- Question
If I chnaged
- Old
--
-- Table structure for table `post_comment`
--
DROP TABLE IF EXISTS post_comment;
CREATE TABLE post_comment (
id BIGINT NOT NULL,
post_id BIGINT NOT NULL,
author_id BIGINT,
parent_id BIGINT,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP,
content TEXT,
PRIMARY KEY (id),
CONSTRAINT fk_comment_post FOREIGN KEY (post_id) REFERENCES post(id),
CONSTRAINT fk_comment_user FOREIGN KEY (author_id) REFERENCES "user"(id),
CONSTRAINT fk_comment_parent FOREIGN KEY (parent_id) REFERENCES post_comment(id)
);
-
new
-
Question
If I changed
- old
--
-- Table structure for table `category`
--
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id BIGINT NOT NULL,
parent_id BIGINT,
title VARCHAR(75) NOT NULL,
meta_title VARCHAR(100),
slug VARCHAR(100) NOT NULL,
content TEXT,
PRIMARY KEY (id)
);
- new
--
-- Table structure for table `category`
--
DROP TABLE IF EXISTS category;
CREATE TABLE category (
id SERIAL PRIMARY KEY,
parent_id BIGINT,
title VARCHAR(75) NOT NULL,
meta_title VARCHAR(100),
slug VARCHAR(100) NOT NULL,
content TEXT
);
- Answer
The changes you made from the old category table to the new one are simply:
The id column is now set to SERIAL data type, which means that it is an auto-incrementing integer value that starts from 1 and increments by 1 for each new row inserted into the table. The PRIMARY KEY constraint is now set on the id column. Other than that, the column names, data types, and constraints are the same. These changes should not affect any existing queries or applications that are using the category table, but they do provide some benefits:
The SERIAL data type automatically generates unique values for the id column, which simplifies the process of adding new rows to the table. The PRIMARY KEY constraint on the id column ensures that each row in the table has a unique identifier, which can be useful for joining with other tables or for referencing rows in foreign keys.
alexlai@munetaka.me So, the chnages are OK Yes, the changes you have proposed for all four tables (post, post_meta, post_comment, and category) appear to be valid and should work in PostgreSQL. You have updated the data types of some columns, added and removed some constraints, and used SERIAL to create a new column as an auto-incrementing primary key. Overall, the changes seem to be sensible and should work as expected.
--
-- Table structure for table `post_comment`
--
DROP TABLE IF EXISTS post_comment;
CREATE TABLE post_comment (
SERIAL PRIMARY KEY,
post_id BIGINT NOT NULL,
author_id BIGINT,
parent_id BIGINT,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP,
content TEXT,
CONSTRAINT fk_comment_user FOREIGN KEY (author_id) REFERENCES user(id),
CONSTRAINT fk_comment_parent FOREIGN KEY (parent_id) REFERENCES post_comment(id)
);
--- Answer
There are a couple of issues with the new table structure:
The id column does not have a name, so you need to specify a name for it. The post_id column does not have a foreign key constraint that references the post table. Here's a corrected version:
--
-- Table structure for table `post_comment`
--
DROP TABLE IF EXISTS post_comment;
CREATE TABLE post_comment (
id SERIAL PRIMARY KEY,
post_id BIGINT NOT NULL,
author_id BIGINT,
parent_id BIGINT,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP,
content TEXT,
CONSTRAINT fk_comment_post FOREIGN KEY (post_id) REFERENCES post(id),
CONSTRAINT fk_comment_user FOREIGN KEY (author_id) REFERENCES "user"(id),
CONSTRAINT fk_comment_parent FOREIGN KEY (parent_id) REFERENCES post_comment(id)
);
---
- Question,
If I change
- old
```sql
--
-- Table structure for table `post_meta`
--
DROP TABLE IF EXISTS "post_meta";
CREATE TABLE "post_meta" (
"id" BIGSERIAL NOT NULL,
"postId" BIGINT NOT NULL,
"key" VARCHAR(50) COLLATE "pg_catalog"."default" NOT NULL,
"content" TEXT COLLATE "pg_catalog"."default",
CONSTRAINT "pk_post_meta_id" PRIMARY KEY ("id"),
CONSTRAINT "uq_post_meta" UNIQUE ("postId", "key"),
CONSTRAINT "fk_meta_post" FOREIGN KEY ("postId") REFERENCES "post" ("id")
);
- new
--
-- Table structure for table `post_meta`
--
DROP TABLE IF EXISTS post_meta;
CREATE TABLE post_meta (
id SERIAL PRIMARY KEY,
post_id BIGINT NOT NULL,
key VARCHAR(50) COLLATE pg_catalog.default NOT NULL,
content TEXT COLLATE pg_catalog.default,
CONSTRAINT uq_post_meta UNIQUE (post_id, key),
CONSTRAINT fk_meta_post FOREIGN KEY (post_id) REFERENCES post(id)
);
- Answer,
Yes, the new table structure you provided is equivalent to the old table structure. The main differences are the use of the SERIAL data type for the id column and the removal of the schema name from the COLLATE clause. The SERIAL data type is just a shorthand for creating an auto-incrementing integer column that starts at 1, and it is functionally equivalent to the BIGSERIAL data type used in the old structure.
The COLLATE clause determines the collation (sorting and comparison rules) to use for the column data. If you don't specify a collation, PostgreSQL uses the default collation for the database. In the new structure, you have removed the schema name from the COLLATE clause, so it will use the default collation for the database. This is equivalent to using the pg_catalog.default collation in the old structure.
- Question,
Could I change this post table from
- old
--
-- Table structure for table `post`
--
DROP TABLE IF EXISTS post;
CREATE TABLE post (
id BIGINT NOT NULL,
author_id BIGINT NOT NULL,
parent_id BIGINT,
title VARCHAR(75) NOT NULL,
meta_title VARCHAR(100),
slug VARCHAR(100) NOT NULL,
summary TEXT,
published BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP,
published_at TIMESTAMP,
content TEXT,
PRIMARY KEY (id),
CONSTRAINT fk_post_parent FOREIGN KEY (parent_id) REFERENCES post(id),
CONSTRAINT fk_post_user FOREIGN KEY (author_id) REFERENCES user(id)
);
- new
--
-- Table structure for table `post`
--
DROP TABLE IF EXISTS post;
CREATE TABLE post (
id SERIAL PRIMARY KEY,
-- BIGINT is a data type used to store 8-byte signed integers
-- from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
author_id BIGINT NOT NULL,
parent_id BIGINT,
title VARCHAR(75) NOT NULL,
meta_title VARCHAR(100),
slug VARCHAR(100) NOT NULL,
summary TEXT,
published BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP,
published_at TIMESTAMP,
content TEXT,
-- adds a foreign key to the parent_id column in the current table, referencing the id column in the post table
CONSTRAINT fk_post_parent FOREIGN KEY (parent_id) REFERENCES post(id),
CONSTRAINT fk_post_user FOREIGN KEY (author_id) REFERENCES "user"(id)
);
If I chnage the id column to PRIMARY, then is this CONSTRAINT fk_post_parent FOREIGN KEY (parent_id) REFERENCES post(id) necessary?
- Answer
Yes, you can change the post table from the old version to the new version that uses the SERIAL data type for the id column instead of BIGINT NOT NULL.
In the new version, SERIAL is an auto-incrementing integer that starts at 1 and increases by 1 for each new row inserted. This is useful because it eliminates the need to specify the id value explicitly for each new row, making the table easier to use and less error-prone.
The new version also removes the NOT NULL constraint on the id column since it's automatically generated by the database, and adds a PRIMARY KEY constraint to the id column.
Note that the foreign key constraints on parent_id and author_id remain the same.
If you change the id column to PRIMARY KEY, then id becomes the primary key and is automatically indexed. This means that when you create a foreign key constraint that references id, it will automatically reference the primary key. Therefore, the CONSTRAINT fk_post_parent FOREIGN KEY (parent_id) REFERENCES post(id) will still work correctly even if you change the id column to a PRIMARY KEY.
In short, you would still need the CONSTRAINT fk_post_parent FOREIGN KEY (parent_id) REFERENCES post(id) constraint if you want to enforce a foreign key relationship between the parent_id column and the id column in the post table, regardless of whether or not the id column is the primary key.