2

Possible Duplicate:
adding primary key to sql view

I'm working with a software that requires a primary key in a Oracle view. There is possible to add a Primary key in a Oracle view? If yes, how? I can't google information about this.

Community
  • 1
  • 1
André
  • 24,706
  • 43
  • 121
  • 178
  • 1
    That doesn't make sense. A View doesn't contain data, it has no indexes or keys. – skaffman Jan 12 '11 at 11:20
  • Materialized views can have a primary key, but I don't know if that's what you want to use? – Klaus Byskov Pedersen Jan 12 '11 at 11:23
  • 1
    As per UNNI's comment, it is possible to add a disabled primary key (or other kind of) constraint to a view. It can be done to provide information to the optimiser. See http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg05itg.htm and search for "constraints on views". – Tony Andrews Jan 12 '11 at 12:53
  • you can try this http://stackoverflow.com/questions/2041308/adding-primary-key-to-sql-view – UVM Jan 12 '11 at 11:27

3 Answers3

3

The SQL standard unfortunately only permits UNIQUE and PRIMARY KEY constraints on base tables, not views. Oracle permits unique indexes on materialized views but not on views generally.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • 5
    It allows them, but only in a disabled state. They provide information to the optimiser but cannot be enforced. – Tony Andrews Jan 12 '11 at 12:55
  • Exactly what Tony said. And some tools that do object-relational mapping want those disabled primary and foreign keys to exist for views, or they generate horrid execution plans. We had that experience with one of the versions of Hibernate, don't remember which one. – Jim Hudson Jan 12 '11 at 15:58
2

The only thing that comes in my mind is using a materialized view and then create a unique index on it:

drop materialized view tq84_mat_view;
drop table tq84_table;
create table tq84_table (
  a number,
  b number
);

create materialized view tq84_mat_view 
refresh on commit as
select 
  a,
  sum(b) sum_b
from 
  tq84_table
group by
  a;

create unique index tq84_mat_view_uix on tq84_mat_view (sum_b);

insert into tq84_table values (1, 1);
insert into tq84_table values (2, 2);
insert into tq84_table values (1, 4);

commit;

insert into tq84_table values (2, 3);

commit;
--> ORA-12008: error in materialized view refresh path
--> ORA-00001: unique constraint (SPEZMDBA.TQ84_MAT_VIEW_UIX) violated

While this might be useful, it must be kept in mind that the materialized view, as opposed to a "normal" view occupies space in a tablespace. And of course, the index needs space, too.

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
-5

This is the way by which you can add a primary key in your view.

CREATE OR REPLACE FORCE VIEW VU_NAME
        (
          PRIMARY_KEY, NAME_ID, ADDRESS_ID
         )
        AS 
        SELECT DISTINCT ROWNUM AS PRIMARY_KEY,
            NAME.ID UNIT_ID,
            ADDRESS_ID
        from table1;
Femme Fatale
  • 870
  • 7
  • 27
  • 56
  • 1
    When the OP says "Primary Key", I suspect they mean "Primary Key *Constraint*". Also, ROWNUM is not guaranteed to be the same every time the query is run, so it is not suitable as a surrogate Primary Key; even if you make the assumption that the table will never receive inserts or deletes. – Jeffrey Kemp Dec 20 '11 at 03:44
  • @Jeffrey Kemp I totally agree with you that ROWNUM is not guaranteed but one can always make Primary constraint by using this command 'alter view VU_NAME add constraint vemp_pk primary key (PRIMARY_KEY) disable'. Still my question remained unanswered that **why does he wants to have a primary key in his view** – Femme Fatale Dec 20 '11 at 05:17