You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by John Armstrong <si...@gmail.com> on 2007/06/04 08:09:50 UTC
Creating Compound PKs
Does Cayenne allow for the creation of a compound PK? I searched the
archives and google more generally and could only find hints at this.
The docs did not clarify either (although it was also hinted at. .I
think, its a bit late :) ).
My table looks like this:
id
name
serverid
status
enabled
I need to create a compound PK on name/serverid that will maintain
uniqueness across these segments.
My fallback is to create a validation that checks these before commit
but my normal route in a non-ORM context is to let the DB enforce it
and catch it there.
Thanks!
John-
Re: Creating Compound PKs
Posted by John Armstrong <si...@gmail.com>.
I see your consideration on the id triple key and completely agree
with your points. I do have my relationships mapped and its been
running very smoothly.
Time to edit the DB. I've been avoiding it so far since I'm working
with Derby, its been quite nice!
Thanks for taking the time to answer my newbie questions. Just
starting into Cayenne and am enjoying it greatly. Its reducing the
development time on my app substantially.
John-
On 6/4/07, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> On Jun 4, 2007, at 9:52 AM, John Armstrong wrote:
>
> > I can enforce it unique in the database but I was thinking that
> > Cayenne may have a more elegant way of managing it without having to
> > manually apply the database changes after schema creation. To me that
> > screamed 'New compound PK'.
>
> To me it doesn't :-)
>
> Seriously, ORM tool (CayenneModeler) doesn't attempt to replace your
> DB management tools 100%, although it overlaps a lot. You should
> still use DB facilities as appropriate.
>
> > How do you create compound PK's in Cayenne? I'm still using the
> > Modeller so I may have to edit the XML natively?
>
> Go to the attributes tab of the given DbEntity and check the PK
> checkboxes of those two extra columns.
>
> But note that you would need to delete the "id" column then,
> otherwise the UNIQUE constraint would span all three columns, not
> just the two you wanted.
>
> Also you would need to mark a relationship from ImapServer to the
> table in question as "To Dep Pk", so that the serverid could be
> propagated whenever you do myObject.setServer(myServer).
>
> Andrus
>
Re: Creating Compound PKs
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jun 4, 2007, at 9:52 AM, John Armstrong wrote:
> I can enforce it unique in the database but I was thinking that
> Cayenne may have a more elegant way of managing it without having to
> manually apply the database changes after schema creation. To me that
> screamed 'New compound PK'.
To me it doesn't :-)
Seriously, ORM tool (CayenneModeler) doesn't attempt to replace your
DB management tools 100%, although it overlaps a lot. You should
still use DB facilities as appropriate.
> How do you create compound PK's in Cayenne? I'm still using the
> Modeller so I may have to edit the XML natively?
Go to the attributes tab of the given DbEntity and check the PK
checkboxes of those two extra columns.
But note that you would need to delete the "id" column then,
otherwise the UNIQUE constraint would span all three columns, not
just the two you wanted.
Also you would need to mark a relationship from ImapServer to the
table in question as "To Dep Pk", so that the serverid could be
propagated whenever you do myObject.setServer(myServer).
Andrus
Re: Creating Compound PKs
Posted by John Armstrong <si...@gmail.com>.
Yes ID is a unique PK but I also want to enforce uniqueness across the
NAME/SERVERID columns (A given Name can only be valid once per
serverid).
I can enforce it unique in the database but I was thinking that
Cayenne may have a more elegant way of managing it without having to
manually apply the database changes after schema creation. To me that
screamed 'New compound PK'.
How do you create compound PK's in Cayenne? I'm still using the
Modeller so I may have to edit the XML natively?
Thanks Andrus
John-
On 6/3/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> Hmm... is this even a PK generation question? I understand that "id"
> is a unique PK already? Is there a need to define name/serverid as a
> part of PK as well? I mean you can (and Cayenne will support it), but
> what's the point. Maybe instead you can define a UNIQUE constraint on
> those two columns in your table?
>
> Andrus
>
>
> On Jun 4, 2007, at 9:25 AM, John Armstrong wrote:
>
> > Yes, sorry misplaced that bit of important information.
> >
> > ImapServer
> > account
> > id
> > password
> > serverip
> >
> > Tx
> > John-
> >
> >
> >
> > On 6/3/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> >> Hi John,
> >>
> >> A question - is "serverid" a foreign key to another table by any
> >> chance?
> >>
> >> Andrus
> >>
> >>
> >> On Jun 4, 2007, at 9:09 AM, John Armstrong wrote:
> >>
> >> > Does Cayenne allow for the creation of a compound PK? I searched
> >> the
> >> > archives and google more generally and could only find hints at
> >> this.
> >> > The docs did not clarify either (although it was also hinted at. .I
> >> > think, its a bit late :) ).
> >> >
> >> > My table looks like this:
> >> >
> >> > id
> >> > name
> >> > serverid
> >> > status
> >> > enabled
> >> >
> >> > I need to create a compound PK on name/serverid that will maintain
> >> > uniqueness across these segments.
> >> >
> >> > My fallback is to create a validation that checks these before
> >> commit
> >> > but my normal route in a non-ORM context is to let the DB
> >> enforce it
> >> > and catch it there.
> >> >
> >> > Thanks!
> >> > John-
> >> >
> >>
> >>
> >
>
>
Re: Creating Compound PKs
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hmm... is this even a PK generation question? I understand that "id"
is a unique PK already? Is there a need to define name/serverid as a
part of PK as well? I mean you can (and Cayenne will support it), but
what's the point. Maybe instead you can define a UNIQUE constraint on
those two columns in your table?
Andrus
On Jun 4, 2007, at 9:25 AM, John Armstrong wrote:
> Yes, sorry misplaced that bit of important information.
>
> ImapServer
> account
> id
> password
> serverip
>
> Tx
> John-
>
>
>
> On 6/3/07, Andrus Adamchik <an...@objectstyle.org> wrote:
>> Hi John,
>>
>> A question - is "serverid" a foreign key to another table by any
>> chance?
>>
>> Andrus
>>
>>
>> On Jun 4, 2007, at 9:09 AM, John Armstrong wrote:
>>
>> > Does Cayenne allow for the creation of a compound PK? I searched
>> the
>> > archives and google more generally and could only find hints at
>> this.
>> > The docs did not clarify either (although it was also hinted at. .I
>> > think, its a bit late :) ).
>> >
>> > My table looks like this:
>> >
>> > id
>> > name
>> > serverid
>> > status
>> > enabled
>> >
>> > I need to create a compound PK on name/serverid that will maintain
>> > uniqueness across these segments.
>> >
>> > My fallback is to create a validation that checks these before
>> commit
>> > but my normal route in a non-ORM context is to let the DB
>> enforce it
>> > and catch it there.
>> >
>> > Thanks!
>> > John-
>> >
>>
>>
>
Re: Creating Compound PKs
Posted by John Armstrong <si...@gmail.com>.
Yes, sorry misplaced that bit of important information.
ImapServer
account
id
password
serverip
Tx
John-
On 6/3/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> Hi John,
>
> A question - is "serverid" a foreign key to another table by any chance?
>
> Andrus
>
>
> On Jun 4, 2007, at 9:09 AM, John Armstrong wrote:
>
> > Does Cayenne allow for the creation of a compound PK? I searched the
> > archives and google more generally and could only find hints at this.
> > The docs did not clarify either (although it was also hinted at. .I
> > think, its a bit late :) ).
> >
> > My table looks like this:
> >
> > id
> > name
> > serverid
> > status
> > enabled
> >
> > I need to create a compound PK on name/serverid that will maintain
> > uniqueness across these segments.
> >
> > My fallback is to create a validation that checks these before commit
> > but my normal route in a non-ORM context is to let the DB enforce it
> > and catch it there.
> >
> > Thanks!
> > John-
> >
>
>
Re: Creating Compound PKs
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi John,
A question - is "serverid" a foreign key to another table by any chance?
Andrus
On Jun 4, 2007, at 9:09 AM, John Armstrong wrote:
> Does Cayenne allow for the creation of a compound PK? I searched the
> archives and google more generally and could only find hints at this.
> The docs did not clarify either (although it was also hinted at. .I
> think, its a bit late :) ).
>
> My table looks like this:
>
> id
> name
> serverid
> status
> enabled
>
> I need to create a compound PK on name/serverid that will maintain
> uniqueness across these segments.
>
> My fallback is to create a validation that checks these before commit
> but my normal route in a non-ORM context is to let the DB enforce it
> and catch it there.
>
> Thanks!
> John-
>