postgresql - 如何在 PostgreSQL 的一行中存储多个外键

Question

我有两张表，我希望能够查找所有employees当前正在使用company. 我的想法是在company表中存储一组员工：

表启动。

(
    employee_id serial not null
        constraint employees_pk
            primary key,
    name text
);

create table company
(
    company_id serial not null
        constraint company_pk
            primary key,
    name text,
    employees jsonb
        constraint company_employee_id_fk
            references employees
);

[42804] ERROR: foreign key constraint "company_employee_id_fk" cannot be implemented Detail: Key columns "employees" and "employee_id" are of incompatible types: jsonb and integer.

我想要的结果是有一个如下所示的对象：

{
   "name": "A company",
   "employees": [{
      "name": "Jamie Hutber",
      "name": "John Smith",
      "name": "Frank Albert"
    }]
}

标签： postgresqlforeign-keys