☰
Navigation :

Constraints and indexes declarations

alt text alt text alt text alt text alt text alt text

Demo application screenshots for this article

KittyORM supports indexes and constraints declaration by using annotations. Right now KittyORM offers you functionality to define seven supported by SQLite constraints without any need to define them in raw SQL code. Also in such way you can define table indexes. KittyORM is capable to define database schema with nearly all features that are supported by SQLite (SQL As Understood By SQLite) with usage only of annotations. In demo you can play with form for creating and inserting entity that declares all constraints and index.

Table of contents

  1. NOT NULL constraint declaration
  2. DEFAULT constraint declaration
  3. UNIQUE constraint declaration
  4. CHECK constraint declaration
  5. COLLATE constraint declaration
  6. PRIMARY KEY constraint declaration
  7. FOREIGN KEY constraint declaration
  8. Indexes declaration

NOT NULL constraint declaration

To declare NOT NULL constraint just annotate corresponding model field with @NOT_NULL annotation. Example: 

1@KITTY_COLUMN(columnOrder = 0)
2@PRIMARY_KEY
3@NOT_NULL // NOT NULL constraint declaration
4public Long id;
Back to table of contents ^

DEFAULT constraint declaration

To declare DEFAULT constraint just annotate corresponding model field with @DEFAULT annotation. Example: 

1@KITTY_COLUMN(columnOrder = 3)
2@DEFAULT(signedInteger = 28) // You can choose for options for default declaration, if nothing set than 0 value would be used
3@NOT_NULL
4public Integer defaultNumber;
Without setting any fields of @DEFAULT annotation then default value for annotated field would be 0 (int, zero). KittyORM provide you some options on declaring default constraints, for example, you can set as a default value for field predefined LiteralValues enum element, signed integer, literal value or expression. Example of declaring DEFAULT constraint from predefined literals: 
1@KITTY_COLUMN(columnOrder = 4)
2@DEFAULT(
3        predefinedLiteralValue = LiteralValues.CURRENT_DATE
4)
5@NOT_NULL
6public String creationDate;
By design, KittyORM tries to insert NULL value from entity field to database at insert, so to avoid inserting NULL to database column that should acquire value from DEFAULT constraint before inserting model you have to invoke on this model KittyModel.setFieldExclusion("modelFieldName") method. Example: 
1IndexesAndConstraintsModel model = new IndexesAndConstraintsModel();
2model.animal = someAnimal;
3...
4model.setFieldExclusion("creationDate"); // Forces KittyORM to exclude this field at insertion so DEFAULT constraint would be triggered
5...
6KittyMapper mapper = getDatabase().getMapper(IndexesAndConstraintsModel.class);
7mapper.save(model);
8mapper.close();
Back to table of contents ^

UNIQUE constraint declaration

You can declare UNIQUE constraint in two ways:

  1. To declare UNIQUE constraint only on one column than just annotate model corresponding field with @UNIQUE annotation. Example: 

    1@KITTY_COLUMN(columnOrder = 1)
2@NOT_NULL
3@UNIQUE
4public Long rndId;
    In order to set conflict clause just set @UNIQUE.onConflict field with any suitable value from ConflictClauses enum.

  2. To declare UNIQUE constraint on more than one column annotate model with @UNIQUE_T annotation. Example: 

     1@KITTY_TABLE(tableName = "cai")
 2@FOREIGN_KEY_T(
 3        name = "CAI_FK",
 4        columns = {IndexesAndConstraintsModel.RANDOM_ID_CNAME},
 5        reference = @FOREIGN_KEY_REFERENCE(
 6                foreignTableName = "random",
 7                foreignTableColumns = {"id"},
 8                onUpdate = OnUpdateDeleteActions.CASCADE,
 9                onDelete = OnUpdateDeleteActions.CASCADE
10        )
11)
12@INDEX(indexColumns = {"creation_date"}) 
13@UNIQUE_T(columns = {"rnd_id, animal"}) // Declaring unique constraint on more than two columns
14public class IndexesAndConstraintsModel extends KittyModel {
15    ...
16}
    In order to set conflict clause just set @UNIQUE_T.onConflict field with any suitable value from ConflictClauses enum.
    In order to set UNIQUE constraint name set @UNIQUE_T.name otherwise it would be generated automatically.
    If you need more than one UNIQUE constraint declaration defined with usage of @UNIQUE_T annotation, annotate model with UNIQUE_T_ARRAY.

