postgresql 如何将主键添加到视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11667508/
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 primary key to View?
提问by Kliver Max
I have a view and want to make one attribute a primary key.
我有一个视图并希望将一个属性设为主键。
CREATE VIEW filedata_view
AS SELECT num PRIMARY KEY, id, ST_TRANSFORM(the_geom,900913) AS the_geom
FROM filedata
But get a error
但得到一个错误
ERROR: syntax error at or near "PRIMARY"
LINE 2: AS SELECT num PRIMARY KEY, id, ST_TRANSFORM(the_geom,900913)...
Use postgesSQL 8.4.
使用 postgesSQL 8.4。
How to do this?
这个怎么做？
回答by hmmftg
Views in Postgresql can't have primary keys.
Postgresql 中的视图不能有主键。
you are basically on wrong way creating constraint on a View, constraints should be created on tables, but some DBMSes do support adding constraints on Views like oracle with this syntax:
您在视图上创建约束的方式基本上是错误的，应该在表上创建约束，但是一些 DBMS 确实支持使用以下语法在 oracle 等视图上添加约束：
ALTER VIEW VIEW_NAME ADD PRIMARY KEY PK_VIEW_NAME DISABLE NOVALIDATE;
You can specify only unique, primary key, and foreign key constraints on views, and they are supported only in DISABLE NOVALIDATE mode.
您只能在视图上指定唯一、主键和外键约束，并且它们仅在 DISABLE NOVALIDATE 模式下受支持。
so they only support it for compatibility, if you want to have a primary key to stop insertion of duplicate data in column numin filedatatable, you should do it by altering the filedatatable and add a primary key on it or by creating your table with primary key on column numfrom the start.
所以他们只支持它的兼容性，如果你想有一个主键列的重复数据光阑插入NUM在FILEDATA表，你应该改变这样做FILEDATA表，并在其上或在创建表添加主键从一开始就在列num上使用主键。
回答by Colin 't Hart
Postgresql doesn't support constraints on views. Other DBMSes (eg Oracle) do support this but Postgresql doesn't.
Postgresql 不支持对视图的约束。其他 DBMS（例如 Oracle）确实支持这一点，但 Postgresql 不支持。
