Composite Primary / Foreign Key support

[Lance Ellinghaus]

I have seen references to people adding the support of composite primary keys to Django for years, but it does not look like it has been added.

I have a number of legacy databases that I cannot add fields to but need to be able to be queried through Django. They are read-only tables.

A number of the tables are also related through Composite Foreign Keys and I have seen some addons but not sure what is fully supported.

I am trying to use Django 2.1.5.

Any help or ideas would be appreciated, and saying  "Add a field to your tables" is not an option.

Thank you.

[Michal Petrucha]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Hi Lance,

I haven't been on top of this in recent years, but let me try.

When it comes to just composite primary keys, as long as you only need
read access, you might be able to get away with marking an arbitrary
field as the primary key. Using a non-unique field as the primary key
would make any writes to the database dangerous, but that's not your
case here. However, you also lose the invariant that filtering on the
“pk” field alias will match at most one row, which means the admin
would probably be off the table, too – since that assumes the “pk” is,
in fact, unique, and the same holds for any other package that makes
use of “pk”. You should still be okay in your own code, though.

For composite foreign keys, there is nowadays a private, undocumented
API, ForeignObject, which is what ForeignKey builds on. The general
ForeignObject lets you specify multiple fields that make up the
relation, you should be able to find some examples in the test suite
(there is a foreign_object package in there). Keep in mind that this
is an internal API, so it might change without warning.

Good luck,

Michal
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJcYXniAAoJEHA7T/IPM/kl8EAP/Rzaxqe0bDO6vkxb4/Mmmj5S
62t6Cfm3VqqK6NxYjRXG904jKwDI4HMH1SAs5fdVMh9vf+VBj/S5bcIpQbesqXbh
wbkcIn+mmnfhLDaHOGunYqp76tscBJv/rtxJZaX5NRLp7OraCWtbjRBtiw1fJ/tl
iJD0HRIk9zn0pqyX8GjeAyM0UR+uL1wwrvz8Ur85ASsc8pFThTP6ZMQoaEIgo9D+
HB3XfrhiOMd5Nb2SbjG4KCRe7alpFx83nuY53YVsv+8X+Nqp4Ndi7ch2Ni3jxxHf
R7qVJYsMp/l72CNb6KiT85sb6PwQyeVdvU78cXKjkIKDirskGgQIEW2OvK+ZXC3B
Aj4I+AMFXEKje8ITfW3/s/v+UNvVTNZHYC5NPZ6o50+YJFDoiwb0mpQMdUredWZk
nT2cYMgyPQA/XKN9w5vUCNbTebPG/AA7yCthXbOrHn9Xl+kICkXRjjl9fHRUs55Q
wgPqz0CFXPKaE+JFt/NABJzdSrki1y587GHsOb7hKFiQwS8DHU8WOMXkR3BGKEtN
q0gebcqKvVnjRwLLsBvB0h3uI/yifgBTp8G8/+tZ0WuNdongTe+yPzchxuRjgx10
e70xtBjgsyCQGRB3CPsgR2UelQOtAEt8ZyYwyPF5sQYOvQg1xjcYp87FqfqflSTE
6aQIMkXhKjKazmc/ew9F
=Ji5Z
-----END PGP SIGNATURE-----

[Ellinghaus, Lance J]

NONCONFIDENTIAL // EXTERNAL
So, basically, Django will not be supporting Composite Primary Keys in the future?

Lance

-----Original Message-----
From: django...@googlegroups.com <django...@googlegroups.com> On Behalf Of Michal Petrucha
Sent: Monday, February 11, 2019 8:34 AM
To: Django users <django...@googlegroups.com>
Subject: [External] Re: Composite Primary / Foreign Key support

NONCONFIDENTIAL // EXTERNAL

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/20190211133426.GV10973%40koniiiik.org.
For more options, visit https://groups.google.com/d/optout.

[Roger Gammans]

Lance,

I can't speak for the core team, only inform you based on what I've
seen here and in other projects.

I suspect if there was a significant need across a number of users,
then enough work could be done by those that needed it and as long as
it was
1) fully functional,
2) had clean good code
3) the people who wrote where going to stay within the
community to support it

Then there is a chance a set of code written might get merged, I don't
however thing there is sufficient interest from the core team for them
to do the work themselves.

Those requirements are quite a high bar, I started on some code to do
this, for a project a few years ago and got a limited set of composite
key functionality working in my own tree, which was just about enough
for the project I was working on. So I do have some understanding of
what is needed, and how intrusive and can get.

My code was fine for reading and writing, but you couldn't use
ForeignKey Fields to traverse composite key relationships. Which has a
surprising number of corner cases. This was for Django 1.8 and isn't
compatible with later versions (unfortunately). On the other hand some
of the code changes in the ORM point at a better approach, than we
took.

So it possible, but someone will need to invest some time into it.


The only immediately helpfuly ideas I have are;
As your requirement is read-only if the data is static you could to create a second database, with all the PK of the legacy database, and
then as single Pk field to that table, and access everything through a view of a join the two tables.

Of course if another app is adding rows then you are a bit stuck, but if you absolutely can't add a autonumber field to use as a fake PK, then there is not a lot you can do. I've started adding single PK autonumber fields to legacy databases now when I get this issue, and
making the old PK into a unique index. So far that has working and hasn't broken my legacy apps.

If those are no good, either you can't use django or you going to need to invest in understanding the ORMs internals.

HTH,

