Jump to content
benners

Foreign Key Mismatch

Recommended Posts

I am trying to normalise a database to remove duplicate info. I am using SQLite Expert to design the database and test sql queries.

The database is going to store information about setup installers, such as paths, installer specific info, users to install for, type and category of installer blah, blah.

I have attached the database thus far and the tables function are as follows:-

  • category - stores text describing the general usage the installer comes under, such as Browser, Compression etc.
  • installer - this is the main table that has relationships with the other tables and stores info about the installer file, install order etc.
  • installer_user - a link table. Stores the user or computer to install the program for or on.
  • package - stores the type of installer, NSIS, Inno  Nullsoft etc.
  • platform - the OS architecture the installer file is compiled for.
  • postinstall - a list of activities to perform when the main install has finished.
  • postinstall_user - a link table. Stores the users\computers that are allowed to run the post install actions
  • user - a list of computers or usernames. I might separate into two tables, undecided yet.

Now there wil be one program that deals with the installation side and another that acts as a front end for editing the database suchs as adding new files, removing old files etc.

The idea with the editing side is to be able to delete an installer from the installer table say with the id of 1 and all other pertinent information in the other tables will also be deleted. The same goes for deleting a user. All the fields relating to that user will be removed.

I have managed to get that part working for the most part. If I delete either a user or installer, the related info in the installer_user and postinstall tables are removed but since I added the postinstall_user table to link usernames to the postinstall action, this is where I get the foreign key error. If someone can explain why, I am sure it is an obvious reason for someone who knows what they are doing 😄

Cheers

Installer - Copy.db

Share this post


Link to post
Share on other sites

Having worked with databases for over 30 years, and after looking over your database definitions, I see several issues with your database that should be corrected, restructured, or optimized.  I am not going to go into all of those issues since this is primarily an AutoIt forum not a SQL and/or database forum.  However, I will try to answer your specific question as to why you are getting this particular foreign key error, how you can begin to trouble shoot such issues in SQLite in the future, and one way that you can resolve this particular error.

First, to identify glaring foreign key issues, you can execute the following SQLite pragma command on the database.  When you execute the command on the DB that you attached, you will see the following error.  The pragma command will not find all foreign key errors, just the ones that the processor can detect at the time.

pragma foreign_key_check;

Result: foreign key mismatch - "postinstall_user" referencing "postinstall"

Why are you getting that error?  The following excerpt from the SQLite documentation will shed some light on the issue:

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.
.
.
.
 If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys. Errors reported when content is changed are "DML errors" and errors reported when the schema is changed are "DDL errors". So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. The English language error message for foreign key DML errors is usually "foreign key mismatch" but can also be "no such table" if the parent table does not exist. Foreign key DML errors are reported if:

   * The parent table does not exist, or
   * The parent key columns named in the foreign key constraint do not exist, or
   
   * The parent key columns named in the foreign key constraint are not the primary 
     key of the parent table and are not subject to a unique constraint using collating 
     sequence specified in the CREATE TABLE, or
     
   * The child table references the primary key of the parent without specifying the 
     primary key columns and the number of primary key columns in the parent do not 
     match the number of child key columns.

In this particular case, the third bullet, above, is the reason why you are having an issue.  If you refer back to the result of the pragma command, it say that there is a foreign key defined in the postinstall_user table that references the postinstall table, that has a problem.  Your postinstall_user table is defined as:

CREATE TABLE [postinstall_user] (
    [installer_id] INTEGER NOT NULL REFERENCES [installer]([id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
    [sequence_id] INTEGER NOT NULL REFERENCES [postinstall]([sequence_id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
    [user_id] INTEGER NOT NULL REFERENCES [user]([id]) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
    PRIMARY KEY (
        [installer_id],
        [sequence_id],
        [user_id]
        )
    )

As you can see, there is one foreign key in that table that references the postinstall table.  sequence_id is not the complete primary key in the postinstall table.  The primary key in that table is defined as "PRIMARY KEY([installer_id], [sequence_id]))".  Therefore to satisfy the third bullet of the documentation, if you change the definition of that foreign key to match the primary key in the postinstall table, it should fix your issue.  So the definition of that postinstall_user table should look something like:

CREATE TABLE postinstall_user (
    installer_id INTEGER NOT NULL,
    sequence_id  INTEGER NOT NULL,
    user_id      INTEGER NOT NULL,
    PRIMARY KEY (installer_id, sequence_id, user_id),
    FOREIGN KEY (installer_id, sequence_id) REFERENCES postinstall (installer_id, sequence_id) 
        ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
    FOREIGN KEY (user_id) REFERENCES user (id) 
        ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
);

After making that change, when I delete the installer record or a user record, the related records, as you defined them, are successfully deleted.

I hope that helps.

Installer - New.db

Edited by TheXman
Attached modified DB

Share this post


Link to post
Share on other sites

Thanks Xman it does help. I did google the error, just didn't understand the explanations. I have only done 2 simple databases before this so I am still getting to grips. @jchd has

helped me before but they aren't something I use daily so forget stuff.

Quote

I see several issues with your database that should be corrected, restructured, or optimized

I would be interested in your optimization suggestions.

I seem to keep going around in circles when trying to find best practice so just decided to stick with a certain style.

  • Lower case for tables and columns and underscore to separate words.
  • Table and column names in the singular.
  • Keep table and column names short but descriptive (with no abbreviations)
  • The primary key column will be id, as long as it's singular.
  • Normalise as much as possible

Thanks for the help.

Share this post


Link to post
Share on other sites
8 hours ago, benners said:

I would be interested in your optimization suggestions.

As I mentioned before, I am not going to go into all of the issues that I noticed.  But since you asked, I will point out a few things.  Please keep in mind that all relational database management systems (MS SQL Server, Oracle, Postgre, MySQL, SQLite, etc.) have their own little quirks and differences.  Since you are working with SQLite, my observations and suggestions will be targeted specifically towards SQLite.

  • In several of your table definitions you have an INTEGER PRIMARY KEY defined with UNIQUE.  For example:
    CREATE TABLE [category] (
        [id]    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
        [class] TEXT NOT NULL UNIQUE
    );

    An INTEGER PRIMARY KEY, by definition, uniquely identifies any given row in a table.  So adding UNIQUE constraint to an INTEGER PRIMARY KEY definition is superfluous.

  • In several of your table definitions you have an INTEGER PRIMARY KEY defined with AUTOINCREMENT (see example above).  As stated in the SQLite documentation for AUTOINCREMENT: "The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.".  INTEGER PRIMARY KEY and AUTOINCREMENT work similarly in that they both will maintain/generate a unique rowid for you if NULL is used for its value upon insert.  However, if you use the AUTOINCREMENT keyword, it will never reuse previously deleted key values.  It does this by creating and maintaining an additional table (sqlite_sequence) that keeps track of the highest used AUTOINCREMENT keys.  Based on the description of your project, there doesn't seem to be a need for the additional AUTOINCREMENT functionality.

  • In general, I prefer to make explicit SQL table definitions as opposed to implicit ones.  In several cases, you use the UNIQUE column constraint to make sure that values are unique.  What this does, implicitly, is to create a UNIQUE INDEX on that column.  In my opinion, it is better to explicitly create any UNIQUE INDEXES yourself.  Doing so makes it easier to see and maintain these indexes as well as gives you more control over the indexes themselves.  Some SQL tools, unless explicitly told to do so, do not even show implicit SQLite tables and indexes.  Implicit indexes in SQLite begin with "sqlite_autoindex_".  If you query the sqlite_master table (select * from sqlite_master), you will see all of the implicitly created indexes.  Of course whether use the UNIQUE column constraint or explicitly define your unique indexes is a personal preference.  But my experience has taught me, for several reasons, that it is usually better to define them explicitly.

Those are just a few of the things that stuck out the most. 

For being just your 3rd database definition, I think you did a pretty good job of normalizing it and getting the foreign key relationships relatively correct.  Kudos!

:thumbsup:

Installer - Original.db.sql

Edited by TheXman

Share this post


Link to post
Share on other sites

200% agreed!


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By dmob
      So I am trying to implement an archive system of sorts for my (SQLite) DB app. I wrote a function to attach a separate (archive) DB and
      sync the columns with main DB. If archive DB file does not exist, create file with _SQLiteOpen then close the file (and thus connection) with SQLite_Close.
      This works as intended, however, after the create operation, all subsequent _SQLite_* functions returned a "Library misuse error".
      After a little digging I found the problem in the _SQLite_Close function: it clears the "last opened database" handle even when there still is a live
      DB connection open. All other functions then "think" there is no DB connection active. I hacked two functions in the UDF for a quick fix:

      In _SQLite_Close: Change
      ... $__g_hDB_SQLite = 0 __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] to:
      $__g_hDB_SQLite = __SQLite_hDel($__g_ahDBs_SQLite, $hDB) Return $iRval[0] and in Func __SQLite_hDel changed
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) EndFunc ;==>__SQLite_hDel to:
      Func __SQLite_hDel(ByRef $ahLists, $hGeneric) Local $iElement = _ArraySearch($ahLists, $hGeneric) If $iElement > 0 Then _ArrayDelete($ahLists, $iElement) Return $ahLists[UBound($ahLists)-1] ; Return last opened db EndIf Return 0 EndFunc ;==>__SQLite_hDel so it preserves last opened DB again.
       
      My archive function now works great
      I'm not sure if this should be classified as a bug, but I believe so...
      Hope this helps someone before
    • By argumentum
      #include <SQLite.au3> ;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools ;-- for manipulating JSON data stored in the database. ;-- This is a gist showing SQLite return query as a JSON object. ;-- https://www.sqlite.org/json1.html Example() Func Example() _SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm _SQLite_Open() ; ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/ If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4 If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _ '(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _ '(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _ '(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5 ; -- Get query data as a JSON object using the ; -- json_group_object() [1] and json_object() [2] functions. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_object(" & _ " email," & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS json_result" & _ " FROM (SELECT * FROM users WHERE created > ""02-01-01"");") ; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}} ; -- Get query data as a JSON object using the ; -- json_group_array() function to maintain order. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_array(" & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS my_json_result_OrAnythingReally" & _ " FROM (SELECT * FROM users ORDER BY created);") ; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}] ;-- Links ;-- [1] https://www.sqlite.org/json1.html#jgroupobject ;-- [2] https://www.sqlite.org/json1.html#jobj ; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2 EndFunc ;==>Example Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1) Local $aResult, $iRows, $iColumns If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF) Else _SQLite_Display2DResult($aResult) EndIf ConsoleWrite(@CRLF) EndFunc ;==>_SQLite_GetTable2d_ArrayToConsole Based on this example, you can build your own query. 
      The code has all the explanations.
      Enjoy  
    • By argumentum
      #include <SQLite.au3> ;~ #include <SQLite.dll.au3> Local $hQuery, $aRow, $aNames _SQLite_Startup() ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) _SQLite_Open() ; open :memory: Database _SQLite_Exec(-1, "CREATE TABLE aTest (A,B int not null unique ,C text);") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('c','2','World');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('b','3',' ');") _SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('a','1','Hello');") _SQLite_Query(-1, "SELECT _ROWID_,* FROM aTest ORDER BY a;", $hQuery) _SQLite_FetchTypes($hQuery, $aNames) ; Read out Column Types ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) _SQLite_FetchNames($hQuery, $aNames) ; Read out Column Names ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CRLF) While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CRLF) WEnd _SQLite_Exec(-1, "DROP TABLE aTest;") _SQLite_Close() _SQLite_Shutdown() ; Output: ; INTEGER int text ; rowid A B C ; 3 a 1 Hello ; 2 b 3 ; 1 c 2 World Func _SQLite_FetchTypes($hQuery, ByRef $aTypes) Dim $aTypes[1] If __SQLite_hChk($hQuery, 3, False) Then Return SetError(@error, 0, $SQLITE_MISUSE) Local $avDataCnt = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_column_count", "ptr", $hQuery) If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error If $avDataCnt[0] <= 0 Then Return SetError(-1, 0, $SQLITE_DONE) ReDim $aTypes[$avDataCnt[0]] Local $avColName For $iCnt = 0 To $avDataCnt[0] - 1 $avColName = DllCall($__g_hDll_SQLite, "wstr:cdecl", "sqlite3_column_decltype16", "ptr", $hQuery, "int", $iCnt) If @error Then Return SetError(2, @error, $SQLITE_MISUSE) ; DllCall error $aTypes[$iCnt] = $avColName[0] Next Return $SQLITE_OK EndFunc ;==>_SQLite_FetchTypes  If you wanna build a proper JSON string, you may want to know if is {"int":123} or {"text":"123"}
      and for that, this can help, obviously only when declared in the SQLite table.
    • By argumentum
      I was thinking but I don't have the experience, so you may have the experience.
      I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
      The DB is now in MySQL. I wanna do all this chopping and use SQLite. 
      Thanks
      [solved]
    • By Skysnake
      Hi
      I am trying to set Accelerator keys from an array.
      I select the KEY and CONTROL from a SQLite table, the Array looks like that generated for the Helpfile, but I can't get the CONTROLS to resolve...
      I though about Assign & Eval, but not sure if that's a step in the right direction. IsDeclared shows that the $var exists in Local Scope -1.  
       
      Local $Main = GUICreate("Custom MsgBox", 225, 80) GUICtrlCreateLabel("Please select a button.", 10, 10) Local $idButton_Yes = GUICtrlCreateButton("Yes", 10, 50, 65, 25) Local $idButton_No = GUICtrlCreateButton("No", 80, 50, 65, 25) Local $idButton_Exit = GUICtrlCreateButton("Exit", 150, 50, 65, 25) Local $query, $aResult, $iRows, $iColumns $query = "" ;reset $query = "Select hotkey_key, hotkey_ctrl from mytable where mykeys = 'hotkey' ; " ; ; Query $iRval = _SQLite_GetTable2d($sqliteDb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then Local $sizeofHotkeys = UBound($aResult) - 1 ConsoleWrite("$sizeofHotkeys " & $sizeofHotkeys & @CRLF) If $sizeofHotkeys > 0 Then Local $main__aAccelKeys[$sizeofHotkeys][2] For $i = 0 To $sizeofHotkeys - 1 $j = $i + 1 ; replace friendly text with code -- ! alt + Shift ^ Ctrl # Windows $aResult[$j][0] = StringReplace($aResult[$j][0], "Alt", "!") $aResult[$j][0] = StringReplace($aResult[$j][0], "Shift", "+") $aResult[$j][0] = StringReplace($aResult[$j][0], "Ctrl", "^") $main__aAccelKeys[$i][0] = $aResult[$j][0] ;--- $main__aAccelKeys[$i][1] = $aResult[$j][1] ;--- Next ;~ Row|Col 0|Col 1 ;~ Row 0|F2|$idButton_Yes ;~ Row 1|F3|$idButton_No _DebugArrayDisplay($main__aAccelKeys) Local $rv = GUISetAccelerators($main__aAccelKeys, $Main) GUISetState(@SW_SHOW) ; Display the GUI.  
      Please note that this is a modified Helpfile example.
      The Helpfile specifies (a) WinHandle and (b) last Gui created.
      --> the example uses a control not a WinHandle and (b) what happens with ChildGuis? Also, the HelpFile specifies lower case, yet the examples show "{F1}" upper case?
      Also, is there a way to check the result of the GuiSetAccelerator function? 
      Note, if I add these to lines after the FOR loop, then the F1 works, and the DebugArrayDisplays shows control 4... not it's name... So I am in the right place, but my $vars names do not convert to their control numbers in the GUI
      Next $main__aAccelKeys[$sizeofHotkeys - 1][0] = "{F1}" ; -- -- use the extra row for the F1 $main__aAccelKeys[$sizeofHotkeys - 1][1] = $ChmHLP ;--- Skysnake
×
×
  • Create New...