CODEWITHSUNDEEP
sqldatabaseoracle-database
sbrbot
sbrbot

Reputation: 6469

Foreign key with distinguisher

I have one table STATUSES containing statuses for more tables:

TABLE_NAME | STATUS_ID | STATUS_NAME
===========+===========+============
TAB_1      |         1 | New
TAB_1      |         2 | Started
TAB_1      |         3 | Complete
TAB_2      |         1 | Empty
TAB_2      |         2 | Full

Table STATUSES has two column primary key (table_name,status_id).

Then I have the table TAB_1:

ID | STATUS_ID | TAB_VALUE
===+===========+==========
 1 |         1 | Value1

When I want to retrieve table values with status I use the following SQL query:

SELECT id,tab_value,status_name
FROM tab_1 t
JOIN statuses s ON (s.status_id=t.status_id AND s.table_name='TAB_1')

Now I'd like to create a foreign key from TAB_1 to STATUSES but STATUS_ID should be distinguished with table name!

I tried sth like this:

ALTER TABLE tab_1
ADD CONSTRAINT tab_1_status_fk FOREIGN KEY ('TAB_1',status_id)
REFERENCES statuses (table_name,status_id)
ENABLE;

Of course, this does not work (otherwise I wouldn't be here).

Is it possible to create such a foreign key with 'distinguisher'?

Upvotes: 0

Views: 48

Answers (1)

kfinity
kfinity

Reputation: 9091

I think I'd add the table name as a virtual column with a constant value, e.g.

alter table tab_1 add (table_name as ('TAB_1'));

And then create the fk using that

ALTER TABLE tab_1
ADD CONSTRAINT tab_1_status_fk FOREIGN KEY (table_name,status_id)
REFERENCES statuses (table_name,status_id)
ENABLE;

Edit: If you're on 12c or up, you can make the virtual column hidden; I think it'll still work with a FK...

alter table tab_1 add (table_name invisible as ('TAB_1'));

Upvotes: 2

Related Questions