sqlite3 foreign key

Question

I have a database made in sqlite3 and have set foreign keys on the loan table to the student table and book table to stop duplicate loans:

using create table:

CREATE TABLE loan(
  SudentID INTEGER,
  ISBN INTEGER,
  out INTEGER,
  FOREIGN KEY(SudentID)
      REFERENCES student(SudentID)
      ON DELETE CASCADE
  FOREIGN KEY(ISBN)
      REFERENCES book(ISBN)
      ON DELETE CASCADE
)

CREATE TABLE student(
  SudentID INTEGER PRIMARY KEY,
  First TEXT,
  Last,
  Contact Text,
  Year INTEGER)

CREATE TABLE book(
  ISBN INTEGER PRIMARY KEY,
  Title TEXT,
  Price INTEGER,
  Qty INTEGER,
  Subject TEXT)

If i try to insert a duplicate records into the loan table the foreign key does not prevent it.

Pragma is switched on both in the code and in Firefox database settings.

The version is 2.6.0

My work around is to use Distinct to filter out duplicates but is there any way I can activate them as I use this database as a teaching tool. However the cascade delete does not work! Why?

JosefAssad · Accepted Answer · 2013-02-24T15:40:04.010

1

You want a composite primary key on the loan table.

CREATE TABLE loan(
  StudentID INTEGER,
  ISBN INTEGER,
  out INTEGER,
  FOREIGN KEY(SudentID)
      REFERENCES student(SudentID)
      ON DELETE CASCADE
  FOREIGN KEY(ISBN)
      REFERENCES book(ISBN)
      ON DELETE CASCADE
  PRIMARY KEY(StudentID, ISBN)
)

edited Feb 24 '13 at 15:40

answered Feb 24 '13 at 15:30

JosefAssad

4,018
28
37

This is what I would do normally but could not find the sql command in any of the books! – Timothy Lawman Feb 24 '13 at 16:51
1

I can verify this works perfectly in version 2.6.0 of Sqlite3 as it has been tested practically. – Timothy Lawman Feb 25 '13 at 02:48

score 0 · Answer 2 · edited Mar 05 '16 at 18:38

Foreign key support is only available in Sqlite version 3.6.19 and upwards. So the problem may be one of the following:

Sqlite version < 3.6.19. To whether your version supports foreign key or not try the following:

sqlite> PRAGMA foreign_keys;

If no data is return as opposed to 0 or 1, then your version does not support foreign keys.

Your version of sqlite3 was compiled with either of these defined

SQLITE_OMIT_FOREIGN_KEY

SQLITE_OMIT_TRIGGER

One thing though, foreign keys are not enforced in sqlite3. Find all this information from here

sqlite3 foreign key

2 Answers2