check key if exists in other table without fk constraint

Question

Lets say I have:

table a (id_a int);
table b (id_b int, id_a int);

I want to check if b.id_a exists in "a" before a new row in "b" will be inserted. Normally I would just add a fk constraint on b.id_a. The problem is that I'm not allowed to add a pk or uq constraint to "a.id_a" to reference the fk on b.id_a.

Would be nice if somebody has a solution for me.

Edit: Why not fk constraint - My model consists of an ESRI Arc SDE with spatial tables and simple postgres tables. Some pg tables reference to an sde uq identifier column. By default the SDE adds no pk to a spatial table in postgres (in sql server it does dont ask me why). However if a pk would be added by default by the SDE it wont help me at all, because i would have to change it to an other uq column, because the default pk column keys could change in the future (=WTF). ATM i just added or changed the pk of the SDE table to my uq identifier column, but as i had errors at first when restoring the db (now it works for the moment), ESRI support told me that my change or add of the pk column can cause these errors or maybe cause some in the future (because SDE wont know this pk). In my opinion it the pk should not matter (because SDE wont know and also wont use it) that's why i added it atm and it seems to work. But for the future, i want to be on the safe side and so i have to find an other possibility.

What i want: It would be nice to have the same behaviour like with a fk constraint - but i guess a similar behaviour wont be possible because that's what fks made for.

I have tried to write a before insert trigger but i think its the wrong approach as it seems that i always have to return the new.row and i don't want something to be inserted when the check for the value to be inserted don't exists.

I also could simply use no relationship or check on the fk but this in my opinion would be a high risk for having wrong values in the tbl.

Thanks for helping me...

Possible Solution:

I have tried to add a fk constraint which references on a column with uq index and it works (thx to ypercube). I have taken a look at the doc, but i cant find something about adding a fk constraint referencing on a column with just a uq index (no pk or uq constraint). At least i found a possible explanation in a tutorial which says

A unique constraint is actually implemented as a unique index in PostgreSQL, just as it is in many databases.

but also

This index wouldn’t be a good candidate for a foreign key, but it does illustrate how you can create a unique index that you can reference with a foreign key.

I just don't really get it why it shouldn't be a "good candidate" for a fk constraint, when either using a uq constraint or uq index on that column grants you unique values.

from docs pg uq constraint:

Unique constraints ensure that the data contained in a column or a group of columns is unique with respect to all the rows in the table.

from docs pg uq index:

Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data which would result in duplicate entries will generate an error.

Answer 1

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

---

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.

Answer 2

The problem is that i'm not allowed to add a pk or uq constraint to "a.id_a" to reference the fk on b.id_a.

If that column is intended to be unique then you have a management problem rather than a technical one. Any technical solution will be sub-optimal because you aren't allowed to use the methods that are designed to be optimal for this purpose.

You could handle it in your business logic layer but that is risky: the first bit of code that doesn't perform the check, doesn't do it properly (does it all make sure the right isolations level is set?), or has some other bug that stops the check working, is the first bit of code that is potentially corrupting that data or causing deadlocks which probably result in functionality errors to your users.

The other option is to use triggers on each table to make the relevant checks and raise errors as needed, but if you are not able to add a unique constraint then you are unlikely to be able to add triggers. If you are able to add a trigger, it would be gloriously inefficient without an index on that column and if you are not permitted to add a unique constraint (due to space or writer speed issues) then you won't be able to add an index either. This is still handling it in your own business logic rather than letting the built-in database constructs do it all or you (reinventing the wheel), but at least it means that you only have to implement the login in one place and not every place that ever adds/updates/deletes rows in the affected tables.

If the values in the column are not expected to be unique then the picture changes somewhat - you can't declare the column unique the primary key, rather than simply not being allowed to, so can't use a foreign key constraint on the related table. In that case the trigger option may be useful, though you will want a non-unique index covering the column for the triggers to be at all efficient.

Of course if the values are not unique then there may be a modelling problem - do you instead need to use a compound key/index and link the fk relationship(s) to that?

Answer 3

You need to create a function and then check result of this funcion.

Ex.

CREATE FUNCTION fn_region_en_region_pais(_region varchar(50))
RETURNS BIGINT AS
$$
   SELECT count(*) FROM region_pais WHERE region = _region 
$$ LANGUAGE SQL IMMUTABLE;

ALTER TABLE indice_arm_global 
  ADD CONSTRAINT chk_region_en_region_pais 
  CHECK(fn_region_en_region_pais(region_pais) > 0);