Composite Primary Key

Using Django
1

Hi

I am new to Django and working on an pre-existing database that has 22 tables with compound primary keys. I have seen the existing ticket on this subject, talking about faking primary keys by using a primary key and a unique constraint. But that does not allow (obviously) the inclusion of duplicates in the primary key. I have tried to remove the primary key constraint, but then it does not work either because it is expecting an id field (my classes are not managed, since I started from an existing database).

I would like to maintain my database structure as much as possible, but I am afraid that I will have to add new ids to all my tables. Is there another possible way to work this out? or maybe is it about to be solved in a next release…?

Thank you

1 Like
2

Hi

This was literally asked on the django-developers mailing list at about the same time: Redirecting to Google Groups

Copying some bits of my reply here:

Multiple column primary keys is a long-open feature request. There’s a wiki page: MultipleColumnPrimaryKeys – Django and the ticket, still open, is #373: https://code.djangoproject.com/ticket/373 .

I’ve thought of a technique before for making composite PK’s work, though haven’t tested it. It should be possible to put a database view in front of your table that combines your primary key columns into one string. For example:

CREATE VIEW book_view AS SELECT CONCAT(catalogue_id, '-', book_id) AS primary_key, author_id, ... FROM book;

Then define your model as having a CharField as a primary key. PostgreSQL and MariaDB/MySQL will both automatically make single-table views map inserts and updates to the underlying table. Though you might need a trigger to allow inserts (new object saves) to work, splitting the parts back out.

If you try this techinque, let me know.

If you are at liberty to move your data around to add ID columns, it’s probably not that much work - depending on the size of the data. But changing primary key is often not possible without downtime.

The ticket is 15 years old and I don’t believe anyone is working on it currently, so I wouldn’t expect any changes there in the next release.

1 Like
3

Thank you for your quick response.

I am working with postgres, so I will try to implement your solution and post the result.

4

I was lucky enough to listen in on a conversation about this back at DjangoCon among a couple of very knowledgeable people. Apparently there have been multiple attempts to do this (at least 3), and they have all run across specific edge cases that create really serious problems, either difficulties in the ORM itself, or by creating breaking changes to existing code. (Sorry, I can’t speak to any of these issues specifically, I’m just parroting what I heard. I’m also quite interested in the topic myself.)

1 Like
5

Your solution seems to work OK with a little example:
Database:

CREATE OR REPLACE FUNCTION public.manage_foo()
  RETURNS trigger AS
$BODY$

DECLARE

new_id character varying[];
old_id character varying[];

BEGIN

IF (TG_OP='INSERT') THEN
	new_id := string_to_array(NEW.id_foo,'-');
	INSERT INTO foo (id1,id2,txt) VALUES (new_id[1]::integer,new_id[2]::integer,NEW.txt);
	RETURN NULL;
ELSEIF (TG_OP='UPDATE') THEN
	new_id := string_to_array(NEW.id_foo,'-');
	old_id := string_to_array(OLD.id_foo,'-');
	UPDATE foo SET txt = NEW.txt WHERE id1 = old_id[1]::integer AND id2 = old_id[2]::integer;
	RETURN NULL;
ELSEIF (TG_OP='DELETE') THEN
	old_id := string_to_array(OLD.id_foo,'-');
	DELETE FROM foo WHERE id1 = old_id[1]::integer AND id2 = old_id[2]::integer;
	RETURN NULL;
END IF;
	RETURN NULL;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

--Create trigger
CREATE TRIGGER manage_foo_view INSTEAD OF INSERT OR UPDATE OR DELETE
   ON public.foo_view FOR EACH ROW
   EXECUTE PROCEDURE public.manage_foo();

On Django models

class Foo(models.Model):
id_foo = models.CharField(max_length=100, primary_key=True)
txt = models.CharField(max_length=100)

    class Meta:
        managed = False
        db_table = 'foo_view'

Get, save, update and delete seem to work fine
Next step will be related tables…

Thanks for the idea!

6

It would be nice to know what they said to save big troubles in the future…

7

Well, in the context of that (very casual) conversation (outside, Monday evening, during the group “social”), there weren’t any details mentioned - at least none I recall. (Or if there were, they went over my head.) It’s something that has been worked on multiple times in the past, and all attempts have fallen short for one reason or another.
I know it’s not much help, but the only suggestion I might have would be to read all of ticket 373 and follow that through to some of the older conversations on the mailing list that it references. You might be able to chase down some of the people involved to find out what they encountered.