CODEWITHSUNDEEP
sqlite
Aserian
Aserian

Reputation: 1117

sqlite3 foreign key mismatch on existing value that references primary key

I haven't used much sqlite3, but I have used many other SQL dbms' and this issue is confusing me a bit.

Here is the schema for my database:

CREATE TABLE "user" (user TEXT PRIMARY_KEY, 
      password BINARY(255) NOT NULL, 
      server_id TEXT NOT NULL UNIQUE);

CREATE TABLE "channels" 
    (server_id TEXT NOT NULL, 
     channel_name TEXT NOT NULL, 
     FOREIGN KEY(server_id) REFERENCES user(server_id) ON DELETE CASCADE ON UPDATE CASCADE, 
     CONSTRAINT channels_pk PRIMARY KEY (channel_name, server_id));

CREATE TABLE "messages" 
      (server_id TEXT NOT NULL, 
       channel_name TEXT NOT NULL, 
       user TEXT NOT NULL, 
       timestamp INTEGER, 
       content TEXT NOT NULL, 
       FOREIGN KEY(server_id) REFERENCES channels(server_id) ON DELETE CASCADE ON UPDATE CASCADE, 
       FOREIGN KEY(channel_name) REFERENCES  channels(channel_name) ON DELETE CASCADE ON UPDATE CASCADE, 
       FOREIGN KEY(user) REFERENCES user(user) ON DELETE CASCADE ON UPDATE CASCADE, 
       CONSTRAINT messages_pk PRIMARY KEY (server_id, channel_name, user, timestamp));

I get an issue on the following:

A user currently exists, named 'hello':

hello | $argon2d$v=19$m=4096,t=3,p=1$oyvxHO2dXfTKVFJ/6/81Nw$MJk82FuxIQLJLfENYJ8Yq0dURN8vga4VXqyJM2vpS3E | hq8PYzf

A channel exists:

hq8PYzf | DefaultChannel

Here is where the explosion occurs:

INSERT INTO messages (server_id, channel_name, user, timestamp, content) 
    VALUES ('hq8PYzf','DefaultChannel', 
      'hello', 1491688597191, 
      'here is a message!');

I am greeted with:

Error: foreign key mismatch - "messages" referencing "user"

I have searched the issue and a lot of people say this occurs when a foreign key is not referencing a primary key, however, every foreign key in messages is referencing something that is part of a primary key.

I could see if the issue was with channel or server, because they are partial primary keys, but user is the only primary key of the user table.

Any help you can offer would be greatly appreciated.

UPDATE:

I found this answer that specified that primary keys that are composite must have matching composite foreign keys, ie my foreign key in messages referencing the primary key of channels should be 

FOREIGN KEY(channel_name, server_id) REFERENCES channels(channel_name, server_id)

Instead of how I had it as two separate keys.

I have changed this in the schema - but it still throws the error.

Upvotes: 0

Views: 532

Answers (1)

CL.
CL.

Reputation: 180020

The error message does not complain about the reference to channels but about the one to user.

The problem is that the user table does not have a primary key:

CREATE TABLE "user" (user TEXT PRIMARY_KEY,
                                      ^

Upvotes: 1

Related Questions