On 30 Jan 2009, at 21:40, gdewitt wrote:
Hi, I am using Catalyst, DBIx and postrges. My tables have primary
keys.
When you create a primary key in postres a unique index is implicitly
created as well. So the majority of my tables have a constraint
that looks
like this:
CONSTRAINT table_pkey PRIMARY KEY (id)
I use catalyst to create my DBIx table definitions like this:
create.pl model MyModel ....
The resulting DBIx table definitions reflect the primary key as well
as the
unique constraint like this:
__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint("table_pkey", ["id"]);
So if I write a query like this:
my $result = $c->model('Schema::Table')->find( 'x' );
it results in the following SQL:
SELECT x,y,z FROM table me WHERE ( ( me.id = 'x' ) OR ( me.id =
'x' ) );
This is happening because the 'find' function checks all the unique
constraints on the table, and in this case there are 2 ('primary' :
'id' and
'table_pkey' : 'id')
I can get around this by writing my query like this:
my $result = $c->model('Schema::Table')->find( { 'id'=> 'x' }, {
'key'=>'primary' } );
But that is a pain.
Does anyone know a way that I can stop the 2nd unique constraint:
__PACKAGE__->add_unique_constraint("table_pkey", ["id"]);
from being generated in my table definition files?
simple. Ignore the fact that Pg created a unique index to go with the
primary key - you created just a primary key, what Pg does is
irrelevant.
i.e. get rid of the add_unique_constraint. That is only needed if you
explicitly create (and want to search on) a subsequent unique
constraint.
-ash
_______________________________________________
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
