Two primary keys specified in MySQL database

Question

I am trying to create a test database that is a replica of a preexisting database. I am using Django models (theoretically the models used with the original database) in order to do this. I recently inherited the code from someone else and am trying to figure out what exactly is going on with the code.

In the model, one the of the tables has two columns identified as the primary key.

        column1 = models.IntegerField(primary_key = True)
        column2 = models.IntegerField(primary_key = True)
        column3 = models.CharField(max_length = 30)

When I try to sync this model to the test database - an error occurs :

File "/somePathHere/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1068, 'Multiple primary key defined')

It is written in the django docs that Django does not allow multiple primary keys. However, looking at the output of DESCRIBE [tablename] in the original MySQL database, it seems like that is exactly what is going on here :

        +------------+------------+------+-----+---------+-------+
        | Field      | Type       | Null | Key | Default | Extra |
        +------------+------------+------+-----+---------+-------+
        | IDENTIFIER | bigint(20) | NO   | PRI | NULL    |       |
        | TIMESTAMP_ | bigint(20) | NO   | PRI | NULL    |       |
        | VALUE_     | longtext   | YES  |     | NULL    |       |
        +------------+------------+------+-----+---------+-------+

Note that both IDENTIFIER and TIMESTAMP_ are listed as the primary keys.

I'm seeing a lot of topics on SO ( Example 1 , Example 2, and Example 3 ) about creating a primary key based on multiple columns - is what I am seeing a composite key? In which case, how is this relayed via a Django model, i.e. how would one replicate it?

If it is not a composite key, what is it?

score 4 · Answer 1 · answered Jul 19 '11 at 22:53

4

It's not supported in Django, but there's a workaround. On your model specify unique_together and the fields in the Meta section:

class MyClass(models.Model):
    IDENTIFIER = models.IntegerField(blank=False,null=False)
    TIMESTAMP_ = models.IntegerField(blank=False,null=False)
    VALUE_ = models.TextField(blank=True, null=True)

    class Meta:
        unique_together = ('IDENTIFIER', 'TIMESTAMP_')

This will preserve the two-column primary key behavior.

answered Jul 19 '11 at 22:53

scoopseven

1,767
3
18
27

Thanks, this is very close to what I need, only, I noticed that it caused Django to create its own auto ID primary key column, which I don't want. Also, the details for the columns are labeled as 'mul' - is this correct? – mshell_lauren Jul 19 '11 at 23:09
Django needs a primary key on the table, are there any specific reasons you absolutely can't have one? How is the primary key adversely affecting you? Yes, MySQL is just letting you know that the column is indexed and it's not unique (UNI) or primary (PRI). From the MySQL docs: "If Key is MUL, multiple occurrences of a given value are permitted within the column. The column is the first column of a nonunique index or a unique-valued index that can contain NULL values." – scoopseven Jul 20 '11 at 15:22
Well, the primary key is supposed to be the composite of the identifier and timestamp rather than an auto id for this table. With django setting it's own primary key, I wasn't able to see how the unique_together was able to preserve the two column primary key behavior. If a MUL key is able to be nonunique or unique with null values, how can it function as a primary key? – mshell_lauren Jul 20 '11 at 16:00
The MUL concerns itself only with the column, not the combination of the two columns. Have you tested trying to insert duplicate values for the two columns? – scoopseven Jul 20 '11 at 17:40
Hmmm, interesting. I have not. All of the values in the database will be unique anyways because of the timestamp. – mshell_lauren Jul 21 '11 at 17:29

score 3 · Answer 2 · answered Jul 19 '11 at 22:16

Django currently does not support multi-column primary keys though there are patches/forks that extend it to do so (with varying degrees of polish). From the FAQ "Do Django models support multiple column primary keys?":

No. Only single-column primary keys are supported.

But this isn't an issue in practice, because there's nothing stopping you from adding other constraints (using the unique_together model option or creating the constraint directly in your database), and enforcing the uniqueness at that level. Single-column primary keys are needed for things such as the admin interface to work; e.g., you need a simple way of being able to specify an object to edit or delete.

What you're seeing is not two primary keys, but rather a two-column primary key. Tables by definition can only have one primary key.

score 2 · Accepted Answer · answered Jul 19 '11 at 22:18

2

It's a composite primary key. Try executing this:

show create table mytable;

It should show you the definition of the composite key.

This is nothing "unusual" from a mysql perspective.

answered Jul 19 '11 at 22:18

Bohemian

412,405
93
575
722

This was really helpful in being able to see how the composite key is defined. Thanks! – mshell_lauren Jul 19 '11 at 23:10

Two primary keys specified in MySQL database

3 Answers3

Linked