1

I need to create a table having a field, which is a foreign key referencing to another query rather than existing table. E.g. the following statement is correct:

CREATE TABLE T1 (ID1 varchar(255) references Types)

but this one throws a syntax error:

CREATE TABLE T2 (ID2 varchar(255) references SELECT ID FROM BaseTypes UNION SELECT ID FROM Types)

I cannot figure out how I can achieve my goal. In the case it’s needed to introduce a temporary table, how can I force this table being updated each time when tables BaseTypes and Types are changed?

I am using Firebird DB and IBExpert management tool.

RedSoft
  • 148
  • 8
  • 1
    Okay. What is your question? Perhaps you should review how foreign keys are defined. There is no such thing as a "temporary table generated by a query" for a *referential integrity* constraint. – Gordon Linoff Jun 17 '20 at 19:59
  • This is my question. I see that it's impossible to write `references SELECT ...` but I need it. I need unite 2 tables into one in order it be referenced by a foreign key field. So I ask how correctly to do it. What is the workaround? – RedSoft Jun 18 '20 at 03:24
  • As a point of terminology, a subquery is not a temporary table. The term temporary table has a specific meaning in SQL. – Mark Rotteveel Jun 18 '20 at 08:34
  • SELECT is not table, neither temporary nor by any other name. It exists as merely a channel - uni-directional! - to pipe some data of non-defined (abstracted out) source to some client. And that is. It is not some lake, it is some facet from which water drops fall into sink. One drop after another. And maybe those drops are created at the moment of reading them and never existed before reading and never will exist after reading. You can not reference a fibonacci-generating function results as persistent keys, even if you misname a function a table. – Arioch 'The Jun 18 '20 at 08:35
  • 1
    `can I force this table being updated each time when tables BaseTypes and Types are changed?` - create a `TRIGGER`, one for each table (or maybe 3 per each table - insert/update/delete), read Firebird documentation about PSQL TRIGGERS – Arioch 'The Jun 18 '20 at 08:39
  • 1
    @Arioch'The In some ways, a subquery is a table, hence the term Common Table Expression (CTE). – Mark Rotteveel Jun 18 '20 at 09:03
  • @MarkRotteveel In some way anything is a table, just a matter of defining ad hoc way. And that abbreviation - quite confusing IMHO - i parse as "common expression of table-like kind" rather than "expression built of common tables" – Arioch 'The Jun 18 '20 at 11:39
  • @Arioch'The This is exactly what I thought to do, the only problem that it seems Firebird doesn't permit to define a foreign key reference to a GTT (global temporary table). So I had to introduce an additional table that I think is excess in this case. – RedSoft Jun 18 '20 at 15:22
  • 1
    @RedSoft of course, one and the same GTT has different content when seen from different connections and/or transactions. More so, any such content disappears completely when the transaction or connection closes. If you make an FK from some persistent table A to a GTT, then you do want the content of table A also disappear at the end of every transaction or at least every conneciton. Then just make it GTT too. – Arioch 'The Jun 18 '20 at 16:58

1 Answers1

2

A foreign key constraint (references) can only reference a table (or more specifically columns in the primary or unique key of a table). You can't use it to reference a select.

If you want to do that, you need to use a CHECK constraint, but that constraint would only be checked on insert and updates: it wouldn't prevent other changes (eg to the tables in your select) from making the constraint invalid while the data is at rest. This means that at insert time the value could meet the constraint, but the constraint could - unnoticed! - become invalid. You would only notice this when updating the row.

An example of the CHECK-constraint could be:

CREATE TABLE T2 (
  ID2 varchar(255) check (exists(
    SELECT ID FROM BaseTypes WHERE BaseTypes.ID = ID2
    UNION 
    SELECT ID FROM Types WHERE Types.ID = ID2))
)

For a working example, see this fiddle.

Alternatively, if your goal is to 'unite' two tables, define a 'super'-table that contains the primary keys of both tables, and reference that table from the foreign key constraint. You could populate and update (eg insert and delete) this table using triggers. Or you could use a single table, and replace the existing views with an updatable view (if this is possible depends on the exact data, eg IDs shouldn't overlap).

This is more complex, but would give you the benefit that the foreign key is also enforced 'at rest'.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197