SQLite Forum

foreign key issue on deleate
Login

foreign key issue on deleate

(1) By elfenliedtopfan5 on 2021-05-28 03:31:21 [link] [source]

Hello all I have an issue when I try to remove a foreign key from db browser,

i get the following error,

Error deleting record: Foreign key mismatch - "Programs" referencing "ProgramsList" (DELETE FROM "main"."ProgramList" WHERE_rowid_IN('1');)

code i have for each table,

    public void createfulltableUser()
    {
        using (SQLiteCommand command = kiritoconnect.CreateCommand())
        {
            OpenConnection();


            string createfulltableuser = @"CREATE TABLE IF NOT EXISTS
                                           [User] (
                                           [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                           [UserName] TEXT NULL,
                                           [MashineID] TEXT)";

            command.CommandText = createfulltableuser;
            command.ExecuteNonQuery();



                CloseConnection();
        }
    }

    public void createfulltableprograms()
    {
        {
            using (SQLiteCommand command = kiritoconnect.CreateCommand())
            {
                OpenConnection();


                string createfulltableprograms = @"CREATE TABLE IF NOT EXISTS
                                           [Programs] (
                                           [ProgID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                           [UserName] TEXT NULL,
                                           [MashineID] TEXT,
                                           [Program] TEXT,
                                           [FilePath] TEXT,
                                           [Installed] BOOLEAN,
                                           FOREIGN KEY(`UserName`) REFERENCES `User`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
                                           FOREIGN KEY(`Program`) REFERENCES `ProgramList`(`Program`) ON DELETE CASCADE ON UPDATE CASCADE)";

                command.CommandText = createfulltableprograms;
                command.ExecuteNonQuery();


            }
            CloseConnection();
        }
    }

    public void createtableprogramlist()
    {

        using (SQLiteCommand command = kiritoconnect.CreateCommand())
        {
            OpenConnection();


            string createfulltableprograms = @"CREATE TABLE IF NOT EXISTS
                                           [ProgramList] (
                                           [ProgNumber] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                           [Program] TEXT)";

            command.CommandText = createfulltableprograms;
            command.ExecuteNonQuery();



            CloseConnection();
        }
    }

Creates all db and tables correctly but if i need to delete then it throws the mismatch error if anyone has any ideas what is causing this any help would be much appreciated

Kind Regards, elfenliedtopfan5

(2) By Keith Medcalf (kmedcalf) on 2021-05-28 05:09:02 in reply to 1 [link] [source]

Programs(UserName) is text but User(ID) is integer.

ProgramList(Program) is not unique.

Your schema is logically inconsistent.

(3) By elfenliedtopfan5 on 2021-05-29 00:27:00 in reply to 2 [link] [source]

i know this must sound like a stupid question but how does one make it unique?

and can't believe I made a stupid mistake as text field to integer lol have fixed that now.

just unsure what you mean by its not unique

(4) By Keith Medcalf (kmedcalf) on 2021-05-29 02:18:42 in reply to 3 [source]

The parent of a foreign key relationship must be unique. That means that you must declare the parent foreign-key as UNIQUE (or PRIMARY KEY) in the table definition or you must declare a UNIQUE index on the parent key (which is the same thing). Also, if you are cascading parent to child, you probably want an index on the child key as well.

CREATE TABLE IF NOT EXISTS User
(
   ID        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   UserName  TEXT NOT NULL,
   MashineID TEXT NOT NULL,
   UNIQUE (UserName, MashineID)
);

CREATE TABLE IF NOT EXISTS ProgramList
(
   ProgNumber INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   Program    TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS Programs
(
   ProgID     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   UserID     INTEGER,
   MashineID  TEXT,
   ProgNumber INTEGER, -- I assume this is what you meant
   FilePath   TEXT,
   Installed  BOOLEAN,
   FOREIGN KEY (UserID) REFERENCES User(ID) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(Program) REFERENCES ProgramList(ProgNumber) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX Programs_UserID on Programs(UserID);
CREATE INDEX Programs_ProgNumber on Programs(ProgNumber);

Why are the pseudokeys NOT NULL AUTOINCREMENT? Is there a reason for this or just because it sounds cool?

(5) By elfenliedtopfan5 on 2021-05-29 18:23:34 in reply to 4 [link] [source]

also unsure why i am getting sql error i am using visual studio and creating a sql db

here is a image https://i.imgur.com/47osCEk.png

unsure of why i am getting this error.