Setting a Foreign Key using ebean in Play Framework

Question

I have two model classes

CustomerAccount

@Entity
public class CustomerAccount extends Model {
    @Id
    @Column(columnDefinition = "int(11)")
    public int customer_id;
    @Column(columnDefinition = "varchar(50) not null unique")
    public String customer_email;
    @Column(columnDefinition = "varchar(50) not null")
    public String password;
}

Customer

@Entity
public class Customer extends Model {

    @Column(columnDefinition = "int(11) unique")
    public int customer_id = 6;
    @Column(columnDefinition = "varchar(50) not null")
    public String customer_fname;
    @Column(columnDefinition = "varchar(50) not null")
    public String customer_lname;
    @Column(columnDefinition = "varchar(50) unique not null")
    public String email = "";
}

Now I want to add a foriegn key in table CustomerAccount reference to Customer table like: -

Foreign Key (customer_email) references Customer(email);

Please tell me how to do this...

while running the following code to add details to Customer Account

public static Result addPerson() {
        String result = "ok";
        CustomerAccount Customer_Account = Form.form(CustomerAccount.class).bindFromRequest().get();
        List<CustomerAccount> personsDetails = new Model.Finder(String.class, CustomerAccount.class).all();

        for (CustomerAccount product : personsDetails) {
            if (product.customer.email.equals(Customer_Account.customer.email) ) {
                result = "";
            }
        }
        if (result.equals("ok")) {
            Customer_Account.save();
            return ok("New Customer Account Created");
        }else{
            return ok("Customer with same email Already Exists");
        }

    }

I am getting this error

[PersistenceException: ERROR executing DML bindLog[] error[Column 'customer_email' cannot be null]]

Have you tried any of the annotations used in the documentation yet: https://www.playframework.com/documentation/1.2.x/cheatsheet/model. If so, it would be good to articulate what about them didn't work for you. — Sam Storie, May 13 '15 at 11:58
I am confused how to implement this. I have started learning Play. — Anand Kumar, May 13 '15 at 12:05

biesior · Accepted Answer · 2015-05-13T12:33:18.130

Ebean uses @Id field as foreign key by default, so your models would need look like:

Customer

@Entity
public class Customer extends Model {

    @Id
    @Column(columnDefinition = "varchar(50) not null")
    public String email = "";

    public static Finder<String, Customer> find 
            = new Finder<>(String.class, Customer.class);

    @Column(columnDefinition = "varchar(50) not null")
    public String firstName;

    @Column(columnDefinition = "varchar(50) not null")
    public String lastName;

    @OneToMany(mappedBy = "customer")
    public List<CustomerAccount> accounts = new ArrayList<>();

}

CustomerAccount

@Entity
public class CustomerAccount extends Model {
    @Id
    public Integer id;

    public static Finder<Integer, CustomerAccount> find
            = new Finder<>(Integer.class, CustomerAccount.class);

    @ManyToOne()
    public Customer customer;

    @Column(columnDefinition = "varchar(50) not null")
    public String password;
}

it will generate the DDL:

create table customer (
  email                     varchar(50) not null not null,
  first_name                varchar(50) not null,
  last_name                 varchar(50) not null,
  constraint pk_customer primary key (email))
;

create table customer_account (
  id                        integer auto_increment not null,
  customer_email            varchar(50) not null,
  password                  varchar(50) not null,
  constraint pk_customer_account primary key (id))
;

alter table customer_account add constraint fk_customer_account_customer_1 foreign key (customer_email) references customer (email) on delete restrict on update restrict;
create index ix_customer_account_customer_1 on customer_account (customer_email);

BTW. take a look at annotation @OneToMany(mappedBy="customer") it allows you to fetch all accounts of customer without adding any additional DB columns like:

Customer customer = Customer.find.byId("foo@bar.com");

play.Logger.debug("All accounts of: " + customer.firstName);

for (CustomerAccount account : customer.accounts) {
    play.Logger.debug("ID: " + account.id);
}

Changed my mind ;) You can not set `Customer.field` as unique, cause will not be able to use `ManyToOne` annotation properly, anyway it's the primary key, so database ensures its uniqueness. — biesior, May 13 '15 at 12:19
Check the latest edit, I fixed it a little and added relation also on the Customer to make it bi-directional — biesior, May 13 '15 at 12:34

Setting a Foreign Key using ebean in Play Framework

CustomerAccount

Customer

1 Answers1