Constraint failed android sqlite

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed:

Inserting

No problem in inserting but while updating im getting this error

Updating

Anyone know why this is so? .

2 Answers 2

You are likely inserting using a value for the id column (KEY_ID). Odds on this column is defined as either INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT.

Typically you do not specify a value for such a column as SQLite will assign a unique value (typically 1 greater than the highest assigned).

If you specify a value for such a column and a row in the table already has that value in the column then the UNIQUE constraint implied by PRIMARY KEY will be violated as it’s against the rule that a PRIMARY KEY has to be unique.

I’d suggest that you want to use :-

returnVariable will then be the value of the id column (KEY_ID) that was assigned when the row was inserted.

• Note id column does not necessarily mean that the column is named id but that it’s a special column due to how it is defined. That is, by specifying INTEGER PRIMARY KEY, the column is actually an alias of the normally hidden rowid column (unless the TABLE is defined with the WITHOUT ROWID phrase).

You may find reading SQLite Autoincrement helpful.

Источник

SQLite Foreign Key Constraint Failed (code 787)

I ran into the Foreign Key Constraint Failed (code 787) error when I tried to upgrade my database. The only change I did was try to add a 4th entry to my InsertStatus . I looked around and I read that using ON DELETE CASCADE should solve my problem so I tried placing it at all my FK references and tried again but still the same problem.

Logcat points to my onUpgrade and all the DROP TABLES in it ( i tried removing it one at a time to see which ones were bad and apparently all of them were ).

Am I using ON DELETE CASCADE wrong? Or is it something else in my code?

InsertStatus

DatabaseHelper

onUpgrade

6 Answers 6

The error here is related to creating a child entity before their parent is in existence.

The Flow should be:

1. Create Parent and get parent ID.
2. Create Child entity containing a reference to parent ID

So If you create a child entity without first having a valid parent ID you will be thrown this fatal error.

According to the below link you insert the value which failed the constraints of foreign key means you added a value of foregin key which not exists in parent table

In my situation problem was that I used

Make sure that you first insert the object which is in the parent table. I was doing the same mistake while making some tests (not adding the corresponding parent, so that PK from the parent table was non existing.)

In my case I forgot to fill a parent table with data. When added an item to child table, got this error. When removed foreignKeys lines in Entity-file:

insertion became possible. (But if you do that without clearing app data, you will get an exception java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you’ve changed schema but forgot to update the version number. You can simply fix this by increasing the version number. )

So, simply add necessary data to parent tables.

UPDATE

I got this exception again.

In order to test which foreign key is conficting, comment several of them and recompile the application. Also delete installed one or wipe it’s data. After starting the app execute requests as usual. If no exception happened, uncomment one foreign key. Then delete the app again, recompile, add data to the table. Do it until you know which foreign key is violated.

In my case I inserted 0 instead of null into a column, so that a parent table didn’t contain 0 .

Источник

android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed

When I want to add data to sqlite this error pops up. Previously in SQLite there was already one data. Can you help provide a solution to my problem? android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed

2 Answers 2

why you are insertion id while you set id auto increment
remove args.put(ID_MOVIE, movieFavorite.getId()); from insertiong query like below

You probably don’t want to have this line :-

in the insertMovie method.

Without the line, the id will be automatically generated (1 then 2 then 3 (likely)).

If you do include the above line then an attempt will be made to insert BUT if the value is already used in that column, in other rows, then you will get the UNIQUE constraint conflict. That is, primary keys have to be UNIQUE and thus even without coding UNIQUE they are as if UNIQUE was coded (i.e the UNIQUE constraint is implied).

