Create view with primary key

Question

I create a view with following codes  SELECT     CONVERT NVARCHAR  YEAR okuma tarihi      T1  AS sno      YEAR okuma tarihi  AS Yillar      SUM toplam kullanim T1  AS TotalUsageValue   T1  AS UsageType FROM     TblSayacOkumalari GROUP BY     CONVERT NVARCHAR  YEAR okuma tarihi      T1   YEAR okuma tarihi   UNION ALL  SELECT     CONVERT NVARCHAR  YEAR okuma tarihi      T2  AS sno      YEAR okuma tarihi  AS Yillar      SUM toplam kullanim T2  AS TotalUsageValue   T2  AS UsageType FROM     TblSayacOkumalari GROUP BY     CONVERT NVARCHAR  YEAR okuma tarihi      T1   YEAR okuma tarihi   UNION ALL  SELECT     CONVERT NVARCHAR  YEAR okuma tarihi      T3  AS sno      YEAR okuma tarihi  AS Yillar      SUM toplam kullanim T3  AS TotalUsageValue   T3  AS UsageType FROM     TblSayacOkumalari GROUP BY     CONVERT NVARCHAR  YEAR okuma tarihi      T1   YEAR okuma tarihi    I want to define CONVERT nvarchar  YEAR okuma tarihi      T1  AS sno as a primary key is that possible  If is this possible how can I do

User · Answer

This worked for me     select ROW NUMBER   over  order by column name of your choice   as pri key  the other columns of the view

User · Answer

I got the error  quot The table view  dbo vMyView  does not have a primary key defined quot  after I created a view in SQL server query designer  I solved the problem by using ISNULL on a column to force entity framework to use it as a primary key  You might have to restart visual studio to get the warnings to go away  CREATE VIEW  dbo   vMyView  AS SELECT ISNULL Id  -1  AS IdPrimaryKey  Name FROM  dbo MyTable

User · Answer

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

User · Answer

A little late to this party - but this also works well   CREATE VIEW  ABC   View SomeDataUniqueKey  AS SELECT  CAST CONCAT CAST  ID  AS VARCHAR 4             CAST ROW NUMBER   OVER ORDER BY  ID  ASC  as VARCHAR 4               AS int  AS  UniqueId    ID  FROM SOME TABLE JOIN SOME OTHER TABLE GO   In my case the join resulted in  ID  - the primary key being repeated up to 5 times  associated different unique data  The nice trick with this is that the original ID can be determined from each UniqueID effectively  ID  RowNumber     11  12  13  14  21  22  23  24 etc  If you add RowNumber   and  ID  back into the view - you can easily determine your original key from the data  But - this is not something that should be committed to a table because I am fairly sure that the RowNumber   of a view will never be reliably the same as the underlying data alters  even with the OVER ORDER BY  ID  ASC  to try and help it   Example output   Select UniqueId  ID  ROWNR  Name from  REF   View Systems       UniqueId   ID  ROWNR   Name 11  1   1   Amazon A 12  1   2   Amazon B 13  1   3   Amazon C 14  1   4   Amazon D 15  1   5   Amazon E  Table1    ID   Name  1 Amazon  Table2    ID   Version  1 A 1 B 1 C 1 D 1 E  CREATE VIEW  REF   View Systems  AS     SELECT  CAST CONCAT CAST TABA  ID  AS VARCHAR 4             CAST ROW NUMBER   OVER ORDER BY TABA  ID  ASC  as VARCHAR 4               AS int  AS  UniqueId   TABA  ID   ROW NUMBER    OVER ORDER BY TABA  ID  ASC  AS ROWNR  TABA  Name    FROM   Ref   Table1  TABA LEFT JOIN  Ref   Table2  TABB ON TABA  ID    TABB  ID  GO

User · Answer

You may not be able to create a primary key  per say  but if your view is based on a table with a primary key and the key is included in the view  then the primary key will be reflected in the view also   Applications requiring a primary key may accept the view as it is the case with Lightswitch

[sql-server] Create view with primary key?

Examples related to sql-server

Examples related to tsql

Examples related to primary-key

Examples related to sql-view