Constraints in JPA (using Hibernate)

Thread view:

Rich - 23 Sep 2007 12:23 GMT

Hi,

I have some @Entitys in JPA with Hibernate which create the following
relationship:

Brand 1<->* User
User 1<->* CreditCard

(where 1<->* means a bidirectional one-many relationship)

I want to be able to put a constraint on the CreditCard entity such
that the field CreditCard.CreditCardNumber is unique for a particular
Brand.

Without adding a reference to Brand in the CreditCard entity, is there
a way that I can achieve this?

Thanks in advance for any pointers!

Rich

Reply to this Message

Lew - 23 Sep 2007 17:24 GMT

> Hi,
>
[quoted text clipped - 5 lines]
>
> (where 1<->* means a bidirectional one-many relationship)

What do you mean it's "bidirectional"?

> I want to be able to put a constraint on the CreditCard entity such
> that the field CreditCard.CreditCardNumber is unique for a particular
> Brand.
>
> Without adding a reference to Brand in the CreditCard entity, is there
> a way that I can achieve this?

No. One-to-many-to-many means there are many CreditCard entries for each
Brand. You want to restrict it to a single CreditCard row per Brand row.
That is contradictory to the one-to-many relationships you asserted.

If you do add a foreign key ("reference", somewhat overloaded term) from
CreditCard to Brand you will have circular foreign keys which is a Bad Thing.

Signature

Lew

Reply to this Message

Lew - 23 Sep 2007 17:58 GMT

>> Hi,
>>
[quoted text clipped - 14 lines]
>> Without adding a reference to Brand in the CreditCard entity, is there
>> a way that I can achieve this?

> No. One-to-many-to-many means there are many CreditCard entries for
> each Brand. You want to restrict it to a single CreditCard row per
[quoted text clipped - 4 lines]
> CreditCard to Brand you will have circular foreign keys which is a Bad
> Thing.

I stated that wrong. I meant "from Brand to CreditCard".

Adding a FK from CreditCard to Brand would just be redundant, not circular.

You need an third table which maps the PK of Brand to the PK of CreditCard,
with FKs from each column to the respective tables, and the two columns
together constituting the PK.

Signature

Lew

Reply to this Message

Lew - 23 Sep 2007 18:03 GMT

> You need an third table which maps the PK of Brand to the PK of
> CreditCard, with FKs from each column to the respective tables, and the
> two columns together constituting the PK.

Oops. Another mistake. Just the Brand PK is the PK of the third table.

Are you saying that a User can only ever use a single CreditCard for a given
Brand?

It looks a lot like you need to redesign your data model. You might be
missing something like Purchase.

Signature

Lew

Reply to this Message

Rich - 23 Sep 2007 18:33 GMT

> > Hi,
>
[quoted text clipped - 7 lines]
>
> What do you mean it's "bidirectional"?

Meaning that Brand.class has:
private List<User> users;

And User.class has:
private Brand brand;

ie the relationship is in both directions.

> > I want to be able to put a constraint on the CreditCard entity such
> > that the field CreditCard.CreditCardNumber is unique for a particular
[quoted text clipped - 5 lines]
> No. One-to-many-to-many means there are many CreditCard entries for each
> Brand. You want to restrict it to a single CreditCard row per Brand row.

No, I don't want to restrict it to a single CreditCard row per Brand
row. I want the credit card numbers associated with the users of brand
X to be unique amongst themselves, but the same credit card number can
appear twice in the credit card table, provided that the brand is
different. I'm looking for a kind of compound key across tables, I
suppose...

Reply to this Message

Daniel Pitts - 23 Sep 2007 19:33 GMT

> Hi,
>
[quoted text clipped - 16 lines]
>
> Rich

This sounds more like a business rule than a data integrity problem.

When ever a new CreditCard entity is saved, you must first do a
validation step to assert that its not duplicate.

Reply to this Message

Constraints in JPA (using Hibernate)

Free Magazines