PostgreSQL - Check foreign key exists when doing a

问题:

Suppose I have the following data:

Table some_table:

some_table_id | value | other_table_id
--------------------------------------
1             | foo   | 1
2             | bar   | 2

Table other_table:

other_table_id | value
----------------------
1              | foo
2              | bar

Here, some_table has a foreign key to column other_table_id from other_table into the column of some name.

With the following query in PostgreSQL:

SELECT * 
FROM some_table 
WHERE other_table_id = 3;

As you see, 3 does not exists in other_table This query obviously will return 0 results.

Without doing a second query, is there a way to know if the foreign key that I am using as a filter effectively does not exist in the other_table?

Ideally as an error that later could be parsed (as it happends when doing an INSERT or an UPDATE with a wrong foreign key, for example).

回答1:

You can exploit a feature of PL/pgSQL to implement this very cheaply:

CREATE OR REPLACE FUNCTION f_select_from_some_tbl(int)
  RETURNS SETOF some_table AS
$func$
BEGIN
   RETURN QUERY
   SELECT * 
   FROM   some_table 
   WHERE  other_table_id = $1;

   IF NOT FOUND THEN
      RAISE WARNING 'Call with non-existing other_table_id >>%<<', $1;
   END IF;
END
$func$  LANGUAGE plpgsql;

A final RETURN; is optional in this case.

The WARNINGis only raised if your query doesn't return any rows. I am not raising an ERROR in the example, since this would roll back the whole transaction (but you can do that if it fits your needs).

We've added a code example to the manual with Postgres 9.3 to demonstrate this.

回答2:

If you perform an INSERT or UPDATE on some_table, specifying an other_table_id value that does not in fact exist in other_table, then you will get an error arising from violation of the foreign key constraint. SELECT queries are therefore your primary concern.

One way you could address the issue with SELECT queries would be to transform your queries to perform an outer join with other_table, like so:

SELECT st.* 
FROM
  other_table ot
  LEFT JOIN some_table st ON st.other_table_id = ot.other_table_id
WHERE st.other_table_id = 3;

That query will always return at least one row if any other_table row has other_table_id = 3. In that case, if there is no matching some_table row, then it will return exactly one row, with that row having all columns NULL (given that it selects only columns from some_table), even columns that are declared not null.

If you want such queries to raise an error then you'll probably need to write a custom function to assist, but it can be done. I'd probably implement it in PL/pgSQL, using that language's RAISE statement.