CODEWITHSUNDEEP
sqlite
mpenkov
mpenkov

Reputation: 21906

Why is the UNIQUE constraint failing on this INSERT?

I'm trying to copy tables from olddb.sqlite3 into newdb.sqlite3 using the method suggested here.

bash-3.2$ cat cp.sql 
ATTACH "olddb.sqlite3" AS old;
INSERT INTO feedback_phone SELECT * FROM old.feedback_phone;
bash-3.2$ rm newdb.sqlite3 
bash-3.2$ touch newdb.sqlite3 
bash-3.2$ sqlite3 newdb.sqlite3 < cp.sql 
Error: near line 3: UNIQUE constraint failed: feedback_phone.id_

Why is this happening? I know the id_ column consists of unique integers:

bash-3.2$ sqlite3 olddb.sqlite3 
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> .schema feedback_phone
CREATE TABLE feedback_phone (
        id_ INTEGER NOT NULL, 
        phone VARCHAR, 
        language VARCHAR, 
        kind VARCHAR, 
        user VARCHAR, 
        timestamp DATETIME, 
        success BOOLEAN, 
        url VARCHAR, 
        PRIMARY KEY (id_), 
        CHECK (success IN (0, 1))
);
sqlite> select id_ from feedback_phone;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

I'm copying that table from one database into a different database that is completely empty. What exactly is violating the constraint?

Upvotes: 1

Views: 2508

Answers (1)

CL.
CL.

Reputation: 180060

The new database file is empty, so it does not even have the table. Therefore, feedback_phone refers to the only table with that name, which is old.feedback_phone.

Add the CREATE TABLE statement to the .sql file.

Upvotes: 3

Related Questions