Opened 11 years ago

Closed 11 years ago

#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;

Change History (7)

comment:1 by ccruickshank, 11 years ago

Summary: primary in model is not primary key in databaseprimary key in model is not primary key in database

comment:2 by Preston Holmes, 11 years ago

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 by ccruickshank, 11 years ago

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 by Preston Holmes, 11 years ago

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 by Ccruickshank, 11 years ago

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 by ccruickshank, 11 years ago

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 by ccruickshank, 11 years ago

Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top