2

While answering this question https://stackoverflow.com/questions/4668146/how-to-add-a-primary-key-on-a-oracle-view/8558346#8558346
A lot of questions popped in my mind.
1. Is it advisable to have primary key in a view.
2. Why anybody be intrested in having a primary key in a view, what are its advantages. I mean one cannot search a view by index.
3. Is it because of hibernate, but again i have used distinct keyword in a view declaration which means i cannot insert via view.


Here is my code for 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; 
3
  • According to the Oracle documentation: "Only logical constraints, that is, constraints that are declarative and not enforced by Oracle, can be defined on views." What exactly are you trying to accomplish? Dec 19, 2011 at 19:56
  • 1
    why not put the PK onthe table where it belongs?
    – HLGEM
    Dec 19, 2011 at 22:03
  • I asked a similar question here.
    – onedaywhen
    Dec 20, 2011 at 9:45

2 Answers 2

2

Your code creates a column with the name PRIMARY_KEY, but this is not the same as a primary key constraint. (It is not enough to be unique.) As Branko Dimitrijevic pointed out only logical constraints can be defined on views, and a primary key constraint will be enforced by Oracle.

On a materialized view where you store your data physically in the database a index may help you to find your data faster. A primary key constraint may be helpful if you want to use replication. But a materialized view is more of a table than a view.

I hope this helps to answer your questions.

1
  • How oracle would enforce primary key constraint if you don't specify it. However, in order to enforce primary key constraint one can always do such thing as: 'alter view VU_NAME add constraint vemp_pk primary key (PRIMARY_KEY) disable' .My question remained unanswered that why we can't have primary key in a view and what are its pros and cons. Dec 19, 2011 at 21:16
2

A relation by definition has no duplicate tuples. A relvar may have one or more candidate keys. The concept of PRIMARY KEY originates from the father of the relational model, E. F. Codd: in the early years, he assumed it would be advantageous for the DBMS to have knowledge of a candidate key considered by the designer to be 'primary' to confer on it special treatment. It was later realised that all candidate keys are 'equal' but too late to prevent PRIMARY KEY from being included in the SQL Standard and the Shackle of Compatibility means you can never recover from mistakes in language design.

Speaking of mistakes in language design, in SQL a table may have duplicate rows. As regards base tables most practitioners agree this is a bad thing and assert that:

every table should have a PRIMARY KEY

By this they actually mean:

every base table should have a key that does not involve nullable columns

Now, finally turning to the question at hand, should we extend this to

every table (base, virtual, derived, etc) should have a key that does not involve nullable columns

I think the answer to this is yes. In fact, I'd modify the assertion to:

every table (base, virtual or derived) should have a key and no table should involve nulls

If this rule was followed (among others e.g. no duplicate columns), SQL tables would be more in the spirit of the relational model, which can only be a good thing IMO.

Let me turn the question around: what is the use of a table (virtual or otherwise) with duplicate rows? If there is none, is there any reason why the system shouldn't remove duplicate rows?

3
  • what is the use of a table (virtual or otherwise) with duplicate rows? Reporting is one. You generate a resultset that have all info needed in a single (somewhat denormalised) presentation to the report tool get this job done easily. You can make that query remain on the database using a view and reports only need to include some filters to get the info needed. Dec 20, 2011 at 18:04
  • @FabricioAraujo: I don't think "somewhat denormalised" has any meaning: do you mean not first normal form (NFNF)? That begs the question, what is the use of a report with duplicate rows?
    – onedaywhen
    Dec 21, 2011 at 10:54
  • because that virtual table (select) is a intermediary result of a more complex process, so in that step of the process is easier to get the intermediate result with duplications (because have just the needed columns to exibition, and other columns would be needed to disambiguate) and finalize it on the client. Dec 22, 2011 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.