Previous Next JavaScript must be enabled to correctly display this content

## Creating Tables with Composite Keys

Learn to create a table with composite primary key fields using Oracle NoSQL Database SDK for Spring Data.

You use the `@NosqlKey` annotation to identify the annotated field as a component of the composite primary key.

Example 2-4 Creating a table with composite primary key fields

The following examples shows how to model `Student` as an entity and use universityId, academicYear, and studentId fields as composite keys.

A Composite key is helpful when you want to use more than one primary key field conjointly to identify a unique row. Within the composite key, you can identify the primary key fields that can be a part of the Shard Key and also specify the ordering of the fields.

The following example shows how to create a composite key with the key fields from student data. You define a class named `StudentKey` to represent the composite key class and then use that in the `Student` entity as described in the following example:

Create a composite key class with the `@NosqlKey` annotation to identify the composite keys. Set the `shardKey` value to `true` if the field is a part of the shard key. Set the `order` value for all the fields in the order of primary key field generation in the table. For more details on the `shardKey` and `order` elements, see Table 1-4.

In the following code sample, the `universityId`, `academicYear`, and `studentId` fields represent the key fields for identifying a student's data and are declared as the primary key fields using the `@NosqlKey` annotation. For an illustration of ordering within the primary key fields, consider two of the primary key fields as shard keys and the third as a non-shard key.

Set the `shardKey` value of the `universityId` and `academicYear` fields to `true`, and the `studentId` field to false. Set the order value for the `universityId` field to 0 to create the `universityId` field as the first primary key field and `academicYear` to 1 to create as second primary key field. As the `studentId` field is a non-shard key, its order value must be higher than the shard keys. Set the `order` value of the `studentId` field to 2.

    import com.oracle.nosql.spring.data.core.mapping.NosqlKey;
    import java.io.Serializable;
    import java.util.Objects;

    /* Define a composite Key class */

    public class StudentKey implements Serializable {

        @NosqlKey(shardKey = true, order = 0)
        long universityId;

        @NosqlKey(shardKey = true, order = 1)
        int academicYear;

        @NosqlKey(shardKey = false, order = 2)
        long studentId;

        /* public or package protected constructor required when retrieving from database */
        public StudentKey() {
        }

        public StudentKey(long universityId, int academicYear, long studentId) {
            this.universityId = universityId;
            this.academicYear = academicYear;
            this.studentId = studentId;
        }

        public long getUniversityId() {
            return universityId;
        }

        public void setUniversityId(long universityId) {
            this.universityId = universityId;
        }

        public int getAcademicYear() {
            return academicYear;
        }

        public void setAcademicYear(int academicYear) {
            this.academicYear = academicYear;
        }

        public long getStudentId() {
            return studentId;
        }

        public void setStudentId(long studentId) {
            this.studentId = studentId;
        }

        /* Define equals method */
        @Override
        public boolean equals(Object o) {
            if (this == o) {
                return true;
            }
            if (!(o instanceof StudentKey)) {
                return false;
            }
            StudentKey studentKey = (StudentKey) o;
            return Objects.equals(universityId, studentKey.universityId) &&
                    Objects.equals(academicYear, studentKey.academicYear) &&
                    Objects.equals(studentId, studentKey.studentId);
        }

        /* Define hashcode method */
        @Override
        public int hashCode() {
            return Objects.hash(universityId, academicYear, studentId);
        }
    }

Create the `Student` entity class with `StudentKey` as a composite primary key. The `StudentKey` is annotated with `@NosqlId` in the entity class to indicate the primary key.

You can declare any non-key fields in the entity class. The non-key fields will be included as JSON data in the`kv_json_` column.

    import com.oracle.nosql.spring.data.core.mapping.NosqlId;
    import com.oracle.nosql.spring.data.core.mapping.NosqlTable;
    import com.oracle.nosql.spring.data.core.mapping.NosqlKey;

    import java.io.Serializable;
    import java.util.Objects;

    /*The @NosqlTable annotation specifies that
      this class will be mapped to an Oracle NoSQL Database table.*/

    @NosqlTable

    public class Student {
        @NosqlId
        StudentKey studentKey;
        String firstName;
        String lastName;
        String resident;

        /* public or package protected constructor required when retrieving from database */
        public Student() {
            studentKey = new StudentKey();
        }

        /*This method overrides the toString() method, and then concatenates id and name, and then returns a String*/
        @Override
        public String toString() {
            return "Student{" +
                    "universityId=" + studentKey.universityId + ", " +
                    "academicYear=" + studentKey.academicYear + ", " +
                    "studentId=" + studentKey.studentId + ", " +
                    "firstName=" + firstName + ", " +
                    "lastName=" + lastName + ", " +
                    "resident=" + resident+
                    '}';
        }
    }

Note:

You must set up the `AppConfig` class that provides a `NosqlDbConfig` Spring bean. The `NosqlDbConfig` Spring bean describes how to connect to the Oracle NoSQL Database. You must also create an interface that extends the `NosqlRepository` interface to retrieve the data from the Oracle NoSQL Database. For details, see the section Accessing Oracle NoSQL Database Using Spring Data Framework.
The Spring Data Framework creates the `Student` table with the following DDL:

    /* Student table DDL */

    CREATE TABLE IF NOT EXISTS Student (
       universityId LONG,
       academicYear INTEGER,
       studentId LONG,
       kv_json_ JSON,
       PRIMARY KEY(SHARD(universityId, academicYear), studentId)
    )

The primary key fields `universityId` and `academicYear` are also shard keys and `studentId` is a non-shard primary key field.