SQLite and Foreign Keys

[dnparsons]

According to the SQLite site, it 'parses' FOREIGN KEY constraints, but doesn't 'enforce' them. This suggests 3 questions for any SQLite gurus out there:

- What syntax can be used in an SQL DDL script that would include FOREIGN KEYs in a form that SQLite doesn't choke on?

- Is this stored in their schema somewhere?

- If so, does MyMeta parse it and report it in the Browser?

BTW, I wrote up my notes on SQLite this evening. I can post them if anyone is interested. It includes some info on the .NET Provider that I struggled with in my Default Settings for SQLite3.

[justin.greenwood]

Here is the language reference:
http://sqlite.org/lang.html
Omitted Functionality:
http://sqlite.org/omitted.html

The docs say it supports SQL92 syntax:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql2bnf.aug92.txt
Example: (or something like this)

Code: Select all

create table foo (
  id INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE bar (
  id INTEGER NOT NULL PRIMARY KEY,
  foo_id INTEGER NOT NULL
         CONSTRAINT fk_foo_id REFERENCES a(id) ON DELETE CASCADE
);


[dnparsons]

Thanks Justin,

I had seen the 3 documents you cited, but the example was worth 2,500 lines of BNF depth-first tree traversal.

BTW, SQLite will accept either of the following:

Code: Select all

   [DepartmentID] INTEGER NOT NULL
      CONSTRAINT FK_Employees_Departments
      REFERENCES Departments(DepartmentID)


Code: Select all

   [DepartmentID] INTEGER NOT NULL
      CONSTRAINT [FK_Employees_Departments]
      REFERENCES [Departments](DepartmentID)


But, I don't think the Finisar .NET Provider likes the second. When I browse the schema in MyMeta, the first reports IsInForeignKey as True. The second, says False.