CODEWITHSUNDEEP
c#sql
Best Tshililo
Best Tshililo

Reputation: 15

How to set Primary Key on a View

I cannot Update my model since the view i have created does not have a primary key. the error message says "Does not have a primary key defined "

ALTER VIEW VwSupplyStatement ADD PRIMARY KEY PK_VwSupplyStatement DISABLE 
NOVALIDATE;
IF OBJECT_ID ('dbo.VwSupplyStatement') IS NOT NULL
DROP VIEW dbo.VwSupplyStatement
GO
CREATE  VIEW [dbo].[VwSupplyStatement]
AS
SELECT
    MAX(Objid) AS Objid,
    MAX(PaymentHeaderId) AS PaymentHeaderId, 
    MAX(PalletNo) AS PalletNo,
    MAX(SequenceNo) AS SequenceNo,  
    MAX(Season) AS Season,
    --MAX(OrganizationId) AS OrganizationId,
    MAX(AccountId) AS AccountId,
    MAX(ShipmentDocumentNumber) AS DocumentNo,
    MAX(AccountFullDescription) AS AccountDescription,
    MAX(ShipmentLoadName) AS Vessel,
    MAX(ShipmentRegionLId) AS TargetMarketId,
    MAX(ContainerNo) AS ContainerNo,
    MAX(QualityCertified) AS QualityCertified,
    MAX(IncoTerm) AS IncoTerm,
    MAX(BuyTerm) AS BuyTerm,
    MAX(TotalpaymentvsPO) AS TotalpaymentvsPO,
    MAX(TripNumber) AS Voyage,
    MAX(DepatureDate) AS DepatureDate,
    MAX(Cartons) AS Cartons,
    MAX(Advance1Currency) AS Advance1Currency,
    MAX(CASE WHEN InvoiceNo ='AD1' THEN 'AD1' END) AS ADvance1InvoiceNo,
    MAX(CASE WHEN InvoiceNo ='AD1' THEN Advanceinvoices END) AS Advance1invoices,
    MAX(CASE WHEN InvoiceNo ='AD1' THEN Amount END) AS AD1Amount,
    MAX(CASE WHEN InvoiceNo ='AD1' THEN AmountPaid END) AS Amount1Paid,
    MAX(CASE WHEN InvoiceNo ='AD1' THEN AmountDue END) AS Amount1Balance,
    ---------------------------------------------------------------------
    MAX(CASE WHEN InvoiceNo ='AD2' THEN 'AD2' END) AS ADvance2InvoiceNo,
    MAX(CASE WHEN InvoiceNo ='AD2' THEN Advanceinvoices END) AS Advance2invoices,
    MAX(CASE WHEN InvoiceNo ='AD2' THEN Amount END) AS AD2Amount,
    MAX(CASE WHEN InvoiceNo ='AD2' THEN AmountPaid END) AS Amount2Paid,
    MAX(CASE WHEN InvoiceNo ='AD2' THEN AmountDue END) AS Amount2Balance,
     ---------------------------------------------------------------------
    MAX(CASE WHEN InvoiceNo ='AD3' THEN 'AD3' END) AS ADvance3InvoiceNo,
    MAX(CASE WHEN InvoiceNo ='AD3' THEN Advanceinvoices END) AS Advance3invoices,
    MAX(CASE WHEN InvoiceNo ='AD3' THEN Amount END) AS AD3Amount,
    MAX(CASE WHEN InvoiceNo ='AD3' THEN AmountPaid END) AS Amount3Paid,
    MAX(CASE WHEN InvoiceNo ='AD3' THEN AmountDue END) AS Amount3Balance
    --MAX(NetPaymentDIP) AS TotalFnl
    --MAX(CreditAmount) AS CreditAmount
FROM dbo.VwSupplyStatementBase
GROUP BY ShipmentDocumentNumber

How do i enforce a primary key to this View ?

Is there a way to modify this sql code so when I update my model on visual studio it does not break ?

Upvotes: 0

Views: 221

Answers (1)

Anson Aricatt
Anson Aricatt

Reputation: 393

You cannot create a primary key on a view, you can create indexes on a view. For further info SQL server indexed views

For solve your issue

Force entity framework to use a column as a primary key, use ISNULL

SELECT
INULL(MAX(Objid),-1) AS Objid ,.... from FROM dbo.VwSupplyStatementBase
GROUP BY ShipmentDocumentNumber

Upvotes: 1

Related Questions