locked
How can I set up a Primary Key for a database View? RRS feed

  • Question

  • I had created a View.  Now with a VS 2008 ASP.NET 3.5 website, I need to insert some
    of the selected data on a FormView from this View to another table.  Without a PK, VS
    cannot generate the Insert Button.  How can I set up a PK for a View?
     
    Jeffrey
    Thursday, July 28, 2011 4:10 PM

Answers

  • Jeffrey

    Are you talking about indexed view? 

    create table T (

      i int,

      filler char(1000) default 'abc'

    )

    go

     

    create view T_count with schemabinding as

    select

      cast(i as bit) as val,

      count_big(*) T_count

    from dbo.T group by cast(i as bit)

    go

     

    create unique clustered index T_count_uci on T_count(val)

    go

    insert into T(i)

    select OrderID

    from Northwind..[Order Details]

    go

    set statistics io on

    select count(*) from T

    go

    select sum(T_count) from T_count with (noexpand)

    go

    set statistics io off

    -- uses an efficient query plan on the materialized view

     

    go

    drop view T_count

    drop table T


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 1, 2011 5:06 AM
  • I can't answer for ASP. But if your question is are those actions available on a VIEW, the answer is yes if it is UPDATEable. See the links my earlier reply or the example Uri has shown.

    Monday, August 1, 2011 11:28 AM
    Answerer
  • Thursday, July 28, 2011 4:33 PM
  • Make your VIEW UPDATEable.

    The VIEW itself cannot have a PK, but can have a regular INDEX to improve performance in some cases.


    Friday, July 29, 2011 11:44 AM
    Answerer

All replies

  • Views aren't tables. They don't have primary keys and you can't insert data into them. They are simply stored queries that reference physical tables. It's the underlying tables that would have primary keys and it's these same underlying table that you'd need to insert new data into.
    Jason Long
    • Proposed as answer by pvdg42 Thursday, July 28, 2011 4:27 PM
    • Unproposed as answer by Brian TkatchEditor Friday, July 29, 2011 11:39 AM
    Thursday, July 28, 2011 4:24 PM
  • Thursday, July 28, 2011 4:33 PM
  • Thanks.  I know we can not insert data into View.  All I need is an Insert button to insert the selected
    data from the View into another table.
    Jeffrey
    Thursday, July 28, 2011 4:35 PM
  • That is not correct. You can INSERT INTO a VIEW.
    Friday, July 29, 2011 11:39 AM
    Answerer
  • Make your VIEW UPDATEable.

    The VIEW itself cannot have a PK, but can have a regular INDEX to improve performance in some cases.


    Friday, July 29, 2011 11:44 AM
    Answerer
  • Brain,

    My main question is can I have the Insert, Delete, Edit buttons when I
    right-click my FormView control, after I set up an INDEX for the View
    SqlDataSource?  

     


    Jeffrey
    Saturday, July 30, 2011 12:50 AM
  • From
    http://forums.asp.net/t/1017684.aspx

    use northwind
    go
    create view v_test as
    select o.OrderID, C.ContactName,newid() as ColID
    from Orders o join Customers c
    on o.CustomerID=C.CustomerID

    ---------------------------------------------------------------

    newid() will be defined as ColID, which is one of the columns from the database, which we want
    to define as a PK, right?  Then in Visual Studio, we will see newid and pick it up as the
    DataKeyName, right?


    Jeffrey
    Monday, August 1, 2011 3:15 AM
  • Jeffrey

    Are you talking about indexed view? 

    create table T (

      i int,

      filler char(1000) default 'abc'

    )

    go

     

    create view T_count with schemabinding as

    select

      cast(i as bit) as val,

      count_big(*) T_count

    from dbo.T group by cast(i as bit)

    go

     

    create unique clustered index T_count_uci on T_count(val)

    go

    insert into T(i)

    select OrderID

    from Northwind..[Order Details]

    go

    set statistics io on

    select count(*) from T

    go

    select sum(T_count) from T_count with (noexpand)

    go

    set statistics io off

    -- uses an efficient query plan on the materialized view

     

    go

    drop view T_count

    drop table T


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, August 1, 2011 5:06 AM
  • I can't answer for ASP. But if your question is are those actions available on a VIEW, the answer is yes if it is UPDATEable. See the links my earlier reply or the example Uri has shown.

    Monday, August 1, 2011 11:28 AM
    Answerer