0

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.

EDIT

Found a similar question (and related answers) on SO here.

Community
  • 1
  • 1
P-Gn
  • 23,115
  • 9
  • 87
  • 104
  • Possible duplicate of [Maintaining subclass integrity in a relational database](http://stackoverflow.com/questions/332602/maintaining-subclass-integrity-in-a-relational-database) – P-Gn Mar 22 '16 at 12:02

1 Answers1

1

You could try to use triggers (http://www.sqlite.org/lang_createtrigger.html).

For instance, you could implement your needs by creating a trigger for the table Sub(n) that, when a record is inserted in Sub(n), checks that its primary key is not alread present in Class; if it is present than fails since this means that another record with the same primary key is already present in another Sub(k) table, otherwise it insert the (primary key of the) record in Class.

In this way, you can add tables corresponding to subclasses without modifying the code of the previous tables.

Renzo
  • 26,848
  • 5
  • 49
  • 61
  • That indeed works nicely when the base class is empty, as my example misleadingly underlies. When it is not empty -- most common case I would assume -- the extra column technique proposed in the link I added provides a working solution. – P-Gn Aug 28 '15 at 06:14
  • Yes, this is true, but I would like also point you to another possibility: you can define a subclass, let's say `Sub(0)`, that contains only the elements of the base class, so that you have the previous solution. And I can imagine other possibilities, as well. What I want principally point out in my answer is that, given the limitations of SQLite checks, the only way to go is through triggers. And in my answer I was giving just an example of using them, but the right solution depends on the precise aspects of the problem! – Renzo Aug 28 '15 at 08:11
  • I am not sure how your second solution would work out. If `Sub0` contains the elements of the base class, how would `Sub1` collect information from `Sub0` if they are exclusive? Or maybe are you saying all base class columns should be duplicated in the `Subn`s? I think that would defeat the purpose of this inheritance pattern. – P-Gn Aug 28 '15 at 09:13
  • You are correct, and I was wrong. But I was misleaded again by the fact that in the example case the Base table has only the key attribute :) – Renzo Aug 28 '15 at 12:09