Custom primary key using Oracle 10g

[Catriona]

Hello

I am a beginning Django user and would appreciate help on the
following issue.

How do I specify a custom primary key in my model when using Oracle
10g

I am using the lastest Django version from svn.

Thanks for your help

Catriona

[Jon Atkinson]

Catriona,

I have very little Oracle experience, but also no reason to think that
setting a custom primary key would be different from any other
database backend.

The documentation is here:
http://www.djangoproject.com/documentation/models/custom_pk/

The primary key is set by using 'primary_key=True' in the model. A
simple model with a custom primary key:

class Employee(models.Model):
employee_code = models.CharField(max_length=10, primary_key=True)
first_name = models.CharField(max_length=20)
last_name = models.CharField(max_length=20)

I hope this helps.

--Jon

[Catriona]

Hi Jon

Thanks for your reply. I read the article but with Oracle, a sequence
and trigger needs to be created for autogenerated primary keys. If I
try the code that you gave me, neither the sequence nor trigger are
created - ie

C:\DjangoTraining\PK>python manage.py sql test
CREATE TABLE "TEST_EMPLOYEE" (
"EMPLOYEE_CODE" NVARCHAR2(10) NULL PRIMARY KEY,
"FIRST_NAME" NVARCHAR2(20) NULL,
"LAST_NAME" NVARCHAR2(20) NULL
)
;
COMMIT;

If I let Django create a primary key, they are:

C:\DjangoTraining\PK>python manage.py sql test
CREATE TABLE "TEST_EMPLOYEE" (
"ID" NUMBER(11) NOT NULL PRIMARY KEY,
"FIRST_NAME" NVARCHAR2(20) NULL,
"LAST_NAME" NVARCHAR2(20) NULL
)
;
CREATE SEQUENCE TEST_EMPLOYEE_SQ;
CREATE OR REPLACE TRIGGER TEST_EMPLOYEE_TR
BEFORE INSERT ON "TEST_EMPLOYEE"
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT TEST_EMPLOYEE_SQ.nextval INTO :new.id FROM dual;
END;
/
COMMIT;

Am I missing something here?

Thanks again

Catriona

On Aug 14, 6:24 pm, "Jon Atkinson" <j...@jonatkinson.co.uk> wrote:
> Catriona,
>
> I have very little Oracle experience, but also no reason to think that
> setting a custom primary key would be different from any other
> database backend.
>
> The documentation is here:http://www.djangoproject.com/documentation/models/custom_pk/
>
> The primary key is set by using 'primary_key=True' in the model. A
> simple model with a custom primary key:
>
> class Employee(models.Model):
> employee_code = models.CharField(max_length=10, primary_key=True)
> first_name = models.CharField(max_length=20)
> last_name = models.CharField(max_length=20)
>
> I hope this helps.
>
> --Jon
>

> On 8/14/07, Catriona <catriona.john...@gmail.com> wrote:
>
>
>
>
>
> > Hello
>
> > I am a beginning Django user and would appreciate help on the
> > following issue.
>
> > How do I specify a custom primary key in my model when using Oracle
> > 10g
>
> > I am using the lastest Django version from svn.
>
> > Thanks for your help
>

> > Catriona- Hide quoted text -
>
> - Show quoted text -

[Ian]

Catriona,

Only AutoFields are auto-incrementing in Django. If you want to use a
custom primary key that isn't an AutoField, you will need to populate
the primary key yourself (or else create a custom trigger for it),
just as you would for any other column that is both NOT NULL and
UNIQUE. This is true across all the backends, not just Oracle.

-Ian

[Catriona]

Hi Ian

Sorry I wasn't really clear in what I want to achieve. I really just
want to call my primary key, in this case, employee_id and have it
incremented automatically.

I tried the following:

from django.db import models

class Employee(models.Model):
employee_id = models.AutoField(max_length=10, primary_key=True)

first_name = models.CharField(max_length=20)
last_name = models.CharField(max_length=20)

and got this output - the table and sequence were created but not the
trigger.

C:\DjangoTraining\PK>python manage.py validate
0 errors found.

C:\DjangoTraining\PK>python manage.py sql test
CREATE TABLE "TEST_EMPLOYEE" (

"EMPLOYEE_ID" NUMBER(11) NOT NULL PRIMARY KEY,

"FIRST_NAME" NVARCHAR2(20) NULL,
"LAST_NAME" NVARCHAR2(20) NULL
)
;
CREATE SEQUENCE TEST_EMPLOYEE_SQ;
CREATE OR REPLACE TRIGGER TEST_EMPLOYEE_TR
BEFORE INSERT ON "TEST_EMPLOYEE"
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT TEST_EMPLOYEE_SQ.nextval INTO :new.id FROM dual;
END;
/
COMMIT;

C:\DjangoTraining\PK>python manage.py syncdb
Creating table test_employee
Traceback (most recent call last):
File "manage.py", line 11, in ?
execute_manager(settings)
File "C:\Python24\Lib\site-packages\django\core\management.py", line
1730, in
execute_manager
execute_from_command_line(action_mapping, argv)
File "C:\Python24\Lib\site-packages\django\core\management.py", line
1621, in
execute_from_command_line
action_mapping[action](int(options.verbosity),
options.interactive)
File "C:\Python24\Lib\site-packages\django\core\management.py", line
554, in s
yncdb
cursor.execute(statement)
File "C:\Python24\Lib\site-packages\django\db\backends\util.py",
line 19, in e
xecute
return self.cursor.execute(sql, params)
File "C:\Python24\Lib\site-packages\django\db\backends\oracle
\base.py", line 1
22, in execute
return Database.Cursor.execute(self, query, params)
cx_Oracle.DatabaseError: ORA-00904: "ID": invalid identifier

Sorry if I'm missing something really basic here but I am a bit
confused.

Thanks

Catriona

> > created- Hide quoted text -

[Ian]

On Aug 15, 9:43 pm, Catriona <catriona.john...@gmail.com> wrote:
> Hi Ian
>
> Sorry I wasn't really clear in what I want to achieve. I really just
> want to call my primary key, in this case, employee_id and have it
> incremented automatically.

Sorry I misunderstood you. The code you tried appears to be correct,
but you've found a bug: the trigger code seems to be blindly assuming
that the AutoField will be named "id". Would you please submit a bug
report so that we don't lose track of it?

Until this is fixed, consider supplying your primary key with the
db_column="id" option as a workaround. That will cause the underlying
column to be named ID, so the trigger should work, but the Django name
for the field will still be the one that you pick for it.

Hope that helps!

-Ian

[Catriona]

Hi Ian

Sorry for taking a while to get back to you but I've been away.

Thanks for your advice. I will log a bug.

I have also found a couple of other issues and will report those as
well.

Regards

Catriona

> > > - Show quoted text -- Hide quoted text -