System.Data.SQLite

 Hello,

I'm new with SqLite but everything was quite easy to understand except foreign keys. Here is my problem, maybe someone could explain it...

I've created two tables:

  Create table Table1 (
                ID_pk Text PRIMARY KEY,
                CHECK(ID_pk like 'P%'));

  Create table Table2 (
                ID_pk Text PRIMARY KEY,
                CHECK(ID_pk like 'M%'));

I would like to create the third table which would have references to foreign keys of these first tables. However, I wanted that fk references would depend on the first letter. Smth like that:

Create table Table3 (
                ID Text PRIMARY KEY,
                ID_fk Text,
                CASE
                      WHEN (ID_fk like P%)
                THEN
                      (FOREIGN KEY (ID_fk) REFERENCES Table1 (ID_pk)),
                      WHEN (ID_fk like M%)
                 THEN
                      (FOREIGN KEY (ID_fk) REFERENCES Table2 (ID_pk)),
                 ELSE RAISE(ABORT)
                END);

However it doesnt work.. Is it possible to do this? If so, should I use CASE (which means in this case I'm making some errors) or it is possible to solve this problem just with triggers?

Hope somebody could help.. I would really appreciate it...

I don't think any SQL database supports a construct like that natively.  On systems with triggers, you could implement the constraint with triggers, but that gets unpleasant to maintain very fast.

My solution to scenarios like this is to have two columns in Table3 (call them ID_fk1 and ID_fk2 for now), and a check constraint that says "(ID_fk1 is null) != (ID_fk2 is null)".  That slightly odd construct basically means that one of the two columns must be null and the other not null. This also makes queries that do joins between the tables easier to write and more efficient for the database to execute.