Code

#19030 closed Bug (fixed)

primary key in model is not primary key in database

Reported by: ccruickshank Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I've recently started using Django for a smallish project.
At the moment I'm developing models but have noticed a possible bug

A "primary key" created in the model is not a "primary key" in the Postgres db but "unique" is this a bug?

The sqlall code suggest that it is attempting to create a tabel with the right structure, but the database shows the field to be "unique" and not "primary key"

Model info

from django.db import models

# Create your models here.

class ModelTest(models.Model):
    pkey=models.CharField(max_length=20, primary_key=True)
    ukey=models.CharField(max_length=20,unique=True)


sqlall info

C:\dev\patinbox>python manage.py sqlall micropat_db
BEGIN;
CREATE TABLE "micropat_db_modeltest" (
    "pkey" varchar(20) NOT NULL PRIMARY KEY,
    "ukey" varchar(20) NOT NULL UNIQUE
)
;



extract from postgres database (version 9.1)

-- Table: micropat_db_modeltest

-- DROP TABLE micropat_db_modeltest;

CREATE TABLE micropat_db_modeltest
(
  pkey character varying(20) NOT NULL,
  ukey character varying(20) NOT NULL,
  CONSTRAINT micropat_db_modeltest_pkey_uniq UNIQUE (pkey ),
  CONSTRAINT micropat_db_modeltest_ukey_key UNIQUE (ukey )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE micropat_db_modeltest
  OWNER TO postgres;

Attachments (0)

Change History (7)

comment:1 Changed 22 months ago by ccruickshank

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Summary changed from primary in model is not primary key in database to primary key in model is not primary key in database

comment:2 Changed 22 months ago by ptone

Are you using pg_dump?

I'm seeing a block in the generated SQL that looks like:

ALTER TABLE ONLY dummy_modeltest
    ADD CONSTRAINT dummy_modeltest_pkey PRIMARY KEY (pkey);

using your models

what backend for the engine value of DATABASES setting are you using?

note the PG docs say:

Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows.

comment:3 Changed 22 months ago by ccruickshank

I am using pgAdminIII to review the db tables

I created some tables in django and then wanted to simply edit them by hand to generate some initial data

pg_admin complained and said that no editing was possible as there was no primary key for the table

If I do not specify a primary key and instead leave it to django then it creates an id field that is a primary key in the database

an example of such a table

class CompanyUrl(models.Model):
    company = models.CharField(max_length=50)
    urllink = models.CharField(max_length=200)

    def __unicode__(self):
        return self.company

this results in the following in pgAdminIII being displayed in the sqlpane

CREATE TABLE news_companyurl
(
  id serial NOT NULL,
  company character varying(50) NOT NULL,
  urllink character varying(200) NOT NULL,
  CONSTRAINT news_companyurl_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE news_companyurl
  OWNER TO postgres;

As you can see there is a "PRIMARY KEY" defined

The reason for using to use my own Primary Key is I want to joins tables together based on patent numbers (these are a combination of chars and numbers eg US7895641B1). I have used this approach succesfully in an earlier application using a different programming language and database.

The information in the setings file is

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': 'osa_direct',            # Or path to database file if using sqlite3.
        'USER': 'postgres',              # Not used with sqlite3.
        'PASSWORD': 'cintelliq',         # Not used with sqlite3.
        'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
    }
}

comment:4 Changed 22 months ago by ptone

Using the same backend and pgadmin I can not reproduce this. I'm nearly positive the platform Win32 vs OSX is not making any difference.

I'm pretty sure this should/will be closed as invalid.

As a final check towards reproducibility - is there a chance you added the primary_key=True to your Django models after running syncdb?

Django does not alter tables once created.

Please try to reproduce your issue from scratch - without reusing your project and see if you can reproduce.

comment:5 Changed 22 months ago by Ccruickshank

Interesting reply. You mentioned syncdb and then I realised that I use South to create and manage tables . I wonder if this has introduced the problem, if fact I'm use the trunk version which may not be fully developed.

I'll try your suggestion and just use a raw model . it is probably worthy checking without and without South.

comment:6 Changed 22 months ago by ccruickshank

I've had a look through the South migration files and the cause of my problem could lay with how South deals with migrations

Lets close this ticket and I'll raise the issue with the South developers

Thank you for your help

comment:7 Changed 22 months ago by ccruickshank

  • Resolution set to fixed
  • Status changed from new to closed

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.