SQLite表约束的唯一性和ON CONFLICT REPLACE的用法 [英] SQLite table constraint unique and ON CONFLICT REPLACE usage

问题描述

请先阅读，然后将其复制.

我正在尝试在SQLite表中插入经度和纬度值，我希望它是唯一的值，对此我进行了一些研究后发现 this 答案非常有帮助，尽管它们都是针对简单查询编写的，但我无法查找如何同时满足 unique 和 ON_CONFLICTION_REPLACE

I am trying to insert longitude and latitude values in SQLite table and i want it to be unique value, after some research for that i have found this and this answers very helpful, although they both are written for simple queries and i am not able to find how to fit my query with both constraints unique and ON_CONFLICTION_REPLACE

查询如下，

 private static final String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_LOCATIONS + "("
                + UID + " TEXT PRIMARY KEY," + ADDRESS + " TEXT,"
                + LONGITUDE + " TEXT unique," + LATITUDE + " TEXT unique" + ")";

但是这个唯一性没有任何区别，在我的情况下使用ON_CONFLICTION_REPLACE是强制性的吗?

but this unique doesn't made any differences, is it compulsion to use ON_CONFLICTION_REPLACE in my case?

来自 Ayman 的

From Ayman's answer i got that i can apply unique on multiple columns together but i still getting duplicate values after insertion. can anyone make appropriate query from my query and help me understand all things? Thanks

推荐答案

将表结构更改为此

private static final String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_LOCATIONS + "("
            + UID + " TEXT PRIMARY KEY," + ADDRESS + " TEXT,"
            + LONGITUDE + " TEXT," + LATITUDE + " TEXT,
            UNIQUE(" + LOGITUDE + "," + LATITUDE + ") ON CONFLICT REPLACE)";

然后，当您执行插入操作时，请使用以下方法

Then when you do the insert use the below method

ContentValues insertValues = new ContentValues();
insertValues.put(LATITUDE, latitude);
insertValues.put(LOGITUDE, longitude);
db.insertWithOnConflict(TABLE_LOCATIONS, null, insertValues, SQLiteDatabase.CONFLICT_REPLACE);

这篇关于SQLite表约束的唯一性和ON CONFLICT REPLACE的用法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！