Hi,
Occasionally it may be useful to have a primary / foreign key relationship between a view and table, where the view is the primary key referenced by a column in the table.
Oracle doesn’t let you do this quite as ‘completely’ as we’d probably like, but it at least let’s you put in the building blocks which may help the CBO and make your database design more concise or elegant in places.
Here is a quick example :
SQL> create view pk_view
(item_id, item_name,
constraint pk_test_view primary key (item_id) rely disable novalidate)
as
select 1, 'One' from dual
union
select 2, 'Two' from dual
union
select 3, 'Three' from dual;
View created.
SQL> create table fk_view (
item_id number references pk_view disable novalidate);
Table created.
SQL>
There, that’s it. Hope this helps someone as the create view syntax can be a little tricky.
