正确的方法来创建一个从另一个表引用变量的表 [英] Correct way to create a table that references variables from another table

问题描述

我有这些关系：

用户（uid：integer，uname：varchar），key is uid

Recipe（rid：integer，content：text），key is rid

Rating ：integer，uid：integer，rating：integer），key is（uid，rid）。

User(uid:integer,uname:varchar), key is uid
Recipe(rid:integer,content:text), key is rid
Rating(rid:integer, uid:integer, rating:integer) , key is (uid,rid).

我以下列方式构建表：

CREATE TABLE User(
    uid INTEGER PRIMARY KEY ,
    uname VARCHAR NOT NULL
);
CREATE TABLE Recipes(
    rid INTEGER PRIMARY KEY,
    content VARCHAR NOT NULL
);

现在为评分表：我想不可能插入 User\Recipe 中不存在的 uid\rid 。

我的问题是：以下哪项是正确的方法？或者如果没有一个是正确的，请建议正确的方法。此外，如果有人能向我解释两者之间的区别，我真的很感激。

Now for the Rating table: I want it to be impossible to insert a uid\rid that does not exist in User\Recipe.
My question is: which of the following is the correct way to do it? Or please suggest the correct way if none of them are correct. Moreover, I would really appreciate if someone could explain to me what is the difference between the two.

首先：

CREATE TABLE Rating(
    rid INTEGER,
    uid INTEGER,
    rating INTEGER CHECK (0<=rating and rating<=5) NOT NULL,
    PRIMARY KEY(rid,uid),
    FOREIGN KEY (rid) REFERENCES Recipes,
    FOREIGN KEY (uid) REFERENCES User
);

第二：

  CREATE TABLE Rating(
      rid INTEGER REFERENCES Recipes,
      uid INTEGER REFERENCES User,
      rating INTEGER CHECK (0<=rating and rating<=5) NOT NULL,
      PRIMARY KEY(rid,uid)
  );

编辑：
我认为用户作为表的名称有问题，所以忽略名称。

I think User is problematic as a name for a table so ignore the name.

推荐答案

技术上这两个版本在Postgres中都是一样的。 CREATE TABLE 的文档说得很清楚：

Technically both versions are the same in Postgres. The docs for CREATE TABLE say so quite clearly:

有两种方式来定义约束：表约束和列约束。列约束被定义为列定义的一部分。表约束定义不与特定列相关联，并且可以包含多个列。每个列约束也可以写成表约束;一个列约束只是当约束只影响一列时才使用的符号方便。

There are two ways to define constraints: table constraints and column constraints. A column constraint is defined as part of a column definition. A table constraint definition is not tied to a particular column, and it can encompass more than one column. Every column constraint can also be written as a table constraint; a column constraint is only a notational convenience for use when the constraint only affects one column.

所以当你必须引用一个复合键表约束是唯一的办法。

So when you have to reference a compound key a table constraint is the only way to go.

但是对于其他情况，我更喜欢最短，最简洁的形式，我不需要给我不重要的东西的名字，所以我的版本将如下所示：

But for every other case I prefer the shortest and most concise form where I don't need to give names to stuff I'm not really interested in. So my version would be like this:

CREATE TABLE usr(
    uid SERIAL PRIMARY KEY ,
    uname TEXT NOT NULL
);
CREATE TABLE recipes(
    rid SERIAL PRIMARY KEY,
    content TEXT NOT NULL
);
CREATE TABLE rating(
    rid INTEGER REFERENCES recipes,
    uid INTEGER REFERENCES usr,
    rating INTEGER NOT NULL CHECK (rating between 0 and 5),
    PRIMARY KEY(rid,uid)
);

这篇关于正确的方法来创建一个从另一个表引用变量的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！