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...
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.
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.