Trying to make a PostgreSQL field with a list of foreign keys in Django

Question

Here is what I'm trying to do: Make a model in Django that is a PostgreSQL array (database specific type), which contains foreign keys to another model.

class Books(models.Model):
    authors = ArrayField(
        models.ForeignKey('my_app.Authors', default=None, null=True, blank=True),
        blank=True,
        default=list()
    )

When I try to makemigrations, Django gives me this error:

SystemCheckError: System check identified some issues:
ERRORS:
my_app.Books.authors: (postgres.E002) Base field for array cannot be a related field.

Any Ideas on how to beat that?

You should use a `ManyToMany` field for this purpose. – Selcuk Mar 12 '16 at 13:01 — Selcuk, Mar 12 '16 at 13:01

Antoine Pinsard · Accepted Answer · 2022-07-01T11:04:45.170

38

You can't create an array of foreign keys. It is not a Django limitation, it is a PostgreSQL "limitation".

The reason is that a foreign key is not a type, it is a constraint on a field. An array of foreign keys does not make any sense.

The general approach to achieve this is to use an intermediate table that will be used as a link between two others :

Authors(id, name)
Books(id, title, pub_date)
BookAuthors(id, book_id, author_id)

In the above exemple, BookAuthors.book_id is a foreign key to Books.id and BookAuthors.author_id is a foreign key to Authors.id. Thus, the table BookAuthors is used to match an author with a book and vice versa.

Django abstracts this intermediate table with ManyToManyField fields:

class Authors(models.Model):
    name = models.CharField(...)

class Books(models.Model):
    title = models.CharField(...)
    pub_date = models.DateField(...)
    authors = models.ManyToManyField('my_app.Authors',
                                     related_name='authored_books')

Behind the scenes, Django will create the intermediate table.

Then, you can get all authors of a book using book.authors.all(), or all books authored by an author using author.authored_books.all().

edited Jul 01 '22 at 11:04

answered Mar 12 '16 at 14:16

Antoine Pinsard

33,148
8
67
87

This makes sense. Thank you! I'll just use the values not as a foreign key instead. I am somewhat worried about performance and the only lime I need these related keys is when I select this object itself. – Danil Mar 12 '16 at 23:04
I think you should still a many-to-many relation. I you set an array of integers, you will probably go into a lot of troubles. Especially about data integrity. I can't see the issue with performance. – Antoine Pinsard Mar 13 '16 at 09:38
"An array of foreign keys does not make any sens." Why? Sounds reasonable to me – Marcos Pereira Dec 01 '21 at 14:44
@MarcosPereira Because a foreign key is a constraint (like a unique constraint, a primary key or an index) not a data type. You can see a foreign key as a rule that states "Valid values for column author_id must be any primary key of table User". – Antoine Pinsard Dec 13 '21 at 04:58
Sounds reasonable, just not implemented. – Marcos Pereira Dec 13 '21 at 22:05

score 4 · Answer 2 · edited Dec 19 '17 at 13:32

4

You have to use ManyToManyField, ArrayField can't be related with another model.

edited Dec 19 '17 at 13:32

cezar

11,616
6
48
84

answered Mar 12 '16 at 16:32

DJeanCar

1,463
1
11
13

Trying to make a PostgreSQL field with a list of foreign keys in Django

2 Answers2

Linked