Create view with primary key?

问题:

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?

回答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.

回答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.

回答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

回答4:

This worked for me..

select ROW_NUMBER() over (order by column_name_of your choice ) as pri_key, the other columns of the view

回答5:

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