SQLite table with two FKs, but only one at a time

Question

I've been struggling with a problem for a little bit now. Help would be much appreciated.

Here it is:

I have two data structures, ObjClass and Obj, where Obj is an specialization of ObjClass. An Obj may have multiple "Slots", each with its own type. The total Slots an Obj has is the Union of his own Slots with the Slots of his ObjClass. Here's the simplified schema(bear with me if it has syntax errors):

create table ObjClass(
    ObjClassId integer not null primary key
)

create table Obj(
    ObjId integer not null primary key,
    ParentClass integer,

    foreign key(ParentClass) references ObjClass(ObjClassId)
)

create table Slot(
    SlotId integer not null primary key,
    SlotType integer
)

What I'm asking is: the Slot table has a 1-n relationship with ObjClass OR Obj, but never both. How do I enforce this on SQLite?

P.S.: I can guess this problem is probably caused by bad database design. Any thoughts on how to achieve a similar structure with a better design are also appreciated.

Thanks!

possible duplicate of [What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys?](http://stackoverflow.com/questions/13311188/what-is-the-best-design-for-a-database-table-that-can-be-owned-by-two-different) — philipxy, Jul 19 '14 at 21:55
1st comment: This question is SQLite-specific, and none of the answers there take this into account(i.e.:They're not applicable, given the technology that is used here) 2nd comment: May be, but that doesn't answer the question. — Not a real meerkat, Jul 19 '14 at 22:11
1st. What makes you think that? Slot=setting. 2nd. Just thought you might like to know. Why else? — philipxy, Jul 19 '14 at 22:24
1st: An error on my part. When I didn't find the documentation on check constraints on SQLite, I assumed it didn't support them. Now I know it does, and it worked like a charm. Thanks. 2nd: Guess I was just being rude. Sorry. — Not a real meerkat, Jul 19 '14 at 22:44

Rahul · Accepted Answer · 2014-07-19T14:29:15.193

0

Well you can have two FK both pointing to ObjClass and Obj like

create table Slot(
    SlotId integer not null primary key,
    SlotType integer,
    FKColumn integer,
    foreign key(SlotType) references ObjClass(ObjClassId),
    foreign key(FKColumn) references Obj(ObjId)
)

EDIT:

Convert the relationship with Obj/ObjClass to a separate intermediary table. Create a intermediary table having reference to all 3 tables like below. That way, you will have association with ObjClass/Obj per each slot.

create table SlorforeachObjandObjClass(
    SlotId integer,
    ObjClassId integer,
    ObjId integer, 
    foreign key(ObjClassId) references ObjClass(ObjClassId),
    foreign key(ObjId) references Obj(ObjId),
    foreign key(SlotId) references Slot(SlotId)
)

edited Jul 19 '14 at 14:29

answered Jul 19 '14 at 00:36

Rahul

76,197
13
71
125

But then there would be no enforcement of the uniqueness of the reference. What I'm asking is, if Slot points to Obj, any FK to ObjClass should be NULL. BTW, the SlotType column is not a reference, it is just an identifier for the slot. I didn't put any FKs there so it would not influence the answers. The relationship is more like "ObjClass/Obj has a Slot" and not "Slot is an ObjClass/Obj". – Not a real meerkat Jul 19 '14 at 13:54
Sorry, confirmed the comment before finishing. Edited. – Not a real meerkat Jul 19 '14 at 13:59
I did. Quote: "What I'm asking is: the Slot table has a 1-n relationship with ObjClass OR Obj, but **never both**. How do I enforce this on SQLite?". The edit also doesn't help, since(I quote again) "An Obj may have **multiple "Slots"**, each with its own type". BTW, thanks for your help, I know I'm not being very easy to deal with. – Not a real meerkat Jul 19 '14 at 14:16
What I'm getting from this is: There's simply no way to enforce that one FK must be NULL if the other has a value(that is, to not accept a record with values on both FKs)? Do I have to force such constraint in the application code? – Not a real meerkat Jul 19 '14 at 14:39
Now I clearly understood. Yes, that have to be enforced either through app code or you can use a before insert trigger as well (if SQLite supports one; which I am not sure off). – Rahul Jul 19 '14 at 14:57

SQLite table with two FKs, but only one at a time

1 Answers1