Foreign key mismatch in SQLite3

Question

I'm using SQLite3 for simple application (like agenda). Agenda is multilingual. That's the reason why I have table rooms in more languages (count of languages must be independent)

At first, I have a table rooms with this definition:

CREATE TABLE rooms 
(
    room_code VARCHAR2(32),
    lang_code CHAR(2),
    room_name VARCHAR(32),
    PRIMARY KEY (room_code, lang_code),
    FOREIGN KEY (lang_code) REFERENCES languages (lang_code),
    CHECK (LENGTH(lang_code) = 2),
    CHECK (room_code <> '')
);

Then I have a table events with this definition:

CREATE TABLE events 
(
    room_code VARCHAR2(32) NOT NULL,
    message_code VARCHAR(64) NOT NULL,
    time_start DATETIME NOT NULL ,
    time_end DATETIME NOT NULL,
    PRIMARY KEY (message_code, room_code),
    FOREIGN KEY (room_code) REFERENCES rooms (room_code),
    -- Check the time is between 00:00 and 23:59
    CHECK (time_start and time_end BETWEEN TIME('00:00') AND TIME('23:59')),
    -- Start time must be lower than end time
    CHECK (time_start < time_end),
    -- Message code cannot be empty
    CHECK (message_code <> '') 
);

But in fact, I got this error in any attempt of insert:

Error: near line 91: foreign key mismatch - "events" referencing "rooms"

Why can I not reference another table? I read that all foreign keys of table B must be PRIMARY KEY in table A. But this dependency is OK for me. Foreign key room_code from table events it's also primary key room_code in table rooms.

Is that possible the solution of this instead of TRIGGERS? In fact, I want use rather KEYS instead of TRIGGERS if is it possible. Thanks

Best regards

MikeT · Accepted Answer · 2018-07-17 19:54:35Z

I believe that your issue is due to non conformation with :-

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index. If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table. For example,

SQLite Foreign Key Support

Note the primary key as opposed to part of a primary key.

In short, the column(s) referenced by the foreign key must be known to be unique.

As such you could either add an index for the room_code column or define it to utilise the UNIQUE constraint e.g. :-

DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS rooms;
DROP INDEX IF EXISTS room_code_idx;
CREATE TABLE IF NOT EXISTS rooms (
    room_code VARCHAR2(32) UNIQUE, --<<<<<<<<<< ADDED UNIQUE
    lang_code CHAR(2),
    room_name VARCHAR(32),
    PRIMARY KEY (room_code, lang_code),
    -- FOREIGN KEY (lang_code) REFERENCES languages (lang_code)  
    CHECK (LENGTH(lang_code) = 2),
    CHECK (room_code <> '')
);

CREATE TABLE IF NOT EXISTS events (
    room_code VARCHAR2(32) NOT NULL,
    message_code VARCHAR(64) NOT NULL,
    time_start DATETIME NOT NULL ,
    time_end DATETIME NOT NULL,
    PRIMARY KEY (message_code, room_code),
    FOREIGN KEY (room_code) REFERENCES rooms (room_code),
    -- Check the time is between 00:00 and 23:59
    CHECK (time_start and time_end BETWEEN TIME('00:00') AND TIME('23:59')),
    -- Start time must be lower than end time
    CHECK (time_start < time_end),
    -- Message code cannot be empty
    CHECK (message_code <> '') 
);

INSERT INTO rooms VALUES('room001','xx','This is room 1');
INSERT INTO events VALUES('room001','This is a message for room 1','01:00','02:00');

Note FK to lang_code commented out for convenience/brevity

results in :-

INSERT INTO rooms VALUES('room001','xx','This is room 1')
> Affected rows: 1
> Time: 0.3s


INSERT INTO events VALUES('room001','This is a message for room 1','01:00','02:00')
> Affected rows: 1
> Time: 0.301s

Of course, you could just define the PRIMARY KEY to just be the room_code column as it then has to be unique.

If the room_code column will/may not be unique and that the only guaranteed uniqueness will be the room_code column and the lang_code column combined. The you would need to define a composite foreign key (refer to link above).

Collectives™ on Stack Overflow

Foreign key mismatch in SQLite3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related