Re: [eclipselink-users] How to map cyclic foreign key references with no

[janne postilista <jannepostilistat@xxxxxxxxx>]

Thanks,

 fixed this by making FOO.BAR_ID nullable and adding constraint

ALTER TABLE FOO
ADD CONSTRAINT FOO_BAR_CHK1 CHECK
(BAR IS NOT NULL)
DEFERRABLE INITIALLY DEFERRED
ENABLE;

On Thu, Jul 28, 2011 at 7:07 PM, Christopher Delahunt
<christopher.delahunt@xxxxxxxxxx> wrote:
> Because of your constraints, you cannot insert a row into FOO unless it can
> reference an existing row in BAR but you cannot insert into BAR until it can
> reference an existing row in FOO.
> So there is no way to insert a row in either table without turning a
> constraint off or delaying constraint processing on your database until the
> transaction is done.
> If you can't delay constraint processing on the database until the
> transaction commits, then I'd recommend you remove the not-null constraints
> and have application logic prevent it from
> being set to null.
> Best Regards,
> Chris
>
>
> On 28/07/2011 10:34 AM, janne postilista wrote:
>>
>> How do I map the following scenario:
>>
>> TABLE FOO (
>>  ID NUMBER(10) PRIMARY KEY NOT NULL,
>>  BAR_ID NUMBER(10) FOREIGN KEY NOT NULL
>> );
>>
>> TABLE BAR (
>>  ID NUMBER(10) PRIMARY KEY NOT NULL,
>>  FOO_ID NUMBER(10) FOREIGN KEY NOT NULL
>> );
>>
>> ids from sequences SEQ_FOO and SEQ_BAR.
>>
>> When I try to persist/merge this kind of dual object I always get:
>>
>> Caused by: org.h2.jdbc.JdbcSQLException: NULL not allowed for column
>> "BAR_ID"; SQL statement:
>> INSERT INTO FOO (ID, BAR_ID) VALUES (?, ?) [23502-154]
>>        at
>> org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
>>        at org.h2.message.DbException.get(DbException.java:167)
>>        at org.h2.message.DbException.get(DbException.java:144)
>>        at
>> org.h2.table.Column.validateConvertUpdateSequence(Column.java:297)
>>        at org.h2.table.Table.validateConvertUpdateSequence(Table.java:669)
>>
>> My mappings:
>>
>> @Entity
>> @Table(name = "FOO")
>> public class Foo {
>>
>>    @Id
>>    @Column(nullable = false)
>>    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator =
>> "FOO_GEN")
>>    @SequenceGenerator(name = "FOO_GEN", sequenceName = "SEQ_FOO",
>> allocationSize = 1)
>>    private Long id;
>>
>>    @OneToOne
>>    @JoinColumn(name = "BAR_ID")
>>    private Bar bar;
>>
>>    public Long getId() {
>>        return id;
>>    }
>>
>>    public void setId(Long id) {
>>        this.id = id;
>>    }
>>
>>    public Bar getBar() {
>>        return bar;
>>    }
>>
>>    public void setBar(Bar bar) {
>>        this.bar = bar;
>>    }
>>
>>
>> }
>>
>>
>>
>> @Entity
>> @Table(name = "BAR")
>> public class Bar {
>>
>>    @Id
>>    @Column(nullable = false)
>>    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator =
>> "BAR_GEN")
>>    @SequenceGenerator(name = "BAR_GEN", sequenceName = "SEQ_BAR",
>> allocationSize = 1)
>>    private Long id;
>>
>>    @OneToOne
>>    @JoinColumn(name = "FOO_ID")
>>    private Foo foo;
>>
>>    public Long getId() {
>>        return id;
>>    }
>>
>>    public void setId(Long id) {
>>        this.id = id;
>>    }
>>
>>    public Foo getFoo() {
>>        return foo;
>>    }
>>
>>    public void setFoo(Foo foo) {
>>        this.foo = foo;
>>    }
>>
>> }
>>
>> I get the same result using ManyToOne relationships. I have also tried
>> a combination with Foo having ManyToOne Bar and Bar having OneToOne
>> Foo.
>>
>> Actual relationships are a) unidirectional 1:n mapping and b)
>> unidirectional 1:1 mapping.
>> - n Foo objects all point to same Bar. Bar does not need to know the
>> collection of n Foo objects pointing to it
>> - each Bar points to a single Foo.
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>