add foreign key constraint to uuid list column

Question

I have a table with following definition:

CREATE TABLE apartment (
  id      uuid         NOT NULL DEFAULT fellowship.uuid_generate_v4(), 
  name    VARCHAR(100) NOT NULL, 
  address VARCHAR(100) NOT NULL, 
  owners  uuid[]
);

Where the column owners is a list of UUIDs referenced in table owner with following definition:

CREATE TABLE owner (
  id      uuid         NOT NULL DEFAULT fellowship.uuid_generate_v4(), 
  name    VARCHAR(100) NOT NULL, 
  email   VARCHAR(100) NOT NULL
);

How can I define a foreign key constraint on column owners of table apartment to the table owner?

Answer 1

PostgreSQL currently does not support adding foreign key constraints on elements of an array type column. It is also not something that would generally be recommended as good practice in structuring your SQL data structure.

A best practice for this kind of many-to-many relationship is to use an association table. In your case, you can introduce an apartment_owner table that represents the relationship between apartments and owners:

CREATE TABLE apartment_owner (
    apartment REFERENCES apartment(id),
    owner REFERENCES owner(id)
)

Every time you want to add or remove an owner, you can just add or remove a row from that table. You can also get an apartment with all its owners as a simple JOIN query.

If you really want to return the owners as an array, you can simply do that with an array aggregate query:

select
    a.id, 
    a.name, 
    a.address,
    array_agg(ao.owner) as owners
from apartments as a
join apartment_owner as ao ON ao.apartment = a.id
group by a.id, a.name, a.address;