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?

Solution 1

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.

Solution 2

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.

Solution 3

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

Solution 4

I got the error "The table/view 'dbo.vMyView' does not have a primary key defined" 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

View more solutions

Comments

[AliRıza Adıyahşi]

I create a model from view and I change sno manualy as a entity key. When I update the model I must change it as a entity key too. this is not functionally for me.

[Ivan]

"If you give us more information as to why you want a key on your view" - Entity Framework throws a warning that a view has no primary key: Error 6002: The table/view 'mydb.dbo.myview' does not have a primary key defined...

[Kevin Aenmey]

@Ivan The following link may help for that situation: stackoverflow.com/a/2715299/1464699

[Ivan]

By the way, @KevinAenmey, I would like to have foreign keys too. The view of mine is actually extending (appending some runtime-computed columns) another table which has foreign keys (which are parts of a composite primary key), so I'd like Entity Framework to understand that the view has got the same natural relations to other tables as the original table has. Looks natural and simple, but in real EF treats the view as just a set of columns with no clue about the fact that a particular cell value references another table.

[JJ_Coder4Hire]

Entity framework complains that there is no primary key but it's not an error, it's just letting you know that the model is going to be read only. which is fine because you can't write to a view anyway. These instructions tell you to edit the .edmx file with xml editor and remove the comments for the error and put in your keyfield. then recompile. msdn.microsoft.com/en-us/library/vstudio/…

[MeTitus]

You can create a PK in a view if the view is set to SCHEMABINDING

[deroby]

@Marco : to avoid confusion, NO you can't create a PK on a view, even when it is schema bound. However, you CAN put a unique clustered index on it if you want which is pretty much the same thing from a practical point of view, but its meta-data will be different (e.g. there is no PK object in sys.objects, the column is_primary_key in sys.indexes is 0, ... ) so some applications (like e.g. some ORM's) will not recognize it to figure out the key-columns for instance..

[S.Yadav]

I try for the same @DLallemant, but primary key of actual table is not working as primary key foe view table.