[CDBI] Dealing with non-required primary keys

John Drago john.drago at data393.com
Sun Sep 4 02:35:53 BST 2005

> 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)
> , 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,
> 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


> 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.

What do you mean - "how to deal with the typeid"?

> I've thought of a couple of solutions, but none of them work very
> 1)  In Class::DBI, you can declare a sequence for "typeid" that
> the one autogenerated by your RDBMS, but that's not really a good
> solution.  You're locking yourself into a single database type, since
> what the sequence is called changes from DBMS to DBMS; and you have to
> figure out what the sequence is called inside each one you want to
> I'm writing a module that uses Class::DBI, so I want it to be generic.
> 2)  You can assume that "set_up_table" from the subclass of Class::DBI
> (such as, for example, Class::DBI::mysql) will take care of it.
> Unfortunately, these modules are very far from working correctly with
> the current version of most RDBMSs - at least the postgresql, mysql,
> MSSQL modules are (and the mysql one isn't portable; it won't run on
> Windows).  They can't be depended on for big things like that.  It's
> also kind of unnecessary, since the database will use the sequence
> entirely without the help of Class::DBI.  Plus, you're limiting
> to only those databases that have subclasses in Class::DBI.  All that
> needful is that insertion is allowed without the primary key by
> Class::DBI.
> So what would you recommend?  Is this a technical feat that can't be
> accomplished in Class::DBI?  I should mention that "don't use primary
> keys" isn't really an option.  I need to use primary keys because I'm
> using quite a few "has_a" relationships in other modules that use the
> primary keys I'm using.

Is there something preventing you from doing it this way?

> _______________________________________________
> ClassDBI mailing list
> ClassDBI at lists.digitalcraftsmen.net
> http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi

More information about the ClassDBI mailing list