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?

Where your second and third `GROUP BY` supposed to use `T2` and `T3`? — Alexis Wilke, May 23 '22 at 23:19

score 47 · Accepted Answer · answered Jul 11 '12 at 17:55

47

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.

answered Jul 11 '12 at 17:55

Kevin Aenmey

13,259
5
46
45

3

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. – AliRıza Adıyahşi Jul 11 '12 at 17:58
14

"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...` – Ivan Dec 14 '12 at 10:18
8

@Ivan The following link may help for that situation: http://stackoverflow.com/a/2715299/1464699 – Kevin Aenmey Dec 14 '12 at 14:04
1

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. – Ivan Dec 14 '12 at 14:40
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. https://msdn.microsoft.com/en-us/library/vstudio/dd163156(v=vs.100).aspx – JJ_Coder4Hire Aug 15 '15 at 02:08
You can create a PK in a view if the view is set to SCHEMABINDING – MeTitus Nov 19 '15 at 13:02
3

@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.. – deroby Sep 22 '16 at 14:58

score 3 · Answer 2 · answered Mar 31 '17 at 03:50

3

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.

answered Mar 31 '17 at 03:50

DLallemant

31
4

1

I try for the same @DLallemant, but primary key of actual table is not working as primary key foe view table. – S.Yadav Aug 30 '17 at 10:34

score 3 · Answer 3 · answered Feb 11 '20 at 14:55

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

score 2 · Answer 4 · answered Jul 08 '20 at 18:33

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

score 1 · Answer 5 · edited May 23 '22 at 23:23

1

This worked for me..

SELECT ROW_NUMBER() over (...order by column_name_of your choice...)
    AS pri_key, ...the other columns of the view...

edited May 23 '22 at 23:23

Alexis Wilke

19,179
10
84
156

answered Mar 03 '20 at 13:45

JKMburu

37
6

Create view with primary key?

5 Answers5

Linked