jpa one to one relationship using mysql

Question

Here is my tables definition

CREATE TABLE IF NOT EXISTS `store` (
`store_id` INT NOT NULL AUTO_INCREMENT,
`store_name` VARCHAR(1024) NOT NULL,
`store_user` INT NOT NULL,
`store_address` INT NOT NULL,
`store_type` INT NOT NULL,
`created_date` DATETIME NOT NULL,
`updated_date` DATETIME NOT NULL,
PRIMARY KEY (`store_id`)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `store_address` (
`address_id` INT NOT NULL AUTO_INCREMENT,
`address_line_1` VARCHAR(1024) NOT NULL,
`address_line_2` VARCHAR(1024) NOT NULL,
`address_line_3` VARCHAR(1024) NULL,
`city` VARCHAR(45) NOT NULL,
`locality` VARCHAR(100) NOT NULL,
`pincode` CHAR(6) NOT NULL,
`latitude` DECIMAL(8,6) NULL,
`longitude` DECIMAL(9,6) NULL,
`state` VARCHAR(45) NOT NULL,
`created_date` DATETIME NOT NULL,
`updated_date` DATETIME NOT NULL,
PRIMARY KEY (`address_id`),
CONSTRAINT `FK_STR_STR_ADR`
FOREIGN KEY (`address_id`)
REFERENCES `store` (`store_address`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

I am trying to have a 1-1 mapping between store and its address. Considering the DDL is ok, while generating JPA entities Store.java look like this:

@Entity
@Table(name="store")
public class Store
{
 @Basic
 @Column(name="created_date", nullable=false)
 private Date createdDate;

 @OneToOne(fetch=FetchType.LAZY, mappedBy="store",    cascade=CascadeType.MERGE)
 private StoreAddress storeAddress;

 @Basic
 @Column(name="store_address", columnDefinition="INT")
 private int storeAddress2;

 /////

Why is there a field storeAddress2 in Store.java? I think this is failing my insertion of a store. Any help?

Answer 1

Considering the DDL is ok [...]

The DDL is not OK, it is erroneous. As the tables are presently structured, the foreign key constraint is backward. store_address.address_id is the referenced key; the constrained column -- that is, the foreign key column -- should be store.store_address.

Moreover, be aware that putting the address into its own table and establishing a NOT NULL foreign key referencing it means that every store must have an address recorded, yet a store address does not have to correspond to any store. If you want the address to be optional then make store.store_address nullable, though that still permits addresses to exist that do not correspond to a store.

Alternatively, even though JPA prefers a forward mapping from parent to child such as you have presented, it is possible to map it in the other direction, so that store addresses cannot exist in the DB without a corresponding store, but stores do not have to have addresses recorded. In the DDL, that would correspond to deleting store.store_address, and creating store_address.store_id as a foreign key referencing store.store_id.

---

Update:

Here is some DDL to clarify my comments about the FK constraint. This is how an FK relationship between store and store_address should be written, given the column definitions as presented in the question:

CREATE TABLE IF NOT EXISTS `store` (
  `store_id` INT NOT NULL AUTO_INCREMENT,
  `store_address` INT NOT NULL,
  -- ...
  PRIMARY KEY (`store_id`),
  CONSTRAINT `FK_STR_STR_ADR`
  FOREIGN KEY (`store_address`)
    REFERENCES `store_address` (`address_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `store_address` (
  `address_id` INT NOT NULL AUTO_INCREMENT,
  -- ...
  PRIMARY KEY (`address_id`)
  -- the FK constraint does NOT go here
)
ENGINE = InnoDB;

Note also that as I already wrote, this does not enforce a 1-1 relationship in the DB. If you want that then you could put a UNIQUE constraint on store.store_address, but it might be better to instead link the PKs of store and store_address. That way you can also prevent orphan store_address rows from being allowed. That could look like this:

CREATE TABLE IF NOT EXISTS `store` (
  `store_id` INT NOT NULL AUTO_INCREMENT,
  -- ... no store_address ...
  PRIMARY KEY (`store_id`)
  -- ... no FK constraint here ...
)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `store_address` (
  -- addresses do not have independent IDs:
  `store_id` INT NOT NULL,
  -- ...
  PRIMARY KEY (`store_id`),
  CONSTRAINT `FK_STR_STR_ADR`
  FOREIGN KEY (`store_id`)
    REFERENCES `store` (`store_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB;

That does permit a store to exist without a corresponding address, which may be sensible, even though you say you don't want that.

Really, though, if a store must not exist without exactly one corresponding address, and a store address must not exist without a store, then why are you mapping these as separate tables? It gains you nothing except, maybe, less manual adjustment to automatically-generated entity classes. It's definitely a loss in the performance and DB complexity arenas.

Note, too, that JPA has annotations for mapping two closely-associated entities to the same table, if you insist that the addresses should be separate entities from their associated stores. Look into the @Embeddable and related annotations.