SQLite Forum

foreign key issue on deleate
Login
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?