I am trying to create a database which allows users to create ‘to do’ lists and fill them with items to complete. However, when inserting data into the tables it gives me a UNIQUE constraint failed error and I don’t know how to solve it. This is my code for creating the database and inserting data.
CREATE TABLE user (
user_id integer NOT NULL PRIMARY KEY,
first_name varchar(15) NOT NULL,
title varchar(5) NOT NULL,
username varchar(15) NOT NULL,
password varchar(20) NOT NULL,
email varchar(50) NOT NULL,
bio text NOT NULL
);
CREATE TABLE list (
list_id integer NOT NULL PRIMARY KEY,
list_name varchar(10) NOT NULL,
user_user_id integer NOT NULL,
FOREIGN KEY (user_user_id) REFERENCES user(user_id)
);
CREATE TABLE item (
item_id integer NOT NULL PRIMARY KEY,
item text NOT NULL,
completed boolean NOT NULL,
list_list_id integer NOT NULL,
FOREIGN KEY (list_list_id) REFERENCES list(list_id)
);
-- Data:
INSERT INTO user VALUES (1, "Name1", "Title1", "Username1", "Password1", "Email1", "Bio1");
INSERT INTO user VALUES (2, "Name2", "Title2", "Username2", "Password2", "Email2", "Bio2");
INSERT INTO user VALUES (3, "Name3", "Title3", "Username3", "Password3", "Email3", "Bio3");
INSERT INTO list VALUES (1, "user1-list1", 1);
INSERT INTO list VALUES (2, "user1-list2", 1);
INSERT INTO list VALUES (3, "user1-list3", 1);
INSERT INTO list VALUES (1, "user2-list1", 2);
INSERT INTO list VALUES (1, "user3-list1", 3);
INSERT INTO list VALUES (2, "user3-list2", 3);
INSERT INTO item VALUES (1, "user1-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user1-list1-item2", "FALSE", 1);
INSERT INTO item VALUES (1, "user1-list2-item1", "FALSE", 2);
INSERT INTO item VALUES (1, "user1-list3-item1", "FALSE", 3);
INSERT INTO item VALUES (2, "user1-list3-item2", "FALSE", 3);
INSERT INTO item VALUES (1, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list3-item1", "FALSE", 2);
I have copied the errors I receive below:
Error: near line 43: UNIQUE constraint failed: list.list_id
Error: near line 44: UNIQUE constraint failed: list.list_id
Error: near line 45: UNIQUE constraint failed: list.list_id
Error: near line 49: UNIQUE constraint failed: item.item_id
Error: near line 50: UNIQUE constraint failed: item.item_id
Error: near line 51: UNIQUE constraint failed: item.item_id
Error: near line 52: UNIQUE constraint failed: item.item_id
Error: near line 53: UNIQUE constraint failed: item.item_id
Error: near line 54: UNIQUE constraint failed: item.item_id
Error: near line 55: UNIQUE constraint failed: item.item_id
Any help would be appreciated!
Не могу понять в чем ошибка
@dp.message_handler(commands=['start'])
async def start(message: types.Message):
user_id = message.from_user.id
user_name = message.from_user.first_name
if not db.user_exists(message.from_user.id):
start_command = message.text
referrer_id = str(start_command[7:])
if str(referrer_id) != '':
if str(referrer_id) != str(message.from_user.id):
db.add_user(message.from_user.id, referrer_id)
try:
await bot.send_message(referrer_id,'+2 рубля')
#Сюда нужно будет добавить счетчик для пополнения баланса
except:
pass
else:
db.add_user(message.from_user.id)
await bot.send_message(message.from_user.id, "Нельзя регестрироваться по собственной ссылке")
else:
db.add_user((message.from_user.id))
await message.answer(f'Бот в работе', reply_markup=kb)
async def profile(message: types.Message):
await message.answer(f'Хмм, Юзер, вот, что у тебя есть:nБаланс:nКупоны для розыгрыша:nДрузья: {db.count_reeferals(message.from_user.id)} n',reply_markup=in1)
@dp.callback_query_handler(text='d')
async def friend(message: types.Message):
await bot.send_message(message.from_user.id,f"Твоя ссылка:https://t.me/{BOT_NAME}?start={message.from_user.id}")
await message.answer()———-БД———-
import sqlite3
class Database:
def __init__(self, db_file):
self.connection = sqlite3.connect(db_file)
self.cursor = self.connection.cursor()
def user_exists(self, user_id):
with self.connection:
result = self.cursor.execute("SELECT * FROM 'users' WHERE 'user_id' = ?",(user_id,)).fetchall()
return bool(len(result))
def add_user(self, user_id, referrer_id=None):
with self.connection:
if referrer_id != None:
self.cursor.execute("INSERT INTO 'users' ('user_id',''referrer_id') VALUES (?,?)", (user_id,referrer_id,))
else:
return self.cursor.execute("INSERT INTO 'users' ('user_id') VALUES (?)", (user_id,))
def count_reeferals(self, user_id):
with self.connection:
return self.cursor.execute("SELECT COUNT('id') as count FROM 'users' WHERE 'referrer_id' = ?", (user_id,)).fetchone()[0]
Ошибка: sqlite3.IntegrityError: UNIQUE constraint failed: users.user_id
Ошибка как я понимаю вот в этой строчке
return self.cursor.execute("SELECT COUNT('id') as count FROM 'users' WHERE 'referrer_id' = ?", (user_id,)).fetchone()[0]До нее все работало
I am trying to create a database which allows users to create ‘to do’ lists and fill them with items to complete. However, when inserting data into the tables it gives me a UNIQUE constraint failed error and I don’t know how to solve it. This is my code for creating the database and inserting data.
CREATE TABLE user (
user_id integer NOT NULL PRIMARY KEY,
first_name varchar(15) NOT NULL,
title varchar(5) NOT NULL,
username varchar(15) NOT NULL,
password varchar(20) NOT NULL,
email varchar(50) NOT NULL,
bio text NOT NULL
);
CREATE TABLE list (
list_id integer NOT NULL PRIMARY KEY,
list_name varchar(10) NOT NULL,
user_user_id integer NOT NULL,
FOREIGN KEY (user_user_id) REFERENCES user(user_id)
);
CREATE TABLE item (
item_id integer NOT NULL PRIMARY KEY,
item text NOT NULL,
completed boolean NOT NULL,
list_list_id integer NOT NULL,
FOREIGN KEY (list_list_id) REFERENCES list(list_id)
);
-- Data:
INSERT INTO user VALUES (1, "Name1", "Title1", "Username1", "Password1", "Email1", "Bio1");
INSERT INTO user VALUES (2, "Name2", "Title2", "Username2", "Password2", "Email2", "Bio2");
INSERT INTO user VALUES (3, "Name3", "Title3", "Username3", "Password3", "Email3", "Bio3");
INSERT INTO list VALUES (1, "user1-list1", 1);
INSERT INTO list VALUES (2, "user1-list2", 1);
INSERT INTO list VALUES (3, "user1-list3", 1);
INSERT INTO list VALUES (1, "user2-list1", 2);
INSERT INTO list VALUES (1, "user3-list1", 3);
INSERT INTO list VALUES (2, "user3-list2", 3);
INSERT INTO item VALUES (1, "user1-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user1-list1-item2", "FALSE", 1);
INSERT INTO item VALUES (1, "user1-list2-item1", "FALSE", 2);
INSERT INTO item VALUES (1, "user1-list3-item1", "FALSE", 3);
INSERT INTO item VALUES (2, "user1-list3-item2", "FALSE", 3);
INSERT INTO item VALUES (1, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list3-item1", "FALSE", 2);
I have copied the errors I receive below:
Error: near line 43: UNIQUE constraint failed: list.list_id
Error: near line 44: UNIQUE constraint failed: list.list_id
Error: near line 45: UNIQUE constraint failed: list.list_id
Error: near line 49: UNIQUE constraint failed: item.item_id
Error: near line 50: UNIQUE constraint failed: item.item_id
Error: near line 51: UNIQUE constraint failed: item.item_id
Error: near line 52: UNIQUE constraint failed: item.item_id
Error: near line 53: UNIQUE constraint failed: item.item_id
Error: near line 54: UNIQUE constraint failed: item.item_id
Error: near line 55: UNIQUE constraint failed: item.item_id
Any help would be appreciated!
SQLite has a non-standard SQL extension clause called ON CONFLICT that enables us to specify how to deal with constraint conflicts.
In particular, the clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints.
This article provides examples of how this clause can be used to determine how to handle primary key constraint conflicts.
By “primary key constraint conflicts”, I mean when you try to insert a duplicate value into a primary key column. By default, when you try to do this, the operation will be aborted and SQLite will return an error.
But you can use the ON CONFLICT clause to change the way SQLite deals with these situations.
One option is to use this clause in the CREATE TABLE statement when creating the table. Doing that will determine how all INSERT operations are treated.
Another option is to use the clause on the INSERT statement whenever you try to insert data into the table. This allows you to take advantage of the clause even when the table wasn’t created with it. When you use this option, the syntax is different; you use OR instead of ON CONFLICT.
The examples on this page use the second option – I create the table without the ON CONFLICT clause, and I instead specify OR on the INSERT statement.
Sample Table
Let’s create a simple table and add one row.
CREATE TABLE Products(
ProductId INTEGER PRIMARY KEY,
ProductName,
Price
);
INSERT INTO Products VALUES (1, 'Hammer', 8.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0
We currently have one row, with a ProductId of 1.
Now we can run through the various scenarios of inserting data into that table that violates the primary key constraint.
Example 1 – Abort (Default Behaviour)
As mentioned, the default behaviour for SQLite is to abort the INSERT operation and return an error.
INSERT INTO Products VALUES (1, 'Wrench', 12.50);Result:
Error: UNIQUE constraint failed: Products.ProductId
An error was returned and nothing was inserted.
This is the equivalent of using the OR ABORT option.
INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 12.50);Result:
Error: UNIQUE constraint failed: Products.ProductId
We can verify that nothing was inserted by running a SELECT statement against the table.
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0
We can see that the table only contains the original row.
Example 2 – Ignore
One alternative is to have SQLite ignore the offending row. In other words, it will skip over the row and continue processing subsequent rows.
To do this within your INSERT statement, use OR IGNORE.
The effect of this is that the INSERT operation succeeds, but without any rows that violate the primary key constraint.
INSERT OR IGNORE INTO Products VALUES
(1, 'Hammer', 12.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0
In this case I tried to insert two new rows with an ID that already existed in the table, so both of those rows were skipped.
Example 3 – Replace
Another option you have is to replace the original row with the new row.
In other words, you will overwrite the existing data with your new data.
To do this, use OR REPLACE.
INSERT OR REPLACE INTO Products VALUES
(1, 'Hammer', 12.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Wrench 22.5 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0
In this case most rows were the same, so they contain the same data after the INSERT operation. However, we can see that the first row has been updated to use the values in my INSERT statement.
We can also see that it used the second set of values (seeing as two shared the same ProductId).
So the effect is kind of like an UPDATE statement and INSERT statement combined.
Example 4 – Rollback
Another option is to use the ROLLBACK option.
This aborts the current SQL statement with an SQLITE_CONSTRAINT error and rolls back the current transaction. If no transaction is active (other than the implied transaction that is created on every command) then it works the same as the ABORT algorithm.
It pays to be mindful of how this option works. Here’s an example that uses multiple INSERT OR ROLLBACK statements within a transaction.
DELETE FROM Products;
BEGIN TRANSACTION;
INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
COMMIT;
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> BEGIN TRANSACTION; sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId sqlite> INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00); sqlite> COMMIT; Error: cannot commit - no transaction is active sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 5 Chisel 23.0 6 Bandage 120.0 sqlite>
Basically what here happened is that, it has gotten as far as the constraint violation, then rolled back the transaction. Then the next two lines were processed and then the COMMIT keyword was encountered. By then, the transaction had already been rolled back and so we got another error telling us that no transaction was active.
Here’s what happens if I remove it from the transaction.
DELETE FROM Products;
INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> DELETE FROM Products; sqlite> sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId sqlite> INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00); sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0 sqlite>
In this case, it worked like ABORT.
To demonstrate this, here’s the same statement using ABORT instead of ROLLBACK.
DELETE FROM Products;
INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ABORT INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ABORT INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00);
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> DELETE FROM Products; sqlite> sqlite> INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 8.00); sqlite> INSERT OR ABORT INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ABORT INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId sqlite> INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00); sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0 sqlite>
The Fail Option
The FAIL option aborts the current SQL statement with an SQLITE_CONSTRAINT error. But this option does not back out prior changes of the SQL statement that failed nor does it end the transaction.
DELETE FROM Products;
INSERT OR FAIL INTO Products VALUES
(1, 'Hammer', 8.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5
I am trying to insert values in table. But there is only one value inserted. I am getting an error in log cat when I am trying to insert new values.
Log cat shows :
abort at 13 in [INSERT INTO event(totalminutesfrom,dayofweek,title,location,totalminutesto,id) VALUES (?,?,?,?,?,?)]: UNIQUE constraint failed: event.id
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: Error inserting totalminutesfrom=694 dayofweek=null title=qxs location=Eded & Mariz totalminutesto=0 id=0
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: event.id (code 1555)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at com.example.siddhi.timetablelayout.EventTableHelper.addEvent(EventTableHelper.java:76)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at com.example.siddhi.timetablelayout.AddEventActivity$5.onClick(AddEventActivity.java:217)
Its showing error on these two lines while inserting row.
db.insert(TABLE, null, values);
db.addEvent(new EventData(eventTitle,dayOfWeek,totalMinutesFrom, totalMinutesTo,location));
EventTableHelper
public class EventTableHelper extends SQLiteOpenHelper {
private static final String TABLE = "event";
private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_LOCATION = "location";
private static final String KEY_DAY_OF_WEEK = "dayofweek";
private static final String KEY_TOTAL_MINUTES_FROM = "totalminutesfrom";
private static final String KEY_TOTAL_MINUTES_TO = "totalminutesto";
public EventTableHelper(Context context) {
super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);
//3rd argument to be passed is CursorFactory instance
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
//createTable(db);
}
public void createTable(SQLiteDatabase db){
String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_TITLE + " TEXT,"
+ KEY_DAY_OF_WEEK +"TEXT" + KEY_TOTAL_MINUTES_FROM +"INTEGER"
+ KEY_TOTAL_MINUTES_TO + "INTEGER" + KEY_LOCATION + "TEXT" + ")";
db.execSQL(CREATE_EVENTS_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
// db.execSQL("DROP TABLE IF EXISTS " + TABLE);
// createTable(db);
// Create tables again
//onCreate(db);
}
// code to add the new contact
public void addEvent(EventData event) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_ID, event.getId());
values.put(KEY_TITLE,event.getTitle()); // Contact Name
values.put(KEY_DAY_OF_WEEK,event.getDayofWeek());
values.put(KEY_TOTAL_MINUTES_FROM,event.getFromMinutes());
values.put(KEY_TOTAL_MINUTES_TO,event.getToMinutes());
values.put(KEY_LOCATION,event.getLocation());
// Inserting Row
db.insert(TABLE, null, values);
//2nd argument is String containing nullColumnHack
db.close(); // Closing database connection
}
// code to get the single contact
EventData getEvent(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE, new String[] { KEY_ID,
KEY_TITLE, KEY_DAY_OF_WEEK, KEY_TOTAL_MINUTES_FROM,KEY_TOTAL_MINUTES_TO,KEY_LOCATION }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
EventData eventData = new EventData(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2),
cursor.getInt(3),cursor.getInt(4),cursor.getString(5));
return eventData;
}
// code to get all contacts in a list view
public List<EventData> getAllEvents() {
List<EventData> conList = new ArrayList<EventData>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
EventData event = new EventData();
event.setId(Integer.parseInt(cursor.getString(0)));
event.setTitle(cursor.getString(1));
event.setDayofWeek(cursor.getString(2));
event.setFromMinutes(cursor.getInt(3));
event.setToMinutes(cursor.getInt(4));
event.setLocation(cursor.getString(5));
// Adding contact to list
conList.add(event);
} while (cursor.moveToNext());
}
// return contact list
return conList;
}
}
How to solve this??
I am trying to insert values in table. But there is only one value inserted. I am getting an error in log cat when I am trying to insert new values.
Log cat shows :
abort at 13 in [INSERT INTO event(totalminutesfrom,dayofweek,title,location,totalminutesto,id) VALUES (?,?,?,?,?,?)]: UNIQUE constraint failed: event.id
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: Error inserting totalminutesfrom=694 dayofweek=null title=qxs location=Eded & Mariz totalminutesto=0 id=0
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: event.id (code 1555)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at com.example.siddhi.timetablelayout.EventTableHelper.addEvent(EventTableHelper.java:76)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: at com.example.siddhi.timetablelayout.AddEventActivity$5.onClick(AddEventActivity.java:217)
Its showing error on these two lines while inserting row.
db.insert(TABLE, null, values);
db.addEvent(new EventData(eventTitle,dayOfWeek,totalMinutesFrom, totalMinutesTo,location));
EventTableHelper
public class EventTableHelper extends SQLiteOpenHelper {
private static final String TABLE = "event";
private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_LOCATION = "location";
private static final String KEY_DAY_OF_WEEK = "dayofweek";
private static final String KEY_TOTAL_MINUTES_FROM = "totalminutesfrom";
private static final String KEY_TOTAL_MINUTES_TO = "totalminutesto";
public EventTableHelper(Context context) {
super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);
//3rd argument to be passed is CursorFactory instance
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
//createTable(db);
}
public void createTable(SQLiteDatabase db){
String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_TITLE + " TEXT,"
+ KEY_DAY_OF_WEEK +"TEXT" + KEY_TOTAL_MINUTES_FROM +"INTEGER"
+ KEY_TOTAL_MINUTES_TO + "INTEGER" + KEY_LOCATION + "TEXT" + ")";
db.execSQL(CREATE_EVENTS_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
// db.execSQL("DROP TABLE IF EXISTS " + TABLE);
// createTable(db);
// Create tables again
//onCreate(db);
}
// code to add the new contact
public void addEvent(EventData event) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_ID, event.getId());
values.put(KEY_TITLE,event.getTitle()); // Contact Name
values.put(KEY_DAY_OF_WEEK,event.getDayofWeek());
values.put(KEY_TOTAL_MINUTES_FROM,event.getFromMinutes());
values.put(KEY_TOTAL_MINUTES_TO,event.getToMinutes());
values.put(KEY_LOCATION,event.getLocation());
// Inserting Row
db.insert(TABLE, null, values);
//2nd argument is String containing nullColumnHack
db.close(); // Closing database connection
}
// code to get the single contact
EventData getEvent(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE, new String[] { KEY_ID,
KEY_TITLE, KEY_DAY_OF_WEEK, KEY_TOTAL_MINUTES_FROM,KEY_TOTAL_MINUTES_TO,KEY_LOCATION }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
EventData eventData = new EventData(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2),
cursor.getInt(3),cursor.getInt(4),cursor.getString(5));
return eventData;
}
// code to get all contacts in a list view
public List<EventData> getAllEvents() {
List<EventData> conList = new ArrayList<EventData>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
EventData event = new EventData();
event.setId(Integer.parseInt(cursor.getString(0)));
event.setTitle(cursor.getString(1));
event.setDayofWeek(cursor.getString(2));
event.setFromMinutes(cursor.getInt(3));
event.setToMinutes(cursor.getInt(4));
event.setLocation(cursor.getString(5));
// Adding contact to list
conList.add(event);
} while (cursor.moveToNext());
}
// return contact list
return conList;
}
}
How to solve this??
Я пытаюсь создать базу данных, которая позволяет пользователям создавать списки дел и заполнять их элементами для завершения. Однако при вставке данных в таблицы это дает мне ошибку UNIQUE constraint failed, и я не знаю, как ее решить. Это мой код для создания базы данных и вставки данных.
CREATE TABLE user (
user_id integer NOT NULL PRIMARY KEY,
first_name varchar(15) NOT NULL,
title varchar(5) NOT NULL,
username varchar(15) NOT NULL,
password varchar(20) NOT NULL,
email varchar(50) NOT NULL,
bio text NOT NULL
);
CREATE TABLE list (
list_id integer NOT NULL PRIMARY KEY,
list_name varchar(10) NOT NULL,
user_user_id integer NOT NULL,
FOREIGN KEY (user_user_id) REFERENCES user(user_id)
);
CREATE TABLE item (
item_id integer NOT NULL PRIMARY KEY,
item text NOT NULL,
completed boolean NOT NULL,
list_list_id integer NOT NULL,
FOREIGN KEY (list_list_id) REFERENCES list(list_id)
);
-- Data:
INSERT INTO user VALUES (1, "Name1", "Title1", "Username1", "Password1", "Email1", "Bio1");
INSERT INTO user VALUES (2, "Name2", "Title2", "Username2", "Password2", "Email2", "Bio2");
INSERT INTO user VALUES (3, "Name3", "Title3", "Username3", "Password3", "Email3", "Bio3");
INSERT INTO list VALUES (1, "user1-list1", 1);
INSERT INTO list VALUES (2, "user1-list2", 1);
INSERT INTO list VALUES (3, "user1-list3", 1);
INSERT INTO list VALUES (1, "user2-list1", 2);
INSERT INTO list VALUES (1, "user3-list1", 3);
INSERT INTO list VALUES (2, "user3-list2", 3);
INSERT INTO item VALUES (1, "user1-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user1-list1-item2", "FALSE", 1);
INSERT INTO item VALUES (1, "user1-list2-item1", "FALSE", 2);
INSERT INTO item VALUES (1, "user1-list3-item1", "FALSE", 3);
INSERT INTO item VALUES (2, "user1-list3-item2", "FALSE", 3);
INSERT INTO item VALUES (1, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (2, "user2-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list1-item1", "FALSE", 1);
INSERT INTO item VALUES (1, "user3-list3-item1", "FALSE", 2);
Я скопировал ошибки, которые получаю ниже:
Error: near line 43: UNIQUE constraint failed: list.list_id
Error: near line 44: UNIQUE constraint failed: list.list_id
Error: near line 45: UNIQUE constraint failed: list.list_id
Error: near line 49: UNIQUE constraint failed: item.item_id
Error: near line 50: UNIQUE constraint failed: item.item_id
Error: near line 51: UNIQUE constraint failed: item.item_id
Error: near line 52: UNIQUE constraint failed: item.item_id
Error: near line 53: UNIQUE constraint failed: item.item_id
Error: near line 54: UNIQUE constraint failed: item.item_id
Error: near line 55: UNIQUE constraint failed: item.item_id
Любая помощь была бы признательна!
Вы получаете ошибку UNIQUE constraint failed, когда данные, которые вы вставляете, содержат запись, которая уже находится в соответствующем столбце таблицы, в которую вы вставляете.
Если вы хотите, чтобы SQL ИГНОРИРОВАЛ эту ошибку и продолжал добавлять другие записи , сделайте следующее:
INSERT or IGNORE into tablename VALUES (value1,value2 , so on );
Если вы хотите заменить значения в таблице , когда запись уже существует, сделайте следующее:
INSERT or REPLACE into tablename VALUES (value1,value2 , so on );
Это экономит много усилий с вашей стороны и весьма полезно.
44
Federico Pellegatta
25 Июл 2017 в 11:11
SQLite has a non-standard SQL extension clause called ON CONFLICT that enables us to specify how to deal with constraint conflicts.
In particular, the clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints.
This article provides examples of how this clause can be used to determine how to handle primary key constraint conflicts.
By “primary key constraint conflicts”, I mean when you try to insert a duplicate value into a primary key column. By default, when you try to do this, the operation will be aborted and SQLite will return an error.
But you can use the ON CONFLICT clause to change the way SQLite deals with these situations.
One option is to use this clause in the CREATE TABLE statement when creating the table. Doing that will determine how all INSERT operations are treated.
Another option is to use the clause on the INSERT statement whenever you try to insert data into the table. This allows you to take advantage of the clause even when the table wasn’t created with it. When you use this option, the syntax is different; you use OR instead of ON CONFLICT.
The examples on this page use the second option – I create the table without the ON CONFLICT clause, and I instead specify OR on the INSERT statement.
Sample Table
Let’s create a simple table and add one row.
CREATE TABLE Products(
ProductId INTEGER PRIMARY KEY,
ProductName,
Price
);
INSERT INTO Products VALUES (1, 'Hammer', 8.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0
We currently have one row, with a ProductId of 1.
Now we can run through the various scenarios of inserting data into that table that violates the primary key constraint.
Example 1 – Abort (Default Behaviour)
As mentioned, the default behaviour for SQLite is to abort the INSERT operation and return an error.
INSERT INTO Products VALUES (1, 'Wrench', 12.50);Result:
Error: UNIQUE constraint failed: Products.ProductId
An error was returned and nothing was inserted.
This is the equivalent of using the OR ABORT option.
INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 12.50);Result:
Error: UNIQUE constraint failed: Products.ProductId
We can verify that nothing was inserted by running a SELECT statement against the table.
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0
We can see that the table only contains the original row.
Example 2 – Ignore
One alternative is to have SQLite ignore the offending row. In other words, it will skip over the row and continue processing subsequent rows.
To do this within your INSERT statement, use OR IGNORE.
The effect of this is that the INSERT operation succeeds, but without any rows that violate the primary key constraint.
INSERT OR IGNORE INTO Products VALUES
(1, 'Hammer', 12.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0
In this case I tried to insert two new rows with an ID that already existed in the table, so both of those rows were skipped.
Example 3 – Replace
Another option you have is to replace the original row with the new row.
In other words, you will overwrite the existing data with your new data.
To do this, use OR REPLACE.
INSERT OR REPLACE INTO Products VALUES
(1, 'Hammer', 12.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Wrench 22.5 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0
In this case most rows were the same, so they contain the same data after the INSERT operation. However, we can see that the first row has been updated to use the values in my INSERT statement.
We can also see that it used the second set of values (seeing as two shared the same ProductId).
So the effect is kind of like an UPDATE statement and INSERT statement combined.
Example 4 – Rollback
Another option is to use the ROLLBACK option.
This aborts the current SQL statement with an SQLITE_CONSTRAINT error and rolls back the current transaction. If no transaction is active (other than the implied transaction that is created on every command) then it works the same as the ABORT algorithm.
It pays to be mindful of how this option works. Here’s an example that uses multiple INSERT OR ROLLBACK statements within a transaction.
DELETE FROM Products;
BEGIN TRANSACTION;
INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
COMMIT;
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> BEGIN TRANSACTION; sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId sqlite> INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00); sqlite> COMMIT; Error: cannot commit - no transaction is active sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 5 Chisel 23.0 6 Bandage 120.0 sqlite>
Basically what here happened is that, it has gotten as far as the constraint violation, then rolled back the transaction. Then the next two lines were processed and then the COMMIT keyword was encountered. By then, the transaction had already been rolled back and so we got another error telling us that no transaction was active.
Here’s what happens if I remove it from the transaction.
DELETE FROM Products;
INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00);
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> DELETE FROM Products; sqlite> sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Hammer', 8.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ROLLBACK INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId sqlite> INSERT OR ROLLBACK INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ROLLBACK INTO Products VALUES (6, 'Bandage', 120.00); sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0 sqlite>
In this case, it worked like ABORT.
To demonstrate this, here’s the same statement using ABORT instead of ROLLBACK.
DELETE FROM Products;
INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 8.00);
INSERT OR ABORT INTO Products VALUES (2, 'Nails', 2.50);
INSERT OR ABORT INTO Products VALUES (3, 'Saw', 10.50);
INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 22.50);
INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00);
INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00);
SELECT * FROM Products;Here’s the full output from my terminal when I run this:
sqlite> DELETE FROM Products; sqlite> sqlite> INSERT OR ABORT INTO Products VALUES (1, 'Hammer', 8.00); sqlite> INSERT OR ABORT INTO Products VALUES (2, 'Nails', 2.50); sqlite> INSERT OR ABORT INTO Products VALUES (3, 'Saw', 10.50); sqlite> INSERT OR ABORT INTO Products VALUES (1, 'Wrench', 22.50); Error: UNIQUE constraint failed: Products.ProductId sqlite> INSERT OR ABORT INTO Products VALUES (5, 'Chisel', 23.00); sqlite> INSERT OR ABORT INTO Products VALUES (6, 'Bandage', 120.00); sqlite> sqlite> SELECT * FROM Products; ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5 5 Chisel 23.0 6 Bandage 120.0 sqlite>
The Fail Option
The FAIL option aborts the current SQL statement with an SQLITE_CONSTRAINT error. But this option does not back out prior changes of the SQL statement that failed nor does it end the transaction.
DELETE FROM Products;
INSERT OR FAIL INTO Products VALUES
(1, 'Hammer', 8.00),
(2, 'Nails', 2.50),
(3, 'Saw', 10.50),
(1, 'Wrench', 22.50),
(5, 'Chisel', 23.00),
(6, 'Bandage', 120.00);
SELECT * FROM Products;Result:
ProductId ProductName Price ---------- ----------- ---------- 1 Hammer 8.0 2 Nails 2.5 3 Saw 10.5
I am learning entity framework core, using it with SQLITE
I have 2 tables in the database.
Messages:
CREATE TABLE `Messages` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`IsDeleted` INTEGER NOT NULL,
`FromUserId` INTEGER,
`ToUserId` INTEGER,
`SendDate` TEXT NOT NULL,
`ReadDate` TEXT NOT NULL,
`MessageContent` TEXT,
CONSTRAINT `FK_Messages_Users_ToUserId` FOREIGN KEY(`ToUserId`) REFERENCES `Users`(`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_Messages_Users_FromUserId` FOREIGN KEY(`FromUserId`) REFERENCES `Users`(`Id`) ON DELETE RESTRICT
);
and Users table:
CREATE TABLE `Users` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`IsDeleted` INTEGER NOT NULL,
`Name` TEXT,
`DisplayName` TEXT,
`Gender` INTEGER NOT NULL,
`BirthDate` TEXT NOT NULL
);
my c# classes look like:
public class User
{
public int Id {get;set;}
public bool IsDeleted{get;set;}
[Required]
public string Name{get;set;}
public string DisplayName{get;set;}
[Required]
public char Gender{get;set;}
[Required]
public DateTime BirthDate{get;set;}
public User(string n, string dn, char g, DateTime bd)
{
Name=n; DisplayName = dn; Gender = g; BirthDate = bd;
}
protected User(){}
public override string ToString()
{
return string.Format("{0} ({1}) {2}", this.Name, this.Gender,this.BirthDate.ToShortDateString());
}
}
and Messages class:
public class Message
{
public int Id{get;set;}
public bool IsDeleted{get;set;}
[Required]
public Users.User FromUser{get;set;}
[Required]
public Users.User ToUser{get;set;}
[Required]
public DateTime SendDate{get;set;}
public DateTime? ReadDate{get;set;}
[Required]
public string MessageContent{get;set;}
protected Message(){}
public Message(User from, User to, string content)
{
this.FromUser = from;
this.ToUser = to;
this.MessageContent = content;
this.SendDate = DateTime.Now;
this.ReadDate = DateTime.Now;
}
}
users table works, but when I try to add a new entity to the messages by this:
public override int Insert(Message entity)
{
dbc.Messages.Add(entity);
dbc.SaveChanges();
return entity.Id;
}
I get the following error:
SQLite Error 19: ‘UNIQUE constraint failed
I have no idea what is wrong. When I manually inserted data to a database (using DB browser for sqlite) it works.
Are the relationships in the classes ok?
My dbContextSnapshot is:
[DbContext(typeof(RandevouDbContext))]
partial class RandevouDbContextModelSnapshot : ModelSnapshot
{
protected override void BuildModel(ModelBuilder modelBuilder)
{
#pragma warning disable 612, 618
modelBuilder.HasAnnotation("ProductVersion", "2.1.4-rtm-31024");
modelBuilder.Entity("RandevouData.Messages.Message", b =>
{
b.Property<int>("Id")
.ValueGeneratedOnAdd();
b.Property<int>("FromUserId");
b.Property<bool>("IsDeleted");
b.Property<string>("MessageContent")
.IsRequired();
b.Property<DateTime?>("ReadDate");
b.Property<DateTime>("SendDate");
b.Property<int>("ToUserId");
b.HasKey("Id");
b.HasIndex("FromUserId");
b.HasIndex("ToUserId");
b.ToTable("Messages");
});
modelBuilder.Entity("RandevouData.Users.User", b =>
{
b.Property<int>("Id")
.ValueGeneratedOnAdd();
b.Property<DateTime>("BirthDate");
b.Property<string>("DisplayName");
b.Property<char>("Gender");
b.Property<bool>("IsDeleted");
b.Property<string>("Name")
.IsRequired();
b.HasKey("Id");
b.ToTable("Users");
});
modelBuilder.Entity("RandevouData.Messages.Message", b =>
{
b.HasOne("RandevouData.Users.User", "FromUser")
.WithMany()
.HasForeignKey("FromUserId")
.OnDelete(DeleteBehavior.Cascade);
b.HasOne("RandevouData.Users.User", "ToUser")
.WithMany()
.HasForeignKey("ToUserId")
.OnDelete(DeleteBehavior.Cascade);
});
#pragma warning restore 612, 618
}
By the way. I cannot configure mapping from two sides.
The Message entity has UserFrom and UserTo fields,
but the User entity can not have Messages, because one time he is «userFrom» and other time he can be «userTo».
And there is also a code, where i create Message entity
public int SendMessage(int senderId, int receiverId, string content)
{
var dao = new MessagesDao();
var usersDao = new UsersDao();
var userService = new UserService(mapper);
var sender = usersDao.Get(senderId);
var receiver = usersDao.Get(receiverId);
var entity = new Message(sender,receiver,content);
var id = dao.Insert(entity);
return id;
}
user dao where i override GET method
public override User Get(int id)
{
using (var context = new RandevouDbContext())
{
var user = dbc.Find<User>(id);
return user;
}
}
and MessagesDao where i override Add method
public override int Insert(Message entity)
{
using (var dc = new RandevouDbContext())
{
dc.Messages.Add(entity);
dc.SaveChanges();
return entity.Id;
}
}
btw, i dont know if it is ok, but i have 0 entries to update (!?)
