none
Linq to sql table doesn't have Primary Key

    Question

  •  

    Hi,

     

    I am working with Linq to Sql . I want to do insert,update, delete operations with the table which doesn't have primary key.

    but i am unable to do this ,i am getting following error...

     

    Can't perform Create, Update or Delete operations on 'Table(test)' because it has no primary key.

     

    Is any one can help me to do operations for the table does't have primary key.

     

    Thanks

    Shiva

    Saturday, May 17, 2008 1:29 PM

Answers

  • The easiest way will be creating PK on the table.

    Think about this. You have table with five rows; you select first two (whatever "first" means for this case); then you change something on both. Now it's impossible to send updates back to the database, because you *can't* say what rows you updated.

    Imagine this table:
    col1     col2     col3
     1         2          3
     a         b          c
     x         y          z  <<
     1         2          4
    Think how you identify the row marked "<<"? No way until you add primary key (no matter that there's just one "this" row, between select and update there can be insert with same values ...).
    Sunday, May 18, 2008 7:33 AM

All replies

  • How would you perform delete/update/etc. without PK (whensome row doesn't have unique identification)? Yes you can delete/update/etc. using i.e. where x > 10, but this is only particular case. But then when updating some selected entity without identification, then saving changes back to DB will be impossible.
    Saturday, May 17, 2008 3:21 PM
  • Hi,

     

    But i Haven't understand how to solve the problem.Can you please explain more clear.

     

    Shiva

     

    Sunday, May 18, 2008 6:38 AM
  • The easiest way will be creating PK on the table.

    Think about this. You have table with five rows; you select first two (whatever "first" means for this case); then you change something on both. Now it's impossible to send updates back to the database, because you *can't* say what rows you updated.

    Imagine this table:
    col1     col2     col3
     1         2          3
     a         b          c
     x         y          z  <<
     1         2          4
    Think how you identify the row marked "<<"? No way until you add primary key (no matter that there's just one "this" row, between select and update there can be insert with same values ...).
    Sunday, May 18, 2008 7:33 AM
  • A primary key defines the columns that make up the unique reference to each row and LINQ to SQL requires this so it can identify which row to update or delete when it goes back to the database.

    As Jiri says the solution is to determine which columns can be used to uniquely identify the rows and to set these as the primary key.

    [)amien
    Sunday, May 18, 2008 7:40 AM
  • I would just add that if for some reason you cannot have a real primary key in your database table, but somehow there is such a set of columns that define a unique key for each row and that can be used for identity resolution, then you should be able to “fake” a primary key in the model by setting the IsPrimaryKey property on the corresponding columns on the designer.

    Thanks,

    Diego

    Sunday, May 18, 2008 9:26 PM
  • Hi,

     

    So I cannot modify a table which doesn't have primary key using LINQ..?

     

    am I right..?

     

    Shiva

     

     

    Tuesday, May 20, 2008 6:45 AM
  • To update a record in a table you need a unique reference to it regardless of whether you are using LINQ or not.

    If you don't want to give it a primary key that would identify to clients what this unique reference is then you can mark some columns within the LINQ to SQL designer as being the primary key even though they aren't as Diego suggests which will at least let LINQ identify the record to update.

    [)amien
    Tuesday, May 20, 2008 7:40 AM
  • But if you create "fake" unique/PK you can have some "mysterious" update if you will have more rows with this "fake keyed" values.
    Tuesday, May 20, 2008 7:45 AM
  • If you have more than one row with these values then it isn't a unique reference.  This problem is not unique to LINQ to SQL.

    [)amien
    Tuesday, May 20, 2008 7:48 AM
  • Yes, I agree. Thanks for pointing this out. I always meant to recommend that you should use some mechanism to ensure that the key identifying the row in the database is unique. If it is not a PK, then a unique index should work.

     

    What is not clear to me is why would you need to do this (i.e., when is not ok to have a PK but is ok to have a unique index). But since I have heard this request before, I wanted to make clear that LINQ to SQL reasons in this case about what you specify in your model, and not about the actual schema in the database.

     

    Thanks,

    Diego

     

    Tuesday, May 20, 2008 7:52 AM
  • Yes, I know. I was talking about fake unique (created just ot make linq to sql work). If there's is a really set of columns, that can be used as identification, I think it should be created as PK in database anyway.
    Tuesday, May 20, 2008 7:52 AM
  • Let's say that you have 3 tables like these;

    Insurances                CoverOptions             CoverRelations
    --------------              -----------------            -----------------
    InsuranceId PK          CoverId                     InsuranceId  (Foreign Key)
    BeginDate                 CoverTypeName         CoverId        (Foreign Key)
    EndDate
    exc.

    Scenario : You are working for a company that has 4000 cars. All those cars have insurances but not all insurances are same. Some of the insurances are selected to cover everything some of them are covering only the selected disasters.

    CoverRelations table has no PK as it is not necessary.

    If your scenario is like this, I suggest System.Data.SqlClient so you can do whatever you want on that table.
    Thursday, September 24, 2009 10:36 AM
  • As we can write Update or Delete Query without specifying WHERE clause in SQL and operation is performed on all rows. Similar is my case I don't want to create PK on table. I want to update all rows at certain time or lets say I have only one row in table and I know that there will always be only one row in table. Then how to write that kind of query in LINQ


    for example: If I write in SQL

    UPDATING ALL EMPLOYESS
    ---------------------------

    UPDATE OPTION_TABLE
       SET Status = 1


    So my above query is to Set the Status to 1 and there will always be one row. Create PK in this case doesn't make sense. So Whats the solution ? Can anybody help?








    Friday, October 02, 2009 2:35 PM
  • As I suggested on my latest reply, use System.Data.SqlClient   For Ex:

     

    SqlConnection Cn = new SqlConnection("<Connection String Goes Here>");
    SqlCommand Cmd = new SqlCommand("update option_table set status=1",Cn);
    Cn.Open();
    Cmd.ExecuteNonQuery();
    Cn.Close();

     

     

     

    Saturday, October 03, 2009 8:45 AM

  • Dear Serdar Tas, thanks for your reply.

    Well, the above code isn't following LINQ. Suppose my application's whole architecture is using LINQ syntax everywhere for database operations then your above code will violate the architectural rules because you have writen in line Query in Command Object. Although I have done the same when I didn't find any solution.
    Saturday, October 03, 2009 8:57 AM
  • if you have a table storing just one record, make one of the fields PK and you're done. Just for cases like having a relationship table storing only foreign keys it's ok not to have a PK.
    Saturday, October 03, 2009 1:46 PM
  • I tried to set the primary key through Visual Studio's Server Explorer.  Unfortunately, this exception continues to be thrown even after that.

    The little yellow key does nothing!  :)

    Could anyone maybe point out what else I must do?  Is there some other "refresh" mechanism that must be invoked before this primary-key-setting takes effect?

    As a sidenote, I have also already tried right-clicking the database and then clicking "Refresh" from the Server Explorer.

    Thanks! 
    Tuesday, February 23, 2010 4:24 AM
  • Setting primary key after creating DBML file in SQL database is not sufficient, You need to refresh DBML or Re-Create the DBML file.
     
    - Ananth Ramasamy Meenachi www.msarm.com
    "Danny Favela" wrote in message news:8ea66137-364a-476a-936f-9d0fb24c2463...
    I tried to set the primary key through Visual Studio's Server Explorer.  Unfortunately, this exception continues to be thrown even after that.

    The little yellow key does nothing!  :)

    Could anyone maybe point out what else I must do?  Is there some other "refresh" mechanism that must be invoked before this primary-key-setting takes effect?

    As a sidenote, I have also already tried right-clicking the database and then clicking "Refresh" from the Server Explorer.

    Thanks! 

    --- Hope this helps. Ananth Ramasamy Meenachi https://www.msarm.com
    Tuesday, February 23, 2010 4:59 PM
  • Hi,

    I faced same kind of situation in my project [like, insert and update to a configuration table which has only one raw and has no primary key]

    We use _db.ExecuteCommand() or _db.ExecuteQuery() functions to do the insert , update and delete.

    we can pass the query strings, directly to these functions

     

    Thanks & Regards

    Derin Davis

    • Proposed as answer by bwellschitown Thursday, September 30, 2010 4:25 PM
    Wednesday, March 24, 2010 6:15 AM
  • This is a link that provide a good workaround for inserting data into table without primary key.

    http://www.a2zmenu.com/LINQ/Cannot perform Create, Update or Delete operations on Table(Employee) because it has no primary key.aspx


    Wednesday, September 08, 2010 9:10 PM
  • I resolved via the ExecuteCommand method as well.

    i.e. the method Executes SQL commands directly on the database so you don't have to *worry* about linq integrity constraints.

    e.g. :

    db.ExecuteCommand("DELETE FROM tblReportExclude WHERE (DateShipped < {0})", DateTime.Now.Date);
    

    Thursday, September 30, 2010 4:26 PM
  • Setting primary key after creating DBML file in SQL database is not sufficient, You need to refresh DBML or Re-Create the DBML file.
     
    - Ananth Ramasamy Meenachi www.msarm.com
    "Danny Favela" wrote in message news:8ea66137-364a-476a-936f-9d0fb24c2463...
    I tried to set the primary key through Visual Studio's Server Explorer.  Unfortunately, this exception continues to be thrown even after that.

    The little yellow key does nothing!  :)

    Could anyone maybe point out what else I must do?  Is there some other "refresh" mechanism that must be invoked before this primary-key-setting takes effect?

    As a sidenote, I have also already tried right-clicking the database and then clicking "Refresh" from the Server Explorer.

    Thanks! 

    --- Hope this helps. Ananth Ramasamy Meenachi https://www.msarm.com

    I just want to add something here. I was having data repeat the same row over and over again in a gridview. I found out that I had changed the primary key on the table, but had not rebuilt the dbml. This post helped me realize my mistake. Thanks!
    Wednesday, July 27, 2011 8:08 AM