正确的方法来创建一个从另一个表引用变量的表

问题描述：

我有这些关系：

用户（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)
);

正确的方法来创建一个从另一个表引用变量的表

相关推荐