Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

While answering this question How to add a Primary Key on a Oracle view?
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; 
share|improve this question
    
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? –  Branko Dimitrijevic Dec 19 '11 at 19:56
1  
why not put the PK onthe table where it belongs? –  HLGEM Dec 19 '11 at 22:03
    
I asked a similar question here. –  onedaywhen Dec 20 '11 at 9:45
add comment

migrated from stackoverflow.com Dec 20 '11 at 5:07

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
    
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. –  Namelus Dec 19 '11 at 21:16
add comment

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?

share|improve this answer
    
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. –  Fabricio Araujo Dec 20 '11 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 '11 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. –  Fabricio Araujo Dec 22 '11 at 19:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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