CODEWITHSUNDEEP
sqliteforeign-keys
Mikhail Edoshin
Mikhail Edoshin

Reputation: 2669

Can a foreign key in SQLite point to a view?

Can I define a foreign key (in SQLite, 3.8.2) pointing to a view?

My situation is that: I have a list of items; each item has a specific type; each type has a list of slots (attributes):

create table "type" ("type id" integer primary key);
create table "slot" (
  "slot id" integer primary key, 
  "type id" integer not null
    references "type" ("type id") on update cascade on delete cascade);
create table "item" (
  "item id" integer primary key
  "type id" integer not null
    references "type" ("type id") on update cascade on delete cascade);

Now I want to write a change log: item A, slot B, value X:

create table "change" (
  "item id" integer not null
    references "item" ("item id") on update cascade on delete restrict,
  "slot id" integer not null
    references "slot" ("slot id") on update cascade on delete restrict,
  "data" none);

Clearly the slot has to be one of the slots of the item's type. I thought I can define a view like this:

create view "item slot" as
  select "item id", "slot id" from "item" join "slot" using ("type id");

and then define a foreign key constrain in "change":

...
foreign key ("item id", "slot id")
  references "item slot" ("item id", "slot id")
  on update cascade on delete cascade,
...

but this doesn't seem to work; when I insert a new value, it tells me that the foreign key doesn't match (although the view does indeed contain such an item and a slot).

Is it not possible? I see that their manual page doesn't mention views at all, but it doesn't say that pointing foreign keys to views is prohibited either. (If this is not possible I'll try to add a trigger.)

Upvotes: 4

Views: 1101

Answers (1)

CL.
CL.

Reputation: 180172

The documentation says that

the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.

Views cannot have constraints or indexes, so it is not possible to have a foreign key that references a view.

Upvotes: 10

Related Questions