mercredi 26 août 2015

exclusive/disjoint inheritance in SQLite

I was wondering how to implement exclusive inheritance in SQlite. By doing simply

create table Class (id integer primary key);
create table Sub1(id integer primary key references Class(id));
create table Sub2(id integer primary key references Class(id));

I have simple inheritance which does not prevent a Class to be both Sub1 and Sub2. I am looking for a way to enforce that a Class cannot be both (and optionnally, enforce it to be at least one of them).

In theory this could be possible with checks, e.g. for Sub2, something like

create table Sub2(id integer primary key references Class(id)
  check(not exists(select 1 from Sub1 where Sub1.id = id limit 1)));

but this has the drawback that it would require maintenance as subclasses are added, and also that it is not accepted by SQLite (subqueries prohibited in CHECK constraints). This does not work when the check is at the table level either.

Aucun commentaire:

Enregistrer un commentaire