[Dbix-class] Unique constraint isn't good enough for update_or_create?

[Darren Duncan]

Sean McAfee wrote:
> On Wed, Nov 11, 2009 at 10:21 PM, Darren Duncan <darren at darrenduncan.net> 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