sqlite - 奇怪的 org.sqlite.SQLiteException: [SQLITE_ERROR] SQL 错误或缺少数据库(外键不匹配 -
问题描述
我正在运行into org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (foreign key mismatch - ...
一个声明,使用普通的 SQLite 前端没有任何抱怨。这将创建我的数据库的关键部分:
CREATE TABLE IF NOT EXISTS artists (
aid INTEGER PRIMARY KEY AUTOINCREMENT,
aname VARCHAR(200) NOT NULL,
CONSTRAINT one UNIQUE(aname)
);
CREATE TABLE IF NOT EXISTS discs (
did INTEGER PRIMARY KEY AUTOINCREMENT,
testAddCD1 BIGINT(10) NOT NULL,
dtitle VARCHAR(125) NOT NULL,
dreleaseyear YEAR(4) DEFAULT NULL,
dlang VARCHAR(3) DEFAULT NULL
);
CREATE TABLE IF NOT EXISTS tracks (
discs_did INTEGER NOT NULL,
tnumber INT(4) NOT NULL,
ttitle VARCHAR(125) NOT NULL,
tseconds INT(4) NOT NULL,
CONSTRAINT pk PRIMARY KEY(discs_did, tnumber),
CONSTRAINT fk FOREIGN KEY(discs_did) REFERENCES discs(did) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT val CHECK(tseconds> 0)
);
CREATE TABLE IF NOT EXISTS track_by_artist (
discs_did INTEGER NOT NULL,
tracks_tnumber INT(4) NOT NULL,
artists_aid INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY(discs_did, tracks_tnumber, artists_aid),
CONSTRAINT fk1 FOREIGN KEY(discs_did) REFERENCES discs(did) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk2 FOREIGN KEY(tracks_tnumber) REFERENCES tracks(tnumber) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk3 FOREIGN KEY(artists_aid) REFERENCES artists(aid) ON DELETE RESTRICT ON UPDATE RESTRICT
数据库被创建,JDBC 驱动程序插入艺术家、光盘和光盘的曲目——一切都很好。最后的插入应该将一个艺术家分配给一个轨道,看起来像
INSERT INTO track_by_artist (discs_did, tracks_tnumber, artists_aid) VALUES (1, 1, 1);
使用 JDBC 这会产生
SQLite-Error #1
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (foreign key mismatch - "track_by_artist" referencing "tracks")
at org.sqlite.core.DB.newSQLException(DB.java:1012)
at org.sqlite.core.DB.newSQLException(DB.java:1024)
at org.sqlite.core.DB.throwex(DB.java:989)
at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
at org.sqlite.core.NativeDB.prepare(NativeDB.java:134)
at org.sqlite.core.DB.prepare(DB.java:257)
at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)
at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:35)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:241)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)
使用 SQLite 的文本前端发出相同的 SQL-Insert 就像奶油一样。
我有点迷茫,不知道如何处理我的 Java 代码。
一些建议,请?
克里斯
解决方案
问题是track_by_artist
你定义了这个外键约束:
CONSTRAINT fk2 FOREIGN KEY(tracks_tnumber) REFERENCES tracks(tnumber) ON DELETE RESTRICT ON UPDATE RESTRICT
尽管tnumber
intracks
不是UNIQUE
(也不应该是)。
外键的父项必须定义为UNIQUE
。
intracks
被PRIMARY KEY
定义为 and 的组合discs_did
,tnumber
这是有道理的,所以这 2 列的组合是唯一的。
您可以做的是在track_by_artist
复合外键中为列discs_did
和tracks_tnumber
该引用discs_did
和tnumber
of定义tracks
:
CREATE TABLE IF NOT EXISTS track_by_artist (
discs_did INTEGER NOT NULL,
tracks_tnumber INT(4) NOT NULL,
artists_aid INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY(discs_did, tracks_tnumber, artists_aid),
CONSTRAINT fk1 FOREIGN KEY(discs_did, tracks_tnumber) REFERENCES tracks(discs_did, tnumber) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk2 FOREIGN KEY(artists_aid) REFERENCES artists(aid) ON DELETE RESTRICT ON UPDATE RESTRICT
);
这样您就不需要为discs_did
.
推荐阅读
- java - 在 Gradle 多项目构建中无法读取另一个项目中的资源文件
- firebase - firebaseConfig.default.auth 承诺拒绝错误
- delphi - Delphi,Indy,如何高效发送记录
- python - Python Dash 应用程序:Heroku 应用程序错误
- python - 如何在VTK中使立方体内的体素半透明?
- python-3.x - 资源耗尽:使用 shape[32,128,768] 分配张量并在 /job:localhost/replica:0/task:0/device:GPU:0 上键入 float 时出现 OOM
- javascript - 如何在函数组件中使用类组件
- cvxpy - CVXPY 在内核化套索上崩溃
- javascript - 反应卡组件
- swift - 字符串内容未更改