Note; you very likely do not need AUTOINCREMENT, + » (%s INTEGER PRIMARY KEY,» + will still auto-generate the id, but will be more efficient. As per :-

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

As per the comment :-

If the args.put(ID_MOVIE, movieFavorite.getId()); is not added then the data will be duplicated

here’s an example :-

DbHelper.java

MovieFav.java

Code in Activity

Result in Log :-

As the above always attempts to add 4 rows, if run a second time then :-

i.e. the same rows have been added BUT with other id’s (5-8 this time)

No Duplicate Movies

If what you want is to not duplicate data, like the entire movie so that 5-8 will not be added then you can create a UNIQUE constraint appropriate to the data that you don’t want to be duplicated. The id will never be duplicated it cannot be and isn’t useful as far as the data is concerned, from the aspect of Movie information (the id is very useful from a database point of view).

Assuming that you never want a Movie to be added if there is already a Movie with the same Title and the same Release date then you could make the combination of Movie’s Title and Movie’s Release date UNIQUE.

e.g. for the above then your code for the creation of the table could be (see comments) :-

The Error (isn’t as such)

Regarding the message, it is a trapped exception, it doesn’t result in a failure and the reported row(s) are not added.

If you wanted to bypasses the reporting in the log then you would have to use an alternative method that entails generating the insert SQL that uses INSERT OR IGNORE INTO the_table (a_comma_separated_list_of_the_columns_for_which_data_is_supplied) VALUES(a_comma_separated_list_of_values) (the values that make up a sentence describes the values as a whole, it is not a special value that would be used).

In short the insert method could be :-

NOTE the line Log.d(«ALTINSERTSQL»,sqlstmnt.toString()); isn’t needed, it was added to show what the SQL generated, as is passed to SQLite e.g.

INSERT OR IGNORE INTO movie(title,overview,release_date,vote_average,photo) VALUES (. )

The ?’s are bound by the SQLite API (in a similar what to using String.format) and hence the use of the bindAllArgsAsStrings to allow SQlite to replace each ? with the appropriate value. This prevents SQLInjection.

Consider the above and using :-

This tries to add the same set of movies BUT the second lot won;t because of the UNIQUE constrain added for the Movie Title Moview Release date combination BUT the log doesn’t include the exception messages (there are no exceptions because INSERT OR IGNORE acts as a noop (do nothing) if the conflict occurs).

Источник

How to avoid room foreign key error — constraint failed (code 787)

I have 3 entities — 1 child and 2 parent. 2 parent entity may have many child entities, everyone has their own.

And this is parents:

Pool and Note can have several Share, which do not intersect, each of them has its own and unique.

But when i try to save Share i have next error:

How to avoid this error?

2 Answers 2

It seems that you are trying to put two foreign-key constraints on the same column (entityId). Bizarrely, SQLite will allow you create a table with this setup. However, when you add a new row it will check its foreign key constraints to verify that the value exists in the other table(s). So in order for this to succeed, you need to have the entityId in both tables:

If I create a new share with entityId = 1 this will succeed because I have a pool with and a note with >

But if I try to create a share with entityId = 2, foreign constraint validation will fail because there is no note with >

You need to rethink the structure of your tables so that you don’t have multiple foreign keys on the same column, possibly with a linking table.

You can test this in SQLite:

One field cannot reference two foreign keys. In your setup, you are declaring that «entity_id» is foreign key of type Pool, which parent column is Pool.id AND «entity_id» is foreign key of type Note, which parent column is Note.id. This is an invalid constraint.

You will need to add a new column in the Share table that will reference the Note table as a foreign key. Add new field, i.e. «note_id» of type String and annotate it as a foreign key to the Note class. Something like this:

I am not sure about the structure of your database, but I don’t know the idea behind the app and I cannot comment about the structure. I can give you one tip, though: if possible, use integers instead of Strings for primary keys — it makes database operations a lot faster.

Источник

android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787) #73

Comments

dylan-colaco commented Aug 12, 2018

Using this version of work manager: android.arch.work:work-runtime:1.0.0-alpha06
I see this causing crashes in crashlytics logs quite frequently:

Fatal Exception: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)
#################################################################
Error Code : 787 (SQLITE_CONSTRAINT_FOEIGNKEY)
Caused By : Abort due to constraint violation.
(FOREIGN KEY constraint failed (code 787))
#################################################################
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(SQLiteConnection.java)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:865)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
at android.arch.persistence.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.java:50)
at android.arch.persistence.room.EntityInsertionAdapter.bind(EntityInsertionAdapter.java:64)
at androidx.work.impl.model.SystemIdInfoDao_Impl.insertSystemIdInfo(SystemIdInfoDao_Impl.java:52)
at androidx.work.impl.background.systemjob.SystemJobScheduler.schedule(SystemJobScheduler.java:92)
at androidx.work.impl.Schedulers.schedule(Schedulers.java:99)
at androidx.work.impl.WorkerWrapper.run(WorkerWrapper.java:113)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1133)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:607)
at java.lang.Thread.run(Thread.java:762)

The text was updated successfully, but these errors were encountered:

tikurahul commented Aug 15, 2018

This should be fixed in alpha07 .

jesualex commented Aug 29, 2018 •

I have the same problem, i’m testing alpha07 and it seems works fine but the Works what are inserted to queue without connection, never are fired after the device are connected again. instead 1.0.0-alpha08 seems solve both, thanks @tikurahul

pfmaggi commented Sep 18, 2018

Thanks for reporting this.
I’m closing this issue because this forum is for issues with the codelab.

If you think that this is a problem of the WorkManager library, please open a bug on the WorkManager public issue tracker: https://issuetracker.google.com/issues/new?component=409906&template=1094197

Please reopen if you still think this is an issue with the codelab.

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник