SQLite Forum

Linking tables
Login

Linking tables

(1) By anonymous on 2021-12-02 15:37:37 [link] [source]

Hi everyone, I am very new in programming in general and in using SQLite (literally this is my first project ever - I'm a student) so please forgive me my dumb questions. Anyways:

I have a few problems with the SQLite database when it comes to assigning values to the foreign/primary keys and the link between different tables.

I have literally one "main table" which is the "whole" of an aggregation relationship. This "whole" contains all foreign keys of the other five tables which are the "parts of" tables. Now my problem is, that the FK for the main table always displays NULL even if i stored data sets into the "parts" tables and they have a PK. Can somebody help me?

(2) By Ryan Smith (cuz) on 2021-12-02 15:55:41 in reply to 1 [link] [source]

All we can tell from your description above is: Yep, you probably have a problem.

If you want more help than that, please show the exact schema SQL for making the tables. That should already make the problem clear, but just in case it is more obscure, posting the data (or some of it) for each table in the form of SQL INSERT statements will also help.

(4) By anonymous on 2021-12-02 16:21:46 in reply to 2 [link] [source]

Thank you so much for you quick reply, Ryan. This is what the code looks like for now (written with Python, I'm sorry that my tables have that many attributes :o):

Create all tables for the database:

"Main" table:

    self.cur.execute("""CREATE TABLE IF NOT EXISTS lastenheft (LastId INTEGER AUTO_INCREMENT PRIMARY KEY, LastName TEXT, UId INTEGER, ZielId INTEGER, IstId Integer, sysAnfordId INTEGER, EinfAnfId INTEGER,

    FOREIGN KEY(UId) REFERENCES unternehmen(UId),
    FOREIGN KEY(UId) REFERENCES ziel(ZielId),
    FOREIGN KEY(UId) REFERENCES istZustand(IstId),
    FOREIGN KEY(UId) REFERENCES sysAnforderung(sysAnfordId),
    FOREIGN KEY(UId) REFERENCES einfAnforderung(EinfAnfId))""")

"parts" tables:

    self.cur.execute("""CREATE TABLE IF NOT EXISTS
                  unternehmen (UId INTEGER AUTO_INCREMENT PRIMARY KEY, Uname TEXT, Uadresse TEXT, TochterU TEXT, Utelefonnr TEXT, Ufax TEXT, Uweb TEXT, Ugroesse TEXT, Uprognose TEXT, Ugruendung TEXT, Ubranche TEXT, UprodBeschr TEXT, Uanlage BLOB, Usonstiges TEXT, 
                  LastId INTEGER,
                  FOREIGN KEY(LastId) REFERENCES lastenheft(LastId))""")


    self.cur.execute("""CREATE TABLE IF NOT EXISTS 
                  ziel(ZielId INTEGER PRIMARY KEY, Anlass TEXT, Beschreibung TEXT, Ziele TEXT, Start DATE, Ende DATE, Sonstiges TEXT, 
                  LastId INTEGER,
                  FOREIGN KEY(LastId) REFERENCES lastenheft(LastId))""")  #, CHECK(Start<Ende)

    command4 = """CREATE TABLE IF NOT EXISTS
                  istZustand (IstId INTEGER PRIMARY KEY, Unetz TEXT, Uhard TEXT, Ubs TEXT, Uanwend TEXT, Uschnitt TEXT, Uunterschied TEXT, Ugp TEXT, Uprozessd TEXT, LastId INTEGER,
                  FOREIGN KEY(LastId) REFERENCES lastenheft(LastId))"""
    self.cur.execute(command4) 

    command5 = """CREATE TABLE IF NOT EXISTS
                  sysAnforderung (sysAnfordId INTEGER PRIMARY KEY, ZielgruppEx TEXT, ZielgruppIn TEXT, Hardware TEXT, Funktionen TEXT, Daten TEXT, Ausgaben TEXT, Schnittstellen TEXT, Nutzeranford TEXT, Systemanford TEXT, Weitereanford TEXT, LastId INTEGER,
                  FOREIGN KEY(LastId) REFERENCES lastenheft(LastId))"""
    self.cur.execute(command5) 

    command6 = """CREATE TABLE IF NOT EXISTS
                  einfAnforderung (EinfAnfId INTEGER PRIMARY KEY, Leistungen TEXT, Unterstuetzung TEXT, Durchfuehrung TEXT, Doku TEXT, LastId INTEGER,
                  FOREIGN KEY(LastId) REFERENCES lastenheft(LastId))"""
    self.cur.execute(command6)

(6) By Ryan Smith (cuz) on 2021-12-02 16:33:21 in reply to 4 [link] [source]

This looks all fine in principle.

Your table definition with he FK definitions need to be the last statement called though, you cannot have that succeed when the referenced tables do not exist yet.

AS to the problem of the original question, now we know the schema, can you now show an example of a set of values you would insert and what do you expect after that insert would be in the different tables vs. what you actually see in the tables? (Just two or three inserts will do).

(7) By anonymous on 2021-12-02 16:40:18 in reply to 6 [link] [source]

Thank you! I posted two of the insert functions in reply to Larry :)

So sadly there are no values given when I try to look at the values of foreign keys in the "main" table lastenheft...

(8) By Ryan Smith (cuz) on 2021-12-02 17:42:05 in reply to 7 [source]

Now that I have insert values and on looking closer, it's clear you are having circular references.

Not sure how the class on foreign keys went in your establishment, but it's a one-way street. if table X has a foreign-key reference to table Y, then table Y cannot ALSO have a foreign Key reference to table X. Else a race condition of sorts is created. In other words, if I want to insert something into table X, it means a corresponding Key value must already exist in Table Y, and I cannot insert it in table Y first because its foreign Key constraint requires the value to already exist in table X, which is circular.

Now you can work around this in two ways, like disabling foreign key checking before inserting, or you can insert NULL values for the columns first and then later update them once values exist in the other table. Any of these workaround however fully negates the entire point of foreign keys and will make your code horribly botched.

Pick the table which is the boss of the values, then make a reference to it from the table which references/is interested in/consumes the values, and not the other way around. If I remove all the FK statements from your data tables while leaving all of them in the consuming main table, then it all works fine.

See here:

  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version 2.1.3.11.
  -- ================================================================================================

PRAGMA foreign_keys = 1;

CREATE TABLE IF NOT EXISTS einfAnforderung(
	EinfAnfId INTEGER PRIMARY KEY,
	Leistungen TEXT,
	Unterstuetzung TEXT,
	Durchfuehrung TEXT,
	Doku TEXT,
	LastId INTEGER
);

CREATE TABLE IF NOT EXISTS sysAnforderung(
	sysAnfordId INTEGER PRIMARY KEY,
	ZielgruppEx TEXT,
	ZielgruppIn TEXT,
	Hardware TEXT,
	Funktionen TEXT,
	Daten TEXT,
	Ausgaben TEXT,
	Schnittstellen TEXT,
	Nutzeranford TEXT,
	Systemanford TEXT,
	Weitereanford TEXT,
	LastId INTEGER
);

CREATE TABLE IF NOT EXISTS istZustand(
	IstId INTEGER PRIMARY KEY,
	Unetz TEXT,
	Uhard TEXT,
	Ubs TEXT,
	Uanwend TEXT,
	Uschnitt TEXT,
	Uunterschied TEXT,
	Ugp TEXT,
	Uprozessd TEXT,
	LastId INTEGER
);

CREATE TABLE IF NOT EXISTS ziel(
	ZielId INTEGER PRIMARY KEY,
	Anlass TEXT,
	Beschreibung TEXT,
	Ziele TEXT,
	Start DATE,
	Ende DATE,
	Sonstiges TEXT,
	LastId INTEGER
);

CREATE TABLE IF NOT EXISTS unternehmen (
	UId INTEGER AUTO_INCREMENT PRIMARY KEY,
	Uname TEXT,
	Uadresse TEXT,
	TochterU TEXT,
	Utelefonnr TEXT,
	Ufax TEXT,
	Uweb TEXT,
	Ugroesse TEXT,
	Uprognose TEXT,
	Ugruendung TEXT,
	Ubranche TEXT,
	UprodBeschr TEXT,
	Uanlage BLOB,
	Usonstiges TEXT,
	LastId INTEGER
);

CREATE TABLE IF NOT EXISTS lastenheft (
	LastId INTEGER AUTO_INCREMENT PRIMARY KEY,
	LastName TEXT,
	UId INTEGER,
	ZielId INTEGER,
	IstId Integer,
	sysAnfordId INTEGER,
	EinfAnfId INTEGER,
	FOREIGN KEY(UId) REFERENCES unternehmen(UId),
	FOREIGN KEY(UId) REFERENCES ziel(ZielId),
	FOREIGN KEY(UId) REFERENCES istZustand(IstId),
	FOREIGN KEY(UId) REFERENCES sysAnforderung(sysAnfordId),
        FOREIGN KEY(UId) REFERENCES einfAnforderung(EinfAnfId)
);

INSERT INTO unternehmen VALUES(NULL, 2, 'hello', 'j', 'j', 'k', 'j', 'k', 'm', 'j', 'kd', '2', 'j', 'j', 'j');


SELECT * FROM unternehmen;

  --       |            |        |        |Utelefon-|     |     |        |         |Ugruendu-|        |            |       |Usonstig-|      
  -- UId   |    Uname   |Uadresse|TochterU|    nr   | Ufax| Uweb|Ugroesse|Uprognose|    ng   |Ubranche| UprodBeschr|Uanlage|    es   |LastId
  -- ------|------------|--------|--------|---------|-----|-----|--------|---------|---------|--------|------------|-------|---------|------
  -- NULL  |      2     |hello   |    j   |    j    |  k  |  j  |    k   |    m    |    j    |   kd   |      2     |   j   |    j    |   j  



  -- ------------------------------------------------------------------------------------------------

  -- 2021-12-02 19:36:24.084  |  [Success]    Script Success.

Also, I don't know if your programming language cares about it, but you have dangling commas at the end of your call and not enough values for to match the insert prototype. You have to check that very well.

Perhaps "try" the SQL outside of the code first making sure it works and then try to adapt it to code. The other way madness lies.

Let us know if there are any other questions.

(9) By anonymous on 2021-12-03 04:05:46 in reply to 4 [link] [source]

This is not relevant to your stated question, but will likely bite you soon anyway: The columns you're declaring as INTEGER AUTO_INCREMENT PRIMARY KEY, such as lastenheft.LastId, are not going to behave as you likely expect them to; namely, they will not automatically increment. SQLite recognizes an AUTOINCREMENT keyword (note the lack of an underscore), and under normal circumstances it will automatically increment an ordinary INTEGER PRIMARY KEY, but it does not know what AUTO_INCREMENT means, and instead assumes that you're declaring a column of the type INTEGER AUTO_INCREMENT. SQLite still accepts the definition, even though most database systems wouldn't recognize such a strange type name, because of its uniquely loose approach to column types - but because of the special rule that causes INTEGER PRIMARY KEY columns to automatically increment, the AUTO_INCREMENT will actually prevent your key from automatically incrementing! You may want to read the following documents, which explain (respectively) how to get automatically incrementing primary keys in SQLite, and how the unique type system works.

(3.1) By Larry Brasfield (larrybr) on 2021-12-02 16:18:01 edited from 3.0 in reply to 1 [link] [source]

(I was hoping Ryan would post a kindly stated and pertinent response.)

It appears to me that, having specified the foreign and primary keys and relationships, you expect some of such data (primary keys) to spring into existence when you insert other such data (foreign keys).

You DML ("data modification language" SQL) doing INSERT and/or UPDATE needs to leave values in the various table rows which satisfy the primary/foreign key relationships you set out (or imagined) in your schema. There is no auto-magical "springing into place" for rows needed to satisfy them. (There can be auto-magical vanishing of rows whose foreign key(s) reference a primary key that was made to vanish. But that is beyond today's problem.)

As Ryan says, further details would probably be needed or useful as part of describing your problem. This is part of making clear what you expected to happen in contrast to what did or did not happen.

(5) By anonymous on 2021-12-02 16:30:03 in reply to 3.1 [link] [source]

Hi Larry, thanks for your reply! So my insert functions work with prepared statements and I already had some problems with bindings in this context.

So those are two of my insert functions as an example, I am not sure if they are correct like that:

Insert functions for the "main" table and one other "parts" table:

def insertLastenheft(self,LastName): """This function inserts the data sets into the database(table/relation) of the Lastenheft

    Args:
        LastName ([type]): [description]
    """        
    self.cur.execute("INSERT INTO lastenheft VALUES (NULL,?, NULL, NULL, NULL, NULL, NULL, NULL, NULL)", (LastName,)) #Schutz vor SQL-Injections
    self.conn.commit()

def insertUnternehmen(self, UId, Uname, Uadresse, TochterU, Utelefonnr, Ufax, Uweb, Ugroesse, Uprognose, Ugruendung, Ubranche, UprodBeschr, Uanlage, Usonstiges, LastId):
    """This function inserts the data sets into the database(table/relation) of the Unternehmen (first GUI "page")

    Args:
        UId (Text): 
        Uname ([type]): [description]
        Uadresse ([type]): [description]
        TochterU ([type]): [description]
        Utelefonnr ([type]): [description]
        Ufax ([type]): [description]
        Uweb ([type]): [description]
        Ugroesse ([type]): [description]
        Uprognose ([type]): [description]
        Ugruendung ([type]): [description]
        Ubranche ([type]): [description]
        UprodBeschr ([type]): [description]
        Uanlage ([type]): [description]
        Usonstiges ([type]): [description]
        LastId ([type]): [description]
    """                  
    self.cur.execute("INSERT INTO unternehmen VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (Uname, Uadresse, TochterU, Utelefonnr, Ufax, Uweb, Ugroesse, Uprognose, Ugruendung, Ubranche, UprodBeschr, Uanlage, Usonstiges, LastId,)) #Schutz vor SQL-Injections
    self.conn.commit()

Insert data into database

dbLast.insertLastenheft("Projekt2") #fügt Datensatz hinzu dbLast.insertLastenheft("Projekt3") #fügt Datensatz hinzu dbLast.insertUnternehmen(2, "Firma XYZ", "Schulstraße 3", "Unterfirma XYZ", "071563483", "66668", "www.firma.com", "5 Millionen", "Gut", "1970", "IT", "Technology", '01010110', "2", 2) dbLast.insertSysAnforderung(2, "hello", "j", "j", "k", "j", "k", "m", "j", "kd", 2)