当前位置：文江博客话题详情

如何在Oracle视图上添加主键？

发布于 2024-10-11 07:26:25 字数 241 浏览 6 评论 0原文

可能的重复：
将主键添加到sql视图

我正在使用需要Oracle 视图中的主键。可以在Oracle视图中添加主键吗？如果是，怎么办？我无法通过谷歌搜索有关此的信息。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一抹微笑 2024-10-18 07:26:25

不幸的是，SQL 标准只允许对基表（而不是视图）进行 UNIQUE 和 PRIMARY KEY 约束。 Oracle 允许在物化视图上建立唯一索引，但一般不允许在视图上建立唯一索引。

回复收藏 0 原文

杀手六號 2024-10-18 07:26:25

我想到的唯一一件事是使用物化视图，然后在其上创建唯一索引：

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

虽然这可能很有用，但必须记住，物化视图与“普通”视图相反，占用空间一个表空间。当然，索引也需要空间。

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.

回复收藏 0 原文

再可℃爱ぅ一点好了 2024-10-18 07:26:25

您可以通过这种方式在视图中添加主键。

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;

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;

回复收藏 0 原文

~没有更多了~