Sqlite3 "Forgets" to Use Foreign Keys

SQLite3 forgets to use foreign keys

I think I can answer my own question: The documentation says: Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled for each database connection separately. Annoying, but it's finally working now.

Sqlite foreign key insert should fail

Enable the foreign key constraints in the connection string like so: "Data Source=:memory:;foreign keys=true;"

Foreign Key constraint doesn't work

Are you sure foreign key support is enabled?

Assuming the library is compiled with foreign key constraints enabled,
it must still be enabled by the application at runtime, using the
PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

Does SQLite really not preserve data integrity of foreign key constraints by default?

I will try to give an answer myself:

No, if configured right, SQLite preserves data integrity in this situation. "NO ACTION" is used by default and this prohibits deletion or update of a master key if there is still a refering key from an referencing table (tested with 3.7.x).
My fault was that I was not aware that PRAGMA foreign_keys = ON; must be configured for every new connection to the database.

Edit: I think the SQLite documentation is misleading here.

SQLite3 + Python3 - creating a database with foreign keys

1. As the message tells you, you can only execute one statement at a time.
2. You need to execute PRAGMA foreign_keys only once for the connection.
3. Executing BEGIN TRANSACTIOn makes no sense here.
4. A PRIMARY KEY constraint already implies NOT NULL.
5. A PRIMARY KEY constraint already implies UNIQUE.
6. If a table name contains a space, you must quote it.
7. When you declare a foreign key as a table constraint, you must write it after all the columns, and must not forget the delimiting comma.
8. When you declare a foreign key as a column constraint, you must use the correct syntax:

c.execute('''
          CREATE TABLE Journey (
              ID INTEGER PRIMARY KEY AUTOINCREMENT,
              Driver_ID INTEGER NOT NULL REFERENCES Driver(ID),
              Origin text,
              Destination text,
              SeatsAvailable integer,
              Date text,
              Time text,
              DriverNotes text,
              PassengerNotes text)
          ''')
c.execute('''
          CREATE TABLE Assigned_Passengers (
              ID INTEGER PRIMARY KEY AUTOINCREMENT,
              Journey_ID INTEGER NOT NULL REFERENCES Journey(ID),
              Passenger_ID INTEGER NOT NULL REFERENCES Passenger(ID))
          ''')

Sqlite FOREIGN KEY constraint failed during add column

My table is called Screens. It has two tables with foreign key
dependencies. They are called Topic and ScreenTypes. Both tables are
very small and they only have 2 columns each (id and name). The
Screens table contains the columns TopicId and ScreenTypeId plus a
couple of other columns. All three table have primary indexes.
Everything has been working fine for the past few weeks.

From the above and your comments then this appears to create the Screens Table, the Topics Table and the ScreenTypes table and additionally populate the tables with some data:-

DROP TABLE IF EXISTS Screens;
CREATE TABLE IF NOT EXISTS Screens (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
    Video TEXT NOT NULL, 
    TopicId INTEGER NOT NULL, 
    Instructions TEXT, 
    ScreenTypeId INTEGER, 
    SortOrder INTEGER DEFAULT (10), 
    Image TEXT, 
    NewColumn INTEGER, 
    FOREIGN KEY (TopicId) REFERENCES Topics (id), 
    FOREIGN KEY (ScreenTypeId) REFERENCES ScreenTypes (id)
    )
;

DROP TABLE IF EXISTS Topics;
CREATE TABLE IF NOT EXISTS Topics (ID INTEGER PRIMARY KEY, name TEXT);
DROP TABLE IF EXISTS ScreenTypes;
CREATE TABLE IF NOT EXISTS ScreenTypes (ID INTEGER PRIMARY KEY, name TEXT);

INSERT INTO Topics (name) VALUES ('Topic1'),('Topic2'),('Topic3'),('Topic4');
INSERT INTO ScreenTypes (name) VALUES ('SreenType1'),('ScreenType2'),('ScreenType3'),('ScreenType4');
INSERT INTO Screens (Video,TopicId,Instructions,ScreenTypeId,Image,NewColumn) VALUES
    ('Video1',2,'do this 001',3,'Image1','blah'),
    ('Video2',2,'do this 002',3,'Image2','blah'),
    ('Video3',1,'do this 002',1,'Image3','blah'),
    ('Video4',3,'do this 004',4,'Image4','blah'),
    ('Video5',4,'do this 005',1,'Image5','blah')
;

Then, I tried to add a new column to the Screens table called
ScreenNumber. The new column is numeric and has no restrictions
whatsoever. But, when I tried to commit the change to the schema, I
got a foreign key constraint error.

The following works :-

ALTER TABLE Screens ADD COLUMN ScreenNumber INTEGER DEFAULT 100;

as per :-

  ALTER TABLE Screens ADD COLUMN ScreenNumber INTEGER DEFAULT 100
  OK
  Time: 0.083s

and then using the following

SELECT * FROM Screens;

The result is :-

• As can be seen, there were no FK constraint conflicts and the column has been added and the default value of 100 applied.

I suspect that your issues with FK Constraints is that you are progressively try to correct issues.

First (at a guess) you try altering (renaming) the Screens table but can't because of the Fk constraint conflicts with the Fields table. You then try altering the Fields table but still you get FK conflicts, due to :-

If an "ALTER TABLE ... RENAME TO" command is used to rename a table that is the parent table of one or more foreign key constraints, the definitions of the foreign key constraints are modified to refer to the parent table by its new name. The text of the child CREATE TABLE statement or statements stored in the sqlite_master table are modified to reflect the new parent table name.

SQLite Foreign Key Support - 5. CREATE, ALTER and DROP TABLE commands

Of course, if acccording to your description, you only want to add the column then the ALTER TABLE Screens ADD COLUMN ScreenNumber INTEGER (with default value if wanted) works without the need to rename tables.

Can't create sqlite tables with foreign key

The primary key is simply called index:

CREATE TABLE "character" (
    "character_id"  TEXT NOT NULL,
    "index" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "member_index"  INTEGER NOT NULL,
        FOREIGN KEY (member_index)
       REFERENCES member ("index");

index however, is a really bad name for a column because it is a SQL keyword. As a habit, I also make primary keys the first column in a table. I would change the name and recommend:

CREATE TABLE members (
    member_id INTEGER PRIMARY KEY AUTOINCREMENT,
    member_name TEXT NOT NULL,
    platform_id INTEGER NOT NULL
);

CREATE TABLE characters (
    character_id INTEGER PRIMARY KEY AUTOINCREMENT,
    character_name  TEXT NOT NULL,
    member_id  INTEGER NOT NULL,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE
);

Note the changes:

• No escaping of the column names. These are not needed and the double quotes just clutter the code.
• The "id"s are all numeric. There is no requirement, but this is rather typical usage of the term.
• What were the "id"s are now called "name"s.
• The table names are in the plural. This is a convention that I use because the tables represent a set of entities, not just one.
• NOT NULL and UNIQUE are redundant when used with PRIMARY KEY. No need for them.
• SQLite requires INTEGER to be fully spelled out for primary keys.
• The changes in names mean that the primary key and foreign key have the same names.

---

Related Topics