Hello,
We are struggling for this almost whole day and we don’t see where is the problem. Everything seems good but we realized that somehow after second connection to database and calling different queries we loose PRIMARY KEY at files table.
Here is what we have and what we call to get error Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: files.id
user.entity.ts
import { Entity } from 'typeorm/decorator/entity/Entity';
import {Column, OneToMany, PrimaryGeneratedColumn} from 'typeorm';
import {Sources} from './sources.entity';
@Entity()
export class Users {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column("varchar", { nullable: true})
email: string;
@OneToMany(type => Sources, source => source.user, {
cascadeInsert: true,
cascadeUpdate: true,
cascadeRemove: true
})
source: Sources[];
}
source_type.entity.ts
import {Entity} from 'typeorm/decorator/entity/Entity';
import {Column, OneToOne, PrimaryGeneratedColumn} from 'typeorm';
import { Sources } from './sources.entity';
@Entity()
export class SourceType {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@OneToOne(type => Sources, sourcesType => sourcesType.sourceType, {
cascadeInsert: true,
cascadeUpdate: true,
cascadeRemove: true
})
source: Sources;
}
sources.entity.ts
import {Entity} from 'typeorm/decorator/entity/Entity';
import {Column, JoinColumn, ManyToOne, OneToMany, OneToOne, PrimaryGeneratedColumn} from 'typeorm';
import { SourceType } from './source_type.entity';
import { Users } from './user.entity';
import {SourcesPaths} from "./sources_paths.entity";
import {Files} from "./files.entity";
@Entity()
export class Sources {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@OneToOne(type => SourceType)
@JoinColumn()
sourceType: SourceType;
@ManyToOne(type => Users, users => users.source)
@JoinColumn()
user: Users;
@OneToMany(type => SourcesPaths, sourcesPaths => sourcesPaths.source)
sourcePath: SourcesPaths[];
@OneToMany(type => Files, file => file.source)
file: Files[];
}
files.entity.ts
import {Entity} from 'typeorm/decorator/entity/Entity';
import { Column, JoinColumn, ManyToOne, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
import {Sources} from './sources.entity';
import {FilesTags} from './files_tags.entity';
@Entity()
export class Files {
@PrimaryGeneratedColumn()
id: number;
@Column('varchar', {length: 45})
name: string;
@Column('varchar', {length: 45, nullable: true})
family_name: string;
@Column('varchar', {length: 45, nullable: true})
created_at: string;
@ManyToOne(type => Sources, source => source.file)
@JoinColumn()
source: Sources;
@OneToMany(type => FilesTags, fileTag => fileTag.file)
fileTag: FilesTags[];
}
insertUser function
When we call insert function for the first time everything goes well. Data is inserted including data into files entity also. But on second call to insertUser() we are getting error
Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: files.id
In SQLite studio we saw that after first insert of files we loose primary key on files.id.
Are we doing something wrong or there is an issue in typeorm?
insertUser(): void {
createConnection({
type: 'sqlite',
database: '../user.sqlite',
entities: [
Users,
Sources,
SourceType,
Files
],
autoSchemaSync: true,
}).then( async connection => {
// Here you can start to work with your entities
const user = new Users();
user.name = 'Mezenga1234';
await connection.manager.save(user);
const sourcetype = new SourceType();
sourcetype.name = 'my_library';
await connection.manager.save(sourcetype);
const source = new Sources();
source.name = 'my_source';
source.sourceType = sourcetype;
source.user = user;
await connection.manager.save(source);
const files = new Files();
files.name = 'Haris.rvt';
files.source = source;
await connection.manager.save(files);
console.log('Inserted');
connection.close();
}).catch(error => console.log(error));
}
Summary: in this tutorial, you will learn how to use the SQLite NOT NULL constraint to ensure the values in a column are not NULL.
Introduction to SQLite NOT NULL constraint
When you create a table, you can specify whether a column acceptsNULL values or not. By default, all columns in a table accept NULL values except you explicitly use NOT NULL constraints.
To define a NOT NULL constraint for a column, you use the following syntax:
Code language: SQL (Structured Query Language) (sql)
CREATE TABLE table_name ( ..., column_name type_name NOT NULL, ... );
Unlike other constraints such as PRIMARY KEY and CHECK, you can only define NOT NULL constraints at the column level, not the table level.
Based on the SQL standard, PRIMARY KEY should always imply NOT NULL. However, SQLite allows NULL values in the PRIMARY KEY column except that a column is INTEGER PRIMARY KEY column or the table is a WITHOUT ROWID table or the column is defined as a NOT NULL column.
This is due to a bug in some early versions. If this bug is fixed to conform with the SQL standard, then it might break the legacy systems. Therefore, it has been decided to allow NULL values in the PRIMARY KEY column.
Once a NOT NULL constraint is attached to a column, any attempt to set the column value to NULL such as inserting or updating will cause a constraint violation.
The following example creates a new table named suppliers:
Code language: SQL (Structured Query Language) (sql)
CREATE TABLE suppliers( supplier_id INTEGER PRIMARY KEY, name TEXT NOT NULL );
In this example, the supplier_id is the PRIMARY KEY column of the suppliers table. Because this column is declared as INTEGER PRIMARY KEY, it will not accept NULL values.
The name column is also declared with a NOT NULL constraint, so it will accept only non-NULL values.
The following statement attempt to insert a NULL into the name column of the suppliers table:
Code language: SQL (Structured Query Language) (sql)
INSERT INTO suppliers(name) VALUES(NULL);
The statement fails due to the NOT NULL constraint violation. Here is the error message:
SQL Error [19]: [SQLITE_CONSTRAINT] Abort due to constraint violation (NOT NULL constraint failed: suppliers.name)Code language: CSS (css)
In this tutorial, you have learned how to use SQLite NOT NULL constraint to ensure values in a column are not NULL.
Was this tutorial helpful ?
Here we will learn SQLite Not Null constraint with examples and how to use SQLite Not Null constraint to set condition on column not to allow NULL values with examples.
SQLite Not Null Constraint
In SQLite, Not Null Constraint is used to indicates that the column will not allow storing NULL values.
Generally, in SQLite by default columns will allow NULL values in case if you have requirement like not to allow NULL values in column means then we need to add NOT NULL constraint on column.
Once we add NOT NULL Constraint on a required column and if we try to insert or update a NULL value in a new or existing row then it will throw an error because of constraint violation.
Syntax of SQLite Not Null Constraint
Following is the syntax of adding SQLite Not Null Constraint to the column.
CREATE TABLE tablename
(column1 INTEGER,
column2 TEXT NOT NULL,
column3 TEXT NOT NULL);
If you observe above SQLite Not Null Constraint syntax we are adding Not Null Constraint on multiple columns (column2, column3) and these columns will not allow null values.
Example of SQLite Not Null Constraint
Following is the example of SQLite Not Null constraint
CREATE TABLE BOOK
(Book_id INTEGER,
Book_name TEXT NOT NULL,
Publisher_name TEXT NOT NULL);
In above example we added Not Null Constraint on Book_name and Publisher_name columns and these columns will not allow null values.
Now run above query to create new table “Book” and insert some of records using following statements.
sqlite> INSERT INTO BOOK Values (1,‘SQLITE’,‘Shweta’);
sqlite> SELECT * FROM BOOK;
book_id Book_name Publisher_name
———- ———- —————
1 SQLITE Shweta
If you observe above query we inserted all the values so No problem arises at the time of insertion.
Now run following SQLite statement to insert new row in Book table.
sqlite> INSERT INTO BOOK Values (2,‘SQL Guide’,»);
sqlite> SELECT * FROM BOOK;
book_id Book_name Publisher_name
———- ———- —————
1 SQLITE Shweta
2 SQL Guide
Here also no problem arises at the time of insertion, though the value of second column is blank.
Now we will insert NULL value in Publisher_name column using following statement.
sqlite> INSERT INTO BOOK Values (1,‘SQLITE’,NULL);
Error: NOT NULL constraint failed: BOOK.Publisher_name
Here in above query we are forcefully inserting Publisher_name column value as NULL so it will violate the constraint and throw the error like as shown above statement.
This is how we can use SQLite Not Null constraint on columns to set restrictions like not to allow NULL values.