Back to table of contents ^

CHECK constraint declaration

To declare CHECK constraint just annotate corresponding model field with @CHECK annotation and specify check expression. Example: 

1@KITTY_COLUMN(columnOrder = 2)
2@CHECK(checkExpression = "animal IN (\"CAT\", \"TIGER\", \"LION\")") // only cats allowed to this party
3public Animals animal;

Back to table of contents ^

COLLATE constraint declaration

To declare COLLATE constraint just annotate corresponding model field with @COLLATE annotation and specify built-in collation. Example: 

1@KITTY_COLUMN(columnOrder = 2)
2@COLLATE(collation = BuiltInCollations.NOCASE) // Collation example
3@CHECK(checkExpression = "animal IN (\"CAT\", \"TIGER\", \"LION\")") 
4public Animals animal;

Back to table of contents ^

PRIMARY KEY constraint declaration

You can declare PRIMARY KEY constraint in three ways:

  1. To define INTEGER PRIMARY KEY just set @KITTY_COLUMN.isIPK field value to true of corresponding model field. Example: 

    1@KITTY_COLUMN(
2        columnOrder = 0, 
3        isIPK = true
4)
5public Long id;

  2. Second way to define PRIMARY KEY is to annotate corresponding model field with @PRIMARY_KEY annotation. Example: 

    1@KITTY_COLUMN(columnOrder = 0)
2@PRIMARY_KEY
3@NOT_NULL
4public Long id;
    In order to set order just set PRIMARY_KEY.orderAscDesc field with any suitable value from AscDesc enum.
    In order to set AUTOIMCREMENT flag set @PRIMARY_KEY.autoincrement field to true (default false).
    In order to set conflict clause just set @PRIMARY_KEY.onConflict field with any suitable value from ConflictClauses enum.

  3. Third way to define PRIMARY KEY is to annotate model implementation with @PRIMARY_KEY_T annotation and specify @PRIMARY_KEY_T.columns array. Example: 

     1@KITTY_TABLE(tableName = "cpk_test")
 2@PRIMARY_KEY_T(
 3    columns = {"user_name", "email"}
 4)
 5public class CPKModel extends KittyModel {
 6
 7    @KITTY_COLUMN(columnOrder = 0)
 8    public String userName;
 9
10    @KITTY_COLUMN(columnOrder = 1)
11    @UNIQUE
12    public String email;
13    
14    ...
15}
    In order to set PRIMARY KEY constraint name set @PRIMARY_KEY_T.name otherwise it would be generated automatically.
    In order to set conflict clause just set @PRIMARY_KEY_T.onConflict field with any suitable value from ConflictClauses enum.

Back to table of contents ^

FOREIGN KEY constraint declaration

You can declare FOREIGN KEY constraint in two ways:

  1. If you have only one column at table that refer to another table column you can just annotate corresponding model field with @FOREIGN_KEY annotation and specify @FOREIGN_KEY.reference with @FOREIGN_KEY_REFERENCE. Example: 

     1@KITTY_COLUMN(columnOrder = 1)
 2@NOT_NULL
 3@UNIQUE
 4@FOREIGN_KEY(
 5        reference = @FOREIGN_KEY_REFERENCE(
 6                foreignTableName = "random",
 7                foreignTableColumns = {"id"},
 8                onUpdate = OnUpdateDeleteActions.CASCADE,
 9                onDelete = OnUpdateDeleteActions.CASCADE
10        )
11)
12public Long rndId;

  2. If you have more than one reference column at FOREIGN KEY declaration, annotate model with @FOREIGN_KEY_T annotation and specify @FOREIGN_KEY_T.reference with @FOREIGN_KEY_REFERENCE and @FOREIGN_KEY_T.columns with string array of reference columns. Example: 

     1@KITTY_TABLE(tableName = "cai")
 2@FOREIGN_KEY_T(
 3        name = "CAI_FK",
 4        columns = {IndexesAndConstraintsModel.RANDOM_ID_CNAME},
 5        reference = @FOREIGN_KEY_REFERENCE(
 6                foreignTableName = "random",
 7                foreignTableColumns = {"id"},
 8                onUpdate = OnUpdateDeleteActions.CASCADE,
 9                onDelete = OnUpdateDeleteActions.CASCADE
10        )
11)
12@INDEX(indexColumns = {"creation_date"})
13public class IndexesAndConstraintsModel extends KittyModel {
14    ...
15    
16    @KITTY_COLUMN(columnOrder = 1)
17    @NOT_NULL
18    @UNIQUE
19    public Long rndId;
20
21    ...
22}
    In order to set FOREIGN KEY constraint name set @FOREIGN_KEY_T.name otherwise it would be generated automatically.

