问题描述
上下文
我们正在构建一个介绍博客。到数据库课程项目。
在我们的博客中,我们希望能够在<$ c $上设置标签
c>帖子。 标签
不能自己存在,他们只能与帖子
相关联。这样,标签
不会被任何帖子
使用,不应该留在数据库中。
多个标签
可以属于单个 Post
,而不止于一个 Post
可以使用标签
。
我们正在使用SQLite3(本地/测试)和PostgreSQL(部署)。
实现
这里是SQL (SQLite3风格),我们用来创建这两个表,以及关系表:
帖子
CREATE TABLE IF NOT EXISTS帖子(
id INTEGER PRIMARY KEY AUTOINCREMENT,
authorId INTEGER,
title VARCHAR(255),
content TEXT,
imageURL VARCHAR(255),
日期DATETIME,
FOREIGN KEY(authorId)参考作者(id)ON DELETE SET NULL
)
标签
CREATE TABLE IF NOT EXISTS Labels (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255)UNIQUE,
- 这不工作:
FOREIGN KEY(id)参考LabelPosts(labelId)ON DELETE CASCADE
)
LabelPosts (帖子 [1 .. *] - * 标签
)
CREATE TABLE IF NOT EXISTS LabelPosts(
postId INTEGER,
labelId INTEGER,
PRIMARY KEY(postId,labelId),
FOREIGN KEY(postId)参考帖子(id)ON DELETE CASCADE
)
问题
-
使用SQLite3,
标签
不会从数据库中删除, code> LabelPosts 表。我认为Postgres给出的原因,尽管SQLite接受了表而没有警告。 -
PostgreSQL抱怨说
labelId
在LabelPosts
中不是唯一的,这是真实的,也是必需的,因为它是多对多的: - 你的第一个错误:
-
在PostgreSQL中使用而不是SQLite
AUTOINCREMENT
。
使用,而不是datetime
。 -
。
-
不要使用非描述性的列名称,如
id
。永远这是半机制中间件和ORM引入的反模式。当您加入几张表格时,最终会出现名称为id
的多列。这是非常有害的。 -
有很多命名风格,但大多数人认为将单数术语作为表名更好。它更短,至少直观/逻辑。
标签
,而不是标签
。 -
As ,你的外键约束倒退了。
所以我明白我的约束是错的。但是我不知道该怎么做。
这是乞求麻烦。你会继续遇到轻微的不兼容。或者甚至没有注意到,直到很久以后,当损坏完成。 不要这样做。也在本地使用PostgreSQL。大多数操作系统都可以免费使用它。对于参与数据库课程项目的人来说,这是令人惊讶的愚蠢的。
一切都放在一起,它可能如下所示:
CREATE TABLE IF NOT EXISTS post(
post_id serial PRIMARY KEY
,author_id integer
,title text
,content text
,image_url text
,date timestamp
);
CREATE TABLE IF NOT EXISTS label(
label_id serial PRIMARY KEY
,name text UNIQUE
);
CREATE TABLE IF NOT EXISTS label_post(
post_id integer REFERENCES post(post_id)
ON UPDATE CASCADE ON DELETE CASCADE
,label_id integer REFERENCES label(label_id)
ON UPDATE CASCADE ON DELETE CASCADE
,PRIMARY KEY(post_id,label_id)
);
触发器
- 要删除未使用的标签,请执行触发器。我提供另一个版本,因为我不满意:
创建或替换功能f_trg_kill_orphaned_label()
RETURNS TRIGGER AS
$ func $
BEGIN
DELETE FROM label
WHERE label_id = OLD.label_id
AND NOT EXISTS(
SELECT 1 FROM label_post
WHERE label_id = OLD.label_id
);
END
$ func $ LANGUAGE plpgsql;
-
触发功能必须先创建 em>触发器。
-
一个简单的
DELETE
命令可以完成这项工作。不需要第二个查询 - 特别是没有count(*)
。EXISTS
更便宜。 -
ppgsql
。这是一个标识符,而不是一个价值!
CREATE TRIGGER label_post_delaft_kill_orphaned_label
删除后label_post
为每个ROW EXECUTE PROCEDURE f_trg_kill_orphaned_label();
没有 CREATE OR REPLACE TRIGGER
in PostgreSQL,但。 。
Context
We're building a blog for an intro. to databases course project.
In our blog, we want to be able to set Labels
on Posts
. The Labels
can't exist by themselves, they only do so if they are related to a Posts
. This way, Labels
that are not used by any Posts
shouldn't stay in the database.
More than one Label
can belong to a single Post
, and more than a single Post
can use a Label
.
We are using both SQLite3 (locally/testing) and PostgreSQL (deployment).
Implementation
Here is the SQL (SQLite3 flavor) that we use to create those two tables, along with the relationship table:
Posts
CREATE TABLE IF NOT EXISTS Posts(
id INTEGER PRIMARY KEY AUTOINCREMENT,
authorId INTEGER,
title VARCHAR(255),
content TEXT,
imageURL VARCHAR(255),
date DATETIME,
FOREIGN KEY (authorId) REFERENCES Authors(id) ON DELETE SET NULL
)
Labels
CREATE TABLE IF NOT EXISTS Labels(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) UNIQUE,
-- This is not working:
FOREIGN KEY (id) REFERENCES LabelPosts(labelId) ON DELETE CASCADE
)
LabelPosts (relation between Post
[1..*] -- * Label
)
CREATE TABLE IF NOT EXISTS LabelPosts(
postId INTEGER,
labelId INTEGER,
PRIMARY KEY (postId, labelId),
FOREIGN KEY (postId) REFERENCES Posts(id) ON DELETE CASCADE
)
Problem
Using SQLite3,
Labels
are not deleted from the database when I remove all references to it from theLabelPosts
table. I think for the reason given by Postgres, despite SQLite accepting the table without warning.PostgreSQL complains that
labelId
is not unique withinLabelPosts
, which is true and also required, since it's many-to-many:
So I understand that I'm doing my constraint wrong. However I don't know how to do it properly.
- Your first big mistake:
This is begging for trouble. You will keep running into minor incompatibilities. Or not even notice them until much later, when damage is done. Don't do it. Use PostgreSQL locally, too. It's freely available for most every OS. For someone involved in a "databases course project" this is a surprising folly.
In PostgreSQL use a
serial
column instead of SQLiteAUTOINCREMENT
.
Usetimestamp
(ortimestamptz
) instead ofdatetime
.Don't use non-descriptive column names like
id
. Ever. That's an anti-pattern introduced by half-wit middleware and ORMs. When you join a couple of tables you end up with multiple columns of the nameid
. That's actively hurtful.There are many naming styles, but most agree it's better to have singular terms as table names. It's shorter and at least as intuitive / logical.
label
, notlabels
.As @Priidu mentioned in the comments, your foreign key constraints are backwards. This is not up for debate, they are simply wrong.
Everything put together, it could look like this:
CREATE TABLE IF NOT EXISTS post (
post_id serial PRIMARY KEY
,author_id integer
,title text
,content text
,image_url text
,date timestamp
);
CREATE TABLE IF NOT EXISTS label (
label_id serial PRIMARY KEY
,name text UNIQUE
);
CREATE TABLE IF NOT EXISTS label_post(
post_id integer REFERENCES post(post_id)
ON UPDATE CASCADE ON DELETE CASCADE
,label_id integer REFERENCES label(label_id)
ON UPDATE CASCADE ON DELETE CASCADE
,PRIMARY KEY (post_id, label_id)
);
Trigger
- To delete unused labels, implement a trigger. I supply another version since I am not happy with the one provided by @Priidu:
CREATE OR REPLACE FUNCTION f_trg_kill_orphaned_label()
RETURNS TRIGGER AS
$func$
BEGIN
DELETE FROM label
WHERE label_id = OLD.label_id
AND NOT EXISTS (
SELECT 1 FROM label_post
WHERE label_id = OLD.label_id
);
END
$func$ LANGUAGE plpgsql;
trigger function must be created before the trigger.
A simple
DELETE
command can do the job. No second query needed - in particular nocount(*)
.EXISTS
is cheaper.No single-quotes around
plpgsql
. It's an identifier, not a value!
CREATE TRIGGER label_post_delaft_kill_orphaned_label
AFTER DELETE ON label_post
FOR EACH ROW EXECUTE PROCEDURE f_trg_kill_orphaned_label();
There is no CREATE OR REPLACE TRIGGER
in PostgreSQL, yet. Just CREATE TRIGGER
.
这篇关于外交关系在多对多的关系中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!