Constraints in JPA (using Hibernate)

[Rich]

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

 

[Lew]

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)

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.

 

[Lew]

What do you mean it's "bidirectional"?

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.

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.

 

[Lew]

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.

 

[Rich]

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.

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...

 

[Daniel Pitts]

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

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.