如何在 Oracle 视图上添加主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4668146/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add a Primary Key on a Oracle view?
提问by André
Possible Duplicate:
adding primary key to sql view
可能的重复：
将主键添加到 sql 视图
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.
我正在使用需要 Oracle 视图中的主键的软件。是否可以在 Oracle 视图中添加主键？如果是，如何？我无法用谷歌搜索这方面的信息。
回答by nvogel
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.
不幸的是，SQL 标准只允许基表上的 UNIQUE 和 PRIMARY KEY 约束，而不是视图。Oracle 允许在物化视图上使用唯一索引，但通常不允许在视图上使用。
回答by René Nyffenegger
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.
虽然这可能很有用，但必须记住，与“普通”视图相反，物化视图占用表空间中的空间。当然，索引也需要空间。
回答by Femme Fatale
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;
