[CDBI] Dealing with non-required primary keys
Matt S Trout
dbix-class at trout.me.uk
Sun Sep 4 13:12:19 BST 2005
On Sat, Sep 03, 2005 at 09:20:55PM -0400, Rusty Phillips wrote:
> There's a fairly common situation that comes up in databases that I
> can't seem to deal with in CDBI.
> Consider that you have a database like this (this is in MySQL, btw)
> CREATE DATABASE CHEESE
> ( typeid INTEGER AUTO_INCREMENT
> , typename TEXT
> , UNIQUE(typename)
> , PRIMARY KEY(typeid)
> This is done fairly often; the result is that you get an index built
> off of "typeid", but you can change the name (and size of the name, for
> that matter) without too many problems. It also means that if you
> decide not to make names unique, you can do that too without a problem.
> It's not really important to add a constraint to Class::DBI to handle
> the typename; the RDBMS will take care of that. What is important is
> how to deal with the typeid.
If you aren't using a sequence, Classs::DBI will automatically fill this
out by getting the last insert id appropriately (*not* using
$dbh->last_insert_id, mind, because this generally doesn't work). Class::DBI
itself can handle MySQL and SQLite out of the box, and the db-specific
subclasses by and large handle it pretty well themselves.
DBIx::Class takes a slightly different approach and provides a collection
of components that each do the job for a particular database - see
for a list of the ones currently available (and note that in some cases these
do a slightly better job than the Class::DBI::* subclasses due to our
faster development cycles and IMO cleaner architecture).
If you don't have existing Class::DBI code, I'd recommend evaluating
DBIx::Class as a possible alternative. Note *evaluating* - in spite of being
the project founder for DBIx::Class, I'm not a religious war kinda guy and
would much prefer people to pick the one that works best for them.
Matt S Trout Specialists in perl consulting, web development, and
Technical Director UNIX/Linux systems architecture and automation. Mail
Shadowcat Systems Ltd. mst (at) shadowcatsystems.co.uk for more information
+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +
More information about the ClassDBI