How to insert only 3 columns data in room database table if we have more columns?

I have a project that I wrote in kotlin. I want to insert data in different columns of the same table on different pages. I have specified these columns in the dataclass, but it gives a null data error.

In order to make this insert process more healthy, should I divide the table into two separate tables or send static 'null' data and update these fields?

Solution

In a database, such as SQLite (which Room is a wrapper around), the unit of insertion is a row.

A row will consist of the same number of columns. You cannot insert a column on it's own, other than if you ALTER the table to add or remove a column, when the change is reflected throughout the entire table.

if adding a column then a DEFAULT VALUE must be provided, this could be the default/implicit value of null or another specific value.
Room with Kotlin will apply a constraint (rule) of NOT NULL unless nulls are specifically allowed using for example ?
- var theColumn: Int has the implicit NOT NULL
- var theColumn: Int? does not have the implicit NOT NULL and nulls can be stored
- var theColumn: Int=-1 will apply a default value of -1 in the absence of the field not being supplied a value when instantiating the object.
- var theColumn: Int?=null will apply null in the absence of the field not being supplied a value when instantiating the object.
  - obviously fields may be altered before inserting the object, if var rather than val is used.

The data stored in the column can be interpreted to represent whatever you wish, often NULL will be used to represent a special situation such as no value.

If using an @Insert annotated function, then ALL columns are applied the values as obtained from the object or objects passed to the function. In Kotlin whether or not NULLs can be used is dependent upon the field definition or in some cases the @NonNull annotation.

@Insert indicates what is termed as a convenience method, it actually builds the underlying SQL along with binding the values using the SQLite API.

However, if you want flexibility, then an @Query annotation with suitable INSERT SQL statement can be used.

e.g. you could perhaps have a table that has 4 columns COL1, COL2, COL3 and COL4 and only apply some of the columns (the DEFAULT VALUE will be applied to the other column if specified, if not the NULL but if there is a NOT NULL constraint then a conflict would be raised).

So to insert when only two of the columns (COL2 and COL4) then you could use:-

@Query("INSERT INTO theTable (COL2,COL4) VALUES(:valueForCol2,:valueForCol4)")
fun insertCol2AndCol4Only(valueForCol2: Int, valueForCol4: Int?)

Note that valueForCol4 could be NULL. However, whether or not a NULL will result in a conflict depends upon how the field is defined in the @Entity annotated class.

