[Dbix-class] Problem with automatic primary keys

[Daniel Westermann-Clark]

On 2006-06-14 10:48:58 +1000, Robert Norris wrote:
> This is part of my Postgres setup:
> 
>     create sequence e_group_pk;
>     create table e_group (
>         groupid integer not null primary key,
>         name text not null
>     );

I almost always let PostgreSQL define my sequences:

CREATE TABLE e_group (
    groupid SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

This is equivalent to:

CREATE SEQUENCE e_group_pk;
CREATE TABLE e_group (
    groupid INTEGER PRIMARY KEY DEFAULT nextval('e_group_pk'),
    name TEXT NOT NULL
);

> I have this in my DBIC schema class to match:
> 
>     package Emma::Schema::Group;
>     use base qw(DBIx::Class);
>     __PACKAGE__->load_components(qw(PK::Auto Core));
>     __PACKAGE__->table("e_group");
>     __PACKAGE__->add_columns(qw(groupid name)); 
>     __PACKAGE__->set_primary_key("groupid");
>     __PACKAGE__->sequence("e_group_pk");

This looks good, though I've not needed to set the sequence
explicitly.  DBIx::Class has automatic sequence detection for some
databases, including PostgreSQL.

> My understanding is that PK::Auto will make it setup groupid
> automatically by getting a new value from the sequence. Is that
> right?

Sorta.  PK::Auto assumes the database will fill groupid itself.  On
insert it queries the storage backend for the "last insert id", then
sets the primary key field on your result instance.

Hope this helps,

-- 
Daniel Westermann-Clark