--
Roger Gammans <rgam...@gammascience.co.uk>
Gamma Science


On Mon, 2019-02-18 at 08:26 +0000, 'Ellinghaus, Lance J' via Django

> users/5c6a6c98.1c69fb81.eb4fb.5c2cSMTPIN_ADDED_MISSING%40gmr-
> mx.google.com.

[Matthew Pava]

"As your requirement is read-only if the data is static you could to create a second database, with all the PK of the legacy database, and
then as single Pk field to that table, and access everything through a view of a join the two tables."

I had such a requirement, and I created a View in PostgreSQL in the same database. The pk field I created was just a Window function for the current row in the data set. Since I didn't need that anyway, I could safely ignore it. Since it was a truly read-only model, I didn't need to worry about the composite key.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/1550510983.11519.20.camel%40gammascience.co.uk.

[Markus Holtermann]

There's general interest in having support for composite keys and composite foreign key. It's just that nobody has implemented it.

There are proposals among the DEPs that layout ideas how they could be implemented:

- https://github.com/django/deps/blob/master/draft/0191-composite-fields.rst
- https://github.com/django/deps/blob/master/draft/0192-standalone-composite-fields.rst

> https://groups.google.com/d/msgid/django-users/5c6a6c98.1c69fb81.eb4fb.5c2cSMTPIN_ADDED_MISSING%40gmr-mx.google.com.

[Simone Federici]

Lance,

sincerely, nobody take care about that feature because that's needed just for legacy db. 

The number of applications with on legacy db is less than 0.01% of the django applications.

There are some workaround for that applications, for example use SQL Alchemy.

I implemented a CompositeKey Dajngo feature for Django 1.4.  It is completly working also for the Generic Keys, concatening escaped keys. There was also a draft to implement the indexes. However, Django core team is evolving the platform so quickly (good for the framework) and the library is not mantained anymore. On Dajngo 1.6 I had should rewrote it completly.

In the end, now I understand why the core team don't care about the feature. Forget it. 

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5c6a6c98.1c69fb81.eb4fb.5c2cSMTPIN_ADDED_MISSING%40gmr-mx.google.com.

[Michal Petrucha]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

Hi Lance,

I see that you've already received a number of replies, but I'll give
you my perspective, too.

On Mon, Feb 18, 2019 at 08:26:43AM +0000, 'Ellinghaus, Lance J' via Django users wrote:
> NONCONFIDENTIAL // EXTERNAL
> So, basically, Django will not be supporting Composite Primary Keys
> in the future?
>
> Lance

That is not what I wrote – it is almost certainly possible for Django
to support this, but it will take a lot of effort, and a relatively
large refactor of ORM internals, enough so that it is unlikely to
happen unless someone steps up to sponsor the work over an extended
period of time.

Just to give you an idea of how much effort is needed here,
personally, I have spent well over half a year working on this full
time under GSoC, with support from some really skilled and smart
Django committers (taken all together, with all the research, and
trying to keep up with django master for some time, it might be close
a year, even, though spread over a much longer period of time), and it
still wasn't enough. Of course, I'm no 10x rockstar ninja, so someone
else might get the job done faster, but it's still a fair amount of
work.

There's a lot of material out there outlining how one might go about
this, the two DEPs were already pointed out by someone else, and the
latest thread on django-developers@ that summarized the topic was
https://groups.google.com/d/topic/django-developers/wakEPFMPiyQ/discussion
The one notable thing that is missing from all design documents that
I've seen (or written) is how to make this work with migrations.
Interactions with most other parts of Django have been described at
some length already.

This answer probably won't help you solve your immediate problem, and
I get that it's frustrating. Either way, that's where we are today.

Cheers,

Michal
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJcbBq2AAoJEHA7T/IPM/kl/oQQAIGotHVDPhiD646CZ3hACeqX
c0+U9g2fwhDl8yVauh+Ra9BSCY74TBYNky72PpBOJJF27q+QTTaGObG7j+Iiua/T
GWMYQNixaZrxxKhDYTO2Cpw9/2vsKGG92+ZjKMLV92dNGXTboFMZyubBaBwIh0wi
erHnVkhqeGuwlmlZHtW6vogjsqZMHK+bFEq3JH6x9v7/eMX3iOhRnJELN/zKcVZE
re49pHrkHtBEwAyhL3uYBdmG4HWdP2FTJ8qEPV97fuTm4ez+BFfqy3SaCd2wjgFG
5sssd8MSWSoY/2DIDJYyOJwaM+ogG8+4HjcnFy8eYG4cPAxcVFDT9o3SN/xHOMNH
rjJ5CVtWZI+XSAWupICLCe8ObKjmMGIgbbM9M6V5DdzUCuyUXBbT59sMABXnyBuQ
OxRLMdbkXz+wgfWRkhQLTj6TC8G0znZoCin1S8+vc6aM42qZA3RMj7wU5dRrgmX6
ln8nBwjibrsoXpdukCeGmgIgpuQqtUaBfbKZh2sfh0v34ZuYHuNd7iJVDP5BJ47R
COyH7QgKijBAxgjl+GHxbLG+lqbMgJKL4kqOVEMsuX2xZ08RYt+1Cpqlq9QpPdj3
UPCYnmB2H+jEhPxBZLsH7sjXbQQCAiUqtE++2BMRDgczR7dsdmeduY+0X7toK+0A
QOowjjbhXohOLpAbmYKj
=VheK
-----END PGP SIGNATURE-----