Conflicts (breaking a rule) can be handled by SQLite, depending upon the type of the conflict. UNIQUE, PRIMARY KEY (which is really a UNIQUE conflict), CHECK (Room doesn't cater for CHECK constraints) and NOT NULL constraints can be handled in various ways at the SQLite level.

A common use of conflict handling is to IGNORE the conflict, in which case the action (INSERT or UPDATE) is ignored. In the case of INSERT the row is not inserted but SQLite ignores the conflict and doesn't issue an error.

So if for example COL4's field was var COL4: Int and not var COL4: Int? then the insert would fail and an SQlite Exception would occurr.

However if instead

@Query("INSERT OR IGNORE INTO theTable (COL2,COL4) VALUES(:valueForCol2,:valueForCol4)")

were used and the COL4 field were defined as var COL4: Int (implied NOT NULL constraint) then the conflict if NULL was passed as valueForCol4 then the row would not be inserted but no failure would occur as the NOT NULL conflict would be ignored.

With the @Insert annotation you can defined this conflict handling via the onConflictStrategy parameter e.g. @Insert(onConflictStrategy=OnConflict.IGNORE)

You may wish to consider reading the following:-

The On Conflict Clause
INSERT

In order to make this insert process more healthy, should I divide the table into two separate tables or send static 'null' data and update these fields?

Note the above is only a summary, INTEGER PRIMARY KEY aka @PrimaryKey var id: Long?=null or variations such as @PrimaryKey(autoGenerate=true) etc has specifically not been discussed.

The design of the database could be handled either way, from the very limited description of the scenario, a most likely suitable scenario cannot really be advised, although either could probably be an approach.

Additional

Based upon the comment:-

For example, I'm going to add the features of a car to the database, but it could be a different type at a time. So on the first page, the type of car will be chosen, like off road, sedan, 4x4, hatchback.

The perhaps consider having a feature table and a mapping table for a many-many relationship between car and it's features as per my response:-

I would suggest that features be a table and with a many-many relationship with the car. That is a car could have a 0-n features and a feature could be used by 0-n cars. The many-many relationship would require a third table known by many terms such as an associative table/reference table/ mapping table. Such a table has 2 core columns a column to map to the car and a column to map to the feature, the primary key being a composite of both these columns.

Here's a basic example of how this could work from an SQLite basis:-

DROP INDEX IF EXISTS carFeatureMap_idxon_feature;
DROP TABLE IF EXISTS carFeatureMap;
DROP TABLE IF EXISTS car;
DROP TABLE IF EXISTS feature;

CREATE TABLE IF NOT EXISTS car (
    carId INTEGER PRIMARY KEY, 
    carname TEXT /* and other columns */
);

CREATE TABLE IF NOT EXISTS feature (
    featureId INTEGER PRIMARY KEY, 
    featureDescription TEXT
);

CREATE TABLE IF NOT EXISTS carFeatureMap (
    carIdMap INTEGER REFERENCES car(carId) ON DELETE CASCADE ON UPDATE CASCADE, 
    featureIdMap INTEGER REFERENCES feature(featureId) ON DELETE CASCADE ON UPDATE CASCADE, 
    PRIMARY KEY(carIdMap, featureIdMap)
);
/* Should improve efficiency of mapping from a feature */
CREATE INDEX IF NOT EXISTS carFeatureMap_idxon_feature ON carFeatureMap(featureIdMap);
/* Add some features */
INSERT OR IGNORE INTO feature VALUES(100,'4x4'),(101,'Sedan'),(106,'Convertable'),(null /*<<<< featureId generated by SQLite*/ ,'Hatchback');
/*Report1 Output the features */
SELECT * FROM feature;
/* Add some cars */
INSERT OR IGNORE INTO car VALUES(10,'Car1'),(20,'Car2'),(30,'Car3');
/*Report2 Output the cars */
SELECT * FROM car;
/* add the mappings/relationships/associations between cars and features */
INSERT OR IGNORE INTO carFeatureMap VALUES (10,101) /* Car 1 has 4x4*/,(10,106) /* Car 1 has Sedan */,(20,100);

/*Report3 Get the Cars with features cartesian product */
SELECT 
    car.carName,
    featureDescription 
    FROM car 
    JOIN carFeatureMap ON car.carId=carFeatureMap.carIdMap 
    JOIN feature ON featureIdMap=featureId
;
/*Report4 Get the Cars with features with all the features concatendated, i.e. single output per car with features */
SELECT 
    car.carName,
    group_concat(featureDescription,': ') AS allFeatures 
    FROM car 
    JOIN carFeatureMap ON car.carId=carFeatureMap.carIdMap 
    JOIN feature ON featureIdMap=featureId GROUP BY (carId)
;

/*Report5 Similar to the previous BUT if no features then output none so ALL cars are output */
SELECT 
    carName,
    coalesce(
        (
            SELECT 
                group_concat(featureDescription) 
                FROM feature 
                JOIN carFeatureMap ON carFeatureMap.featureIdMap=featureId AND carFeatureMap.carIdMap=carId
        ),
        'none'
    ) AS features
FROM car
;
/* Clean Up After Demo*/
DROP INDEX IF EXISTS carFeatureMap_idxon_feature;
DROP TABLE IF EXISTS carFeatureMap;
DROP TABLE IF EXISTS car;
DROP TABLE IF EXISTS feature;

Results from the demo code above

Report1 - The features

Report2 - The cars

**Report3 ** Cars and features

Report 4 Cars and features 2

Report 5 Cars and features 3