Unique constraint isn't good enough for update_or_create?

[Sean McAfee]

I have a table that doesn't have a primary key, but does a unique index.  When I call update_or_create on this table, I can create the row just fine, but when I try to update it with a second call, this exception gets thrown:

Cannot safely update a row in a PK-less table

But the documentation for DBIx::Class::ResultSet explicitly says that the method can use unique constraints.  So why isn't it?


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Darren Duncan]

Sean McAfee wrote:
> I have a table that doesn't have a primary key, but does a unique
> index.  When I call update_or_create on this table, I can create the row
> just fine, but when I try to update it with a second call, this
> exception gets thrown:
>
> Cannot safely update a row in a PK-less table
>
> But the documentation for DBIx::Class::ResultSet explicitly says that
> the method can use unique constraints.  So why isn't it?

Does your table have multiple unique constraints or just one?

 From my quick reading, the ResultSet documentation appears to be saying you
need to provide an explicit 'key' attribute value when a match is being done on
a non-primary key (aka unique constraint), rather than it figuring things out
for itself if you have exactly 1 unique constraint.  Have you set 'key' explicitly?

-- Darren Duncan

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Sean McAfee]

On Wed, Nov 11, 2009 at 10:21 PM, Darren Duncan <[hidden email]> wrote:

Sean McAfee wrote:

I have a table that doesn't have a primary key, but does a unique index.  When I call update_or_create on this table, I can create the row just fine, but when I try to update it with a second call, this exception gets thrown:

Cannot safely update a row in a PK-less table

But the documentation for DBIx::Class::ResultSet explicitly says that the method can use unique constraints.  So why isn't it?

Does your table have multiple unique constraints or just one?

Just one.
 

>From my quick reading, the ResultSet documentation appears to be saying you need to provide an explicit 'key' attribute value when a match is being done on a non-primary key (aka unique constraint), rather than it figuring things out for itself if you have exactly 1 unique constraint.  

That's not how I read it:

If no "key" is specified, it searches on all unique constraints defined on the source, including the primary key.

I have no primary key and a single unique constraint, so there's no ambiguity.
 

Have you set 'key' explicitly?

Yes, I had to set it to get the create half of update_or_create to work.

Shortly after I posted my original message, I found a workaround: recasting my unique constraint as a primary key.  It's a bit conceptually dirty, but it gets the job done.

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Darren Duncan]

Sean McAfee wrote:

> On Wed, Nov 11, 2009 at 10:21 PM, Darren Duncan <[hidden email]> wrote:
>      >From my quick reading, the ResultSet documentation appears to be
>     saying you need to provide an explicit 'key' attribute value when a
>     match is being done on a non-primary key (aka unique constraint),
>     rather than it figuring things out for itself if you have exactly 1
>     unique constraint.  
>
> That's not how I read it:
>
> If no "key" is specified, it searches on all unique constraints defined
> on the source, including the primary key.

It also says:

"If your table does not have a primary key, you *must* provide a value for the
key attribute matching one of the unique constraints on the source."

Now maybe one problem here is with the documentation, but I don't yet know
enough to suggest what should be changed.

> I have no primary key and a single unique constraint, so there's no
> ambiguity.
>
>     Have you set 'key' explicitly?
>
> Yes, I had to set it to get the create half of update_or_create to work.
>
> Shortly after I posted my original message, I found a workaround:
> recasting my unique constraint as a primary key.  It's a bit
> conceptually dirty, but it gets the job done.

Logically speaking, the relational model has no concept of a "primary key" but
just has (unique) "keys", where any key is just as good for uniquely identifying
a tuple (row) within a relation (rowset/table) using a subset of its attributes
(fields/columns) as any other .

That said, the concept of singling out one of the "keys" as a "primary key" is
still useful in practice for several purposes.  One of those purposes is when
you want to conceive of tuples in a relation being updateable, and the "primary
key" is conceptually the best thing to match on when you want to distinguish
between the case where a tuple is "updated" versus one where a tuple is deleted
plus another very similar one is inserted; the relational model doesn't
distinguish those 2 cases but it is useful in practice to do so.  A related
purpose involves designing transition constraints or triggers, that conceptually
want to make the same distinction between a conceptual update and a
delete+insert.  Another main use of a "primary key" is to help an automated
process that changes how a database is normalized so it is easier for that
process to know which of the keys is the best to clone into split relations for
matching the parts with.  A primary key designation is the user telling the DBMS
which of their keys is least likely to have conceptually mutable values.

-- Darren Duncan

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...