Hi Tom,
I have some constants to be used all through my application and I don't want to give access to anybody. So I am wrapping them up in a Package. I am creating a view from those package constants.
CREATE OR REPLACE VIEW "Const_VIew" ("Cons_id", "Cons_name") AS
Select 1 id, 'Low' from dual
UNION ALL
Select 2 id, 'Medium' from dual
UNION ALL
Select 3 id, 'High' from dual
UNION ALL
Select 4 id, 'Very_High' from dualIn my application I am replacing these constants with package calls.
I want to have tables referencing this view and have referential integrity between this view and the tables.
CREATE TABLE "TEST_1"
( "TEST_ID" NUMBER NOT NULL ENABLE,
"TEST_NAME" VARCHAR2(4000),
"CONST_ID_REF" NUMBER NOT NULL ENABLE,
CONSTRAINT "TEST_1_PK" PRIMARY KEY ("TEST_ID") ENABLE
)I want to have foreign key relation between TEST_1 and Const_View. Is it possible? If not, what are my options?
Any help is greatly appreciated.
Thanks
Nimi
instead of a create view, use a create table - a lookup table.
The view provides nothing here - just use a real table. The view doesn't "hide" anything from anyone any more or less than a table would/could.
Integrity checks are triggered by insert/update/deletes - not create or replace views. Here, a create or replace view would have to drop and re-enable a constraint - and it doesn't do that.