Is it possible to have a single foreign key column referenced to two or more different tables?

Question

In Spatialite (3.0.1), having a normal (non-spatial) table, I would like to relate it to two different spatial tables, with different geometries types (multipolygons and polylines), using the same column as a foreign key, and constraint it to their primary keys.

The general idea would be to, while inserting a new record in the non-spatial table, one can choose to relate it to a line or a polygon from two distinct spatial tables.

Example:

CREATE TABLE sp1_polygons
  poly_id TEXT NOT NULL PRIMARY KEY;

SELECT AddGeometryColumn(
  'sp1_polygons', 'geometry',
  27493, 'MULTIPOLYGON', 'XY');

CREATE TABLE sp2_lines
  poly_id TEXT NOT NULL PRIMARY KEY;

SELECT AddGeometryColumn(
  'sp2_lines', 'geometry',
  27493, 'MULTILINESTRING', 'XY');

CREATE TABLE t1 (
  t1_id INTEGER NOT NULL PRIMARY KEY,
  geo_id TEXT NOT NULL,
  CONSTRAINT fk_sp1
    FOREIGN KEY (geo_id) 
    REFERENCES sp1 (sp1_id)),
  CONSTRAINT fk_sp2
    FOREIGN KEY (geo_id) -- Equal to before
    REFERENCES sp2 (sp2_id));

In my particular case, the non-spatial table represent management actions (harvesting, planting, cutting, fixing, etc...) in a farm, while the spatial tables represent homogeneus plots (polygons) to be managed (farmland, forest, natural vegetation), or homogeneus lines (water streams, natural fences, etc...).

Answer 1

AFAIK, there's no problem having two foreign key constraints, with one column referencing two different columns in two foreign tables. However, that means that every row in the table with the constraints must reference an existing entry in both of the other tables. So you will not be able to relate some of the rows of t1 to sp1_lines, and some to sp1_polygons. Each entry in t1 will need to have a geo_id which actually exists in both spatial tables.

It is possible, on the other hand, to create a trigger which checks that the new.geo_id existes in either the lines table OR the polygons table. Something like:

CREATE TRIGGER "test_geo_id" BEFORE INSERT ON "t1"
FOR EACH ROW BEGIN

SELECT RAISE(ROLLBACK, 'No geo_id in either lines or polygons')
WHERE (SELECT count(l.id), count(p.id) FROM
sp1_lines AS l, sp1_polygons AS p 
WHERE l.id = NEW.geo_id OR p.id=NEW.geo_id) < 1

END

But, I think you have another design flaw in the tables you described above: How will you insure that an id value in sp1_lines is not duplicated in sp1_polygons?