40

I was just practicing code first new database entity framework from msdn, I wanna know whether a table without primary key can be created in code first new database EF?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Prateek
  • 1,172
  • 1
  • 11
  • 17
  • 2
    http://stackoverflow.com/questions/1944366/how-do-you-update-a-table-with-a-foreign-key-to-another-table-in-ado-net-entity – Hidden Mar 13 '13 at 09:26
  • 1
    Have a look at http://stackoverflow.com/questions/4068920/code-first-ctp4-table-with-no-primary-key – Francesco Mar 13 '13 at 09:27

6 Answers6

51

There is a great difference between what EF can do with a database, and what is possible with a database.

Most databases allow for a table to be without a primary key. Most databases also allow for a table to be without a clustered index / Index Organized Table (or what ever it is the specific term for it in other database systems).

There is nothing wrong with that, and one should not state that it is a bad idea to have a table without a PK.

As always, it dependes on the needs and usage of the specific table. e.g. a log table, does not need a PK. It will never be used as a FK, so what is it use?

Bottom line, EF does not support tables without a key out of the box, there are some weird workarounds, but none that I have seen is good enough. That is a shame.

Ganesh Jadhav
  • 2,830
  • 1
  • 20
  • 32
sirpadk
  • 809
  • 2
  • 7
  • 12
  • 9
    Exactly. We have a settings table with one record. Doesn't really need a PK. – Steve Greene Aug 30 '17 at 17:40
  • 2
    I have a program that is not made by another company and that I thus cannot alter, but it has a table without primary key, it contains order rows for well, orders. Individual rows are updated or deleted by using two columns such as the articleID (or perhaps row number) and orderID combined, adding new rows is no problem. This is one way it seems to be used. Not saying it's a good or bad idea, just a real life example and something I have to deal with if I want to interface to the same database with EF. – Dennis Aug 22 '18 at 11:33
  • "one should not state that it is a bad idea to have a table without a PK". That is really a great idea to create tables without PKs. I think it should be default option when creating tables, who needs PK if not for FKs, right, right? – IamDOM Jun 08 '20 at 08:34
28

No you can't because Entity Framework needs to know the key to keep track on the object when you make an update or delete operation.

Anyway it's not a good idea to have a table without a PrimaryKey

Wahid Bitar
  • 13,776
  • 13
  • 78
  • 106
  • 3
    Ok wahid, you are right, a table without PK, general operation like update or delete can't be performed, but if i can tell EF to use a particular column which is unique but not PK, then in that case , this operation(update, delete) can be performed by EF? – Prateek Mar 13 '13 at 09:42
  • 1
    No the column should be PK – Wahid Bitar Mar 13 '13 at 09:50
  • SHOULD <> MUST. As long as you don't rely on code first I'm pretty sure you can use any unique column as the key and EF won't see a thing. However, you might have performance issues (unless your other column is the clustered index, but in that case why not make it the primary key). – Cyprien Autexier Mar 25 '18 at 11:23
  • According to SQL Server docs, no-key tables (heaps) should be used when there is no much data for imporoved lookup. – FindOutIslamNow Mar 29 '18 at 13:22
  • Does Lookup table also need primary key? I just want to retrieve the data from that tables. I don't want to do the add, update & delete operations. – Ananth Cool Sep 05 '18 at 06:49
  • Agree. I've noticed that EF 4.0 (and higher) enable user to have a table without primary key, but that table have to contain at least one foreign key. Then, it's possible to set up model builder. For those, who want to know how to achieve that, please follow this link: [Entity Framework: table without primary key](https://stackoverflow.com/questions/3996782/entity-framework-table-without-primary-key) – Maciej Los May 07 '19 at 11:36
  • 1
    @WahidBitar what if I have just composite key and not any other key in table ? – Mox Shah May 30 '19 at 19:58
  • @MoxShah I didn't understand your question clearly, but you can have multiple fields marked as a compound key – Wahid Bitar Jun 09 '19 at 22:22
  • @WahidBitar in some of my tables, we were having multiple primary key (which is Composite Key), but somehow EF doesn't understand that and whenever we try to make any DB transaction, it was throwing an error saying particular table doesn't have any primary key. Later we changed those tables and added single field as primary key. – Mox Shah Jun 14 '19 at 17:54
  • 1
    @MoxShah Yes, I guess you'll have this in EF core. Sadly I don't know why in some cases it doesn't accept table with just composed key. As I tried if the table is part of many to many relationships it will work, but when you have a table without this relationship it will not work till you add a standalone primary key. – Wahid Bitar Jun 15 '19 at 08:20
  • 1
    @WahidBitar Thanks for your response Wahid, appreciate it, for now I have added standalone primary key. – Mox Shah Jun 17 '19 at 10:41
  • Anyway it's not a good idea to say "it's not a good idea to have a table without a PrimaryKey". – Mr. Squirrel.Downy Aug 02 '22 at 08:14
17
  1. Entity Framework must have a key identified on the entity (POCO class) that models the table.
  2. The key you define in Entity Framework does NOT need to be present in the underlying database (e.g. sql table).

If your SQL table does not have a primary key, you can still model it in Entity Framework, you will just need to define a key for that Entity. Pick one or more (possibly all) columns on the Entity that, when combined, will uniquely identify that instance within a collection of the entities. Note this is only important for updating or deleting entities, in that they need to be uniquely identified against the others in that collection to target the change. Find/Select and Add/Insert do not require this consistency.

Sean B
  • 11,189
  • 3
  • 27
  • 40
13

Sometimes adding PK is not possible because it can be read-only, super protected database of a client which you have to use. EF throws exception even on SELECT operations. When I faced that issue I was able to solve it like this (names are artificial):

[Table( "WeirdTable", Schema = "SomeSchema" )]
public class WeirdTable
{
    [Column( "ID1" )]
    public int Id1 { get; set; }

    [Column( "ID2" )]
    public int Id2 { get; set; }
}

In the code file of context:

protected override void OnModelCreating( DbModelBuilder model_builder )
{
    base.OnModelCreating( model_builder );
    model_builder.Entity<WeirdTable>().HasKey(
        t => new { t.Id1, t.Id2 }
    );
}

So basically you just need to specify which columns can be primary key.

Slavik Shynkarenko
  • 376
  • 1
  • 5
  • 8
10

Starting with .NET Core 2.1, EF Core supports models without primary key.
This is realized through a concept of Query Types instead of Enitity Types.
See https://learn.microsoft.com/en-us/ef/core/modeling/query-types

Some of the main usage scenarios for query types are:

  • Serving as the return type for ad hoc FromSql() queries.
  • Mapping to database views.
  • Mapping to tables that do not have a primary key defined.
  • Mapping to queries defined in the model.

Query Types have limits though. They cannot be inserted, updated or deleted on the database. And they have only limited support of navigation properties.

Jpsy
  • 20,077
  • 7
  • 118
  • 115
  • so, I have a question. when we Map a Query type to a table. the table treats as read-only and we can't CRUD on it, well how can we fil that table with data?? at first-time run application how to must we fill that table?!! – Mehdi Sheykhveysi Jan 01 '20 at 14:29
2

EF.Core 5 will have [Keyless] attribute allowing to have an entity without PK. But it is still under development.

EF.Core 5 what's new

AndrewSilver
  • 976
  • 2
  • 14
  • 25