adding primary key to sql view

Question

Reading that

http://stackoverflow.com/questions/894610/how-to-do-hibernate-mapping-for-table-or-view-without-a-primary-key

I am wondering how to add a primary key to my view as it is basically just a stored query...?

PS: oracle 10g

thx

Answer 1

We can add a disabled primary key constraint to a view. That is, the constraint does not fire if an insert or update is run against the view. The database expects integrity to be maintained through constraints on the underlying tables. So the constraint exists solely for the purposes of documentation.

SQL> create view emp_view as select * from emp
  2  /


View created.

SQL> alter view emp_view add constraint vemp_pk primary key (empno) disable
  2  /

View altered.

SQL> 

Caveat: I have never tried this with Hibernate, so I don't know whether it would work in your scenario. However, I do know sites which use Hibernate exclusively against a layer of views, so I presume it does. Please experiment with the syntax and report back.