sqldeveloper check in foreign key table attribute

Question

I have two tables:

BIRACI(ID, broj_biraca)
PRAVNI_AKT_PREDLOG_BIRACA(Pravni_akt_id, ID_biraca(FK from BIRACI)

When I want to insert into table PRAVNI_AKT_PREDLOG_BIRACA I need some rule to check if inserted ID_biraca has its attribute in table BIRACI with the condition broj_biraca > 15000.

I need something to show error like "Insertion not possible, BIRACI with that ID isnt having 15000 or more in broj_biraca"... Is this possible?

A constraint won't help you here, but a pre-insert trigger could perform the validation. — The Impaler, Apr 27 '22 at 21:17
Please tag you DBMS. SQL Developer is an IDE that can connect to different DBMS — astentx, Apr 27 '22 at 21:18
It may be possible with ref constraint to the calculated column `case when broj_biraca > 15000 then id end` — astentx, Apr 27 '22 at 21:21
how do i do that @astentx "It may be possible with ref constraint to the calculated column case when broj_biraca > 15000 then id end" — Ракићевић Димитрије, Apr 27 '22 at 21:24
I presume you connect to the Oracle database, but... Try `select * from v$version` or `select version()` to obtain the information about your DBMS and add it's tag instead of sql-developer tag by [editing your question](https://stackoverflow.com/posts/72035360/edit) — astentx, Apr 27 '22 at 21:28
[Oracle SQL Developer is a free **graphical tool** that enhances productivity and simplifies database development tasks.](https://docs.oracle.com/en/database/oracle/sql-developer/index.html) It doesn't manage databases in the way DBMS do, it just allows you to access the database run by some DBMS — astentx, Apr 27 '22 at 21:46

score 0 · Answer 1 · answered Apr 28 '22 at 12:04

I'm not an Oracle expert but I've searched a bit and it seems that Oracle doesn't support check constraints that reference another table.

2 potential workarounds:

According to https://stackoverflow.com/a/3880853/6996150 it could work via a user defined function (however this seems not the case according to Oracle's documentation; cf. the following approach).
According to https://stackoverflow.com/a/41467321/6996150 it could be done via virtual columns.

Hopefully you can make it work!

sqldeveloper check in foreign key table attribute

1 Answers1