Search This Blog

Tuesday 3 January 2012

Foreign Key referencing Nonprimary Keys

Usually a foreign key references a Primary Key. However it is possible to have an extreme scenario wherein the foreign key refers a unique column in another table. To illustrate the above case let us create a simple(but highly contrived) example.
Consider that Student has a vehicle. One student can have many vehicles. The Vehicle table however, instead of referring to student id chooses to use Student name as the foreign key (mighty stupid of vehicle, no doubts).
The Java entities in this scenario would be as normal as always.
public class Student {
    private Integer id;
    private String name;
    private Set<Vehicle> vehicles = new HashSet<Vehicle>();
//setter getter methods
}

public class Vehicle {
    private Integer id;
    private String model;
    private Student student;
//setter getter methods
}
The hbm files is where the difference is:
Vehicle.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.ec.foreign_non_primary">
    <class name="Vehicle" table="VEHICLE">
        <id name="id" type="integer" column="ID">
            <generator class="native" />
        </id>
        <property name="model" column="MODEL" />
        <many-to-one name="student" class="Student" column="STUDENT_NAME"
            property-ref="name">
        </many-to-one>
    </class>
</hibernate-mapping>
A new property-ref attribute has been introduced in the many-to-one mapping element. This tells hibernate that the column name specified here must have a value that matches exactly the value for this column in the other table. Hibernate now knows who the target of the association is. Similarly in the Student.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.ec.foreign_non_primary">
    <class name="Student" table="STUDENT">
        <id name="id" type="integer" column="ID">
            <generator class="native" />
        </id>
        <property name="name" column="NAME" unique="true" />

        <set name="vehicles" cascade="all" inverse="true">
            <key column="STUDENT_NAME" property-ref="name" />
            <one-to-many class="Vehicle" />
        </set>
    </class>
</hibernate-mapping>
property-ref requires that the target property be unique, hence unique="true" has been set in the name property. The below code was used to create data:
static void testCreate() {
    Session session = sessionFactory.openSession();
    Student student = new Student();
    student.setName("#2145");
    Vehicle book1 = new Vehicle();
    book1.setModel("Honda Unicorn");
    book1.setStudent(student);
    student.getVehicles().add(book1);
    Transaction t = session.beginTransaction();
    session.save(student);
    t.commit();
}
The logs indicate successful creation:
2625 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        STUDENT
        (NAME) 
    values
        (?)
...
2656 [main] DEBUG org.hibernate.SQL  - 
    insert 
    into
        VEHICLE
        (MODEL, STUDENT_NAME) 
    values
        (?, ?)
I decided to test out the code to delete a student.
static void deleteElements() {
    Session session = sessionFactory.openSession();
    Transaction t = session.beginTransaction();
    Student student = (Student) session.load(Student.class, 1);
    session.delete(student);
    t.commit();
}
The SQL queries generated are as follows:
  1. Select query to fetch student by identifier( Object needed to be loaded before deletion)
    select
            student0_.ID as ID0_0_,
            student0_.NAME as NAME0_0_ 
        from
            STUDENT student0_ 
        where
            student0_.ID= ?
    
  2. Query to fetch all vehicles for student on the basis of the foreign key. (The cascade means vehicles are to be deleted too. Hence fetching the same.)
    select
            vehicles0_.STUDENT_NAME as STUDENT3_1_,
            vehicles0_.ID as ID1_,
            vehicles0_.ID as ID1_0_,
            vehicles0_.MODEL as MODEL1_0_,
            vehicles0_.STUDENT_NAME as STUDENT3_1_0_ 
        from
            VEHICLE vehicles0_ 
        where
            vehicles0_.STUDENT_NAME= ?
    
  3. Query to fetch student associated with the vehicle.
    select
            student0_.ID as ID0_0_,
            student0_.NAME as NAME0_0_ 
        from
            STUDENT student0_ 
        where
            student0_.NAME= ?
    
    This was done to resolve the vehicle association, although I do not see the need for the same.
  4. Query to delete a vehicle.
    delete 
        from
            VEHICLE 
        where
            ID= ?
    
    As the deletion is id based, this has to be one delete query per vehicle.
  5. Query to now delete the student.
    delete 
        from
            STUDENT 
        where
            ID= ?
    
    All associations to this student record have been terminated before this query was executed.

No comments:

Post a Comment