[Dbix-class] Dose DBIx::Class necessary need primary key?

[Brandon Black]

On 2/6/06, atsushi kobayashi <kak-829 at nifty.com> wrote:
> Hi all.
>
> I tried DBIx::Class-0.05000 & DBIx::Class-0.05001 with SQLite3 & MySQL.
> This script doesn't work on My PC.
> [...]
> Dose DBIx::Class necessary need primary key?
>
> MySQL was same.
> Any ideas ?

DBIx::Class issues aside, having a SQL table with no primary key is
generally a Bad Idea.

What happens in your particular example is that you're asking
DBIx::Class to update a specific Row object in your table, but since
the table has no primary key, the code cannot identify which row in
the table should actually be updated (for instance, if you had two
rows in that PK-less table that were duplicates of each other, it
could not tell them apart).

The problem with the existing code is that in this situation it ended
up doing the update operation on all rows in the table, when it
probably should have thrown an error regarding the situation with the
lack of primary keys.  I've put a patch into trunk now that throws
that error instead of altering your data, it seems the safest thing to
do for now unless someone comes up with a clean method of handling
these cases in the future.

With the new patch, your example would still have thrown an error, but
it wouldn't have messed with your data.

One semi-clean way to handle it would be to treat a PK-less table like
one that has a PK defined across all columns for row-based
update/delete purposes.  This would work intuitively for a unique row
in a PK-less table, but would also update any duplicate rows in the
same table, which may or may not make sense to various users.

In any case, another way around this whole mess would be to use
ResultSet-based update/delete:

$schema->resultset('Test')->search({id => [1,2,3],name =>
'test'})->update({ name => 'test 1'});

But this only works if you're making the same update to all of the
rows; it would not allow you to change the value of name on a per-row
basis.  Same basic caveats and methods apply to delete operations.

-- Brandon