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
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?