postgresql - Postgres : Not-null constraint with two foreign keys

Question

我是数据库设计的新手，想尝试做一个简单的聊天网络应用来练习这项技能。

我正在尝试制作一个类似于 Messenger.com 的聊天应用程序，用户可以在其中向联系人和群组发送消息。

我找到了这个不错的教程，但我的“消息”表有问题:http://www.vertabelo.com/blog/technical-articles/database-model-for-a-messaging-system

想法是，当消息发送给用户时，“reciever_group_id”为 NULL，而当消息发送给组时，“reciever_user_id”为 NULL。但是，postgres 不允许向 Messages 表添加消息，因为外键不能为 NULL，也就是说它违反了 reciever_user_id 或 reciever_group_id 的 NOT-NULL 约束。

有什么建议吗？

CREATE TABLE users (
  id serial primary key,
  username character varying(32) NOT NULL UNIQUE,
  password character varying(255) NOT NULL,
  name character varying(64) NOT NULL,
  image character varying(255),
  active int NOT NULL DEFAULT 0
);

CREATE TABLE groups (
  id serial primary key,
  name character varying(255) NOT NULL
);

CREATE TABLE group_users (
  id serial primary key,
  user_id serial references users(id),
  group_id serial references groups(id)
);

CREATE TABLE messages (
  id serial primary key,
  user_id serial references users(id),
  reciever_user_id serial references users(id),
  reciever_group_id serial references groups(id),
  body text
);

Answer 1

您需要一个检查约束来确保这两列中的一列恰好包含非空值。

CREATE TABLE messages (
  id serial primary key,
  user_id integer,
  receiver_user_id integer,
  receiver_group_id integer,
  body text,
  constraint check_group_user check (
      (receiver_user_id is null and receiver_group_id is not null) 
   or (receiver_user_id is not null and receiver_group_id is null) )
);

此外:不要将 FK 列定义为 serial，这样会自动生成不同的 ID，因为每个列使用另一个序列。

外键列应使用引用列的基本数据类型定义。所以 integer 而不是 serial。 serial is not a real data type