Adding Primary Key to SQL View

Create view with primary key?

You cannot create a primary key on a view. In SQL Server you can create an index on a view but that is different to creating a primary key.

If you give us more information as to why you want a key on your view, perhaps we can help with that.

Create A View, Select a Column As Primary Key

You cannot create primary keys on the view itself.

To get around the no primary key issue, you can create a unique column that the entity framework will pick up and use.

ISNULL(CAST((row_number() OVER (ORDER BY <columnName>)) AS int), 0) AS ID

So:

 SELECT  ISNULL(CAST((row_number() OVER (ORDER BY uniqueid)) AS int), 0) AS ID,  dstchannel,billsec,disposition,accountcode, uniqueid,cnam, callRateImport.Description, callRateImport.Code, callRateImport.Day FROM callLogImport
 LEFT JOIN callRateImport ON ....

adding primary key to sql view

We can add a disabled primary key constraint to a view. That is, the constraint does not fire if an insert or update is run against the view. The database expects integrity to be maintained through constraints on the underlying tables. So the constraint exists solely for the purposes of documentation.

SQL> create view emp_view as select * from emp
  2  /

View created.

SQL> alter view emp_view add constraint vemp_pk primary key (empno) disable
  2  /

View altered.

SQL> 

Caveat: I have never tried this with Hibernate, so I don't know whether it would work in your scenario. However, I do know sites which use Hibernate exclusively against a layer of views, so I presume it does. Please experiment with the syntax and report back.

How to add primary key to View?

Views in Postgresql can't have primary keys.

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:

ALTER VIEW VIEW_NAME ADD PRIMARY KEY PK_VIEW_NAME DISABLE NOVALIDATE;

Oracle Doc For Constraints

You can specify only unique, primary key, and foreign key constraints on views, and they are supported only in DISABLE NOVALIDATE mode.

so they only support it for compatibility, if you want to have a primary key to stop insertion of duplicate data in column num in filedata table, you should do it by altering the filedata table and add a primary key on it or by creating your table with primary key on column num from the start.

How to add a primary key to a View - Oracle

Building on Ivan's comment, you could build your view like this:

SELECT rownum, *
  FROM (your_current_view_query)

Setting primary key for SQL Server view linked to Access front end

CREATE INDEX <YourIndexName> ON <YourTableName>(<YourColumnName>) WITH PRIMARY

• <YourIndexName> = Any name you want, this will be used by Access

• <YourTableName> = The name of your table in Access

• <YourColumnName> = The name of the column in the table

Is it possible to set a primary key to a view?

You can create a clustered index on a SQL Server view - it's called an indexed view.

See Improving Performance with SQL Server 2008 Indexed Views for details.

How do you add a primary key to a sql view? - Or alternate way to link views to LINQ-2-Entities

After you create the view using schemabinding you can add a primary key to it:

CREATE VIEW Colors WITH SCHEMABINDING
AS SELECT Color='yellow', ColorCount=100
GO
CREATE UNIQUE CLUSTERED INDEX PK_Colors ON Colors (Color)

---

Related Topics