If you need more than one FK that can be declared only with @FOREIGN_KEY_T you can annotate model implementation @FOREIGN_KEY_T_ARRAY and specify at @FOREIGN_KEY_T_ARRAY.foreignKeys all foreign keys you need.
At @FOREIGN_KEY_REFERENCE annotation you have to specify reference table and columns by setting @FOREIGN_KEY_REFERENCE.foreignTableName and @FOREIGN_KEY_REFERENCE.foreignTableColumns. Optionally you can specify ON UPDATE and ON DELETE actions by setting @FOREIGN_KEY_REFERENCE.onUpdate and @FOREIGN_KEY_REFERENCE.onDelete with enum element from OnUpdateDeleteActions. Also, you can specify defferable option by setting @FOREIGN_KEY_REFERENCE.deferrableOption with some value DeferrableOptions enumeration.

Do not forget to turn on foreign keys supports by setting @KITTY_DATABASE.isPragmaOn to true at your KittyORM database implementation if you want to use them!

Tip #1

Back to table of contents ^

Indexes declaration

In KittyORM indexes declarations stored at same POJO classes that are used for schema generation. To declare an index just annotate model implementation with columns that would be indexed with @INDEX annotation and set @INDEX.indexColumns with array of those indexed columns or in case when there is only one indexed column for one index declaration just annotate corresponding nodel implementation field with @ONE_COLUMN_INDEX. Example:

  1. Index declaration with @INDEX annotation: 

     1@KITTY_TABLE(tableName = "cai")
 2@FOREIGN_KEY_T(
 3        name = "CAI_FK",
 4        columns = {IndexesAndConstraintsModel.RANDOM_ID_CNAME},
 5        reference = @FOREIGN_KEY_REFERENCE(
 6                foreignTableName = "random",
 7                foreignTableColumns = {"id"},
 8                onUpdate = OnUpdateDeleteActions.CASCADE,
 9                onDelete = OnUpdateDeleteActions.CASCADE
10        )
11)
12@INDEX(indexColumns = {"creation_date"}) // index declaration
13public class IndexesAndConstraintsModel extends KittyModel {
14    ...
15
16    @KITTY_COLUMN(columnOrder = 4)
17    @DEFAULT(
18            predefinedLiteralValue = LiteralValues.CURRENT_DATE
19    )
20    @NOT_NULL
21    public String creationDate; // indexed column
22
23    ...
24}

  2. Index declaration with @ONE_COLUMN_INDEX annotation: 

     1@KITTY_TABLE(tableName = "cai")
 2...
 3public class IndexesAndConstraintsModel extends KittyModel {
 4    ...
 5
 6    @KITTY_COLUMN(columnOrder = 5)
 7    @DEFAULT(
 8            predefinedLiteralValue = LiteralValues.CURRENT_TIMESTAMP
 9    )
10    // One column indexe declaration example
11    @ONE_COLUMN_INDEX(unique = true, indexName = "IAC_unique_index_creation_timestamp") 
12    @NOT_NULL
13    public Timestamp creationTmstmp;
14
15    ...
16}

For both @INDEX and @ONE_COLUMN_INDEX index declaration you can specify index uniqueness (@INDEX.unique and @ONE_COLUMN_INDEX.unique fields), IF NOT EXISTS flag (@INDEX.ifNotExists and @ONE_COLUMN_INDEX.ifNotExists fields), where expression (@INDEX.whereExpression and @ONE_COLUMN_INDEX.whereExpression fields) and index name (@INDEX.indexName and @ONE_COLUMN_INDEX.indexName fields).
If you need more than one index declaration with more than one indexed columns than annotate model implementation with @INDEX_ARRAY annotation and define your indexes there.

Back to table of contents ^