3

I have read many posts about how to specify the foreign key name in relationships, no problems there. What I would like to know though, is there a way to change how the primary keys and relationships are named?

For instance, you have a User table with UserId as the primary key. The primary key is called PK_User_1788CC4C07020F21 (or something like that) by the EF code first database generation. Or you have a reference from your User table to your UserVersion table and that is called UserVersion_User by EF. I would like to have the primary key called something like PK_User and the foreign reference something like FK_UserVersion_User.

Is there a way to do this? Some convention override or actually being able to specify the text?

Thanks.

Update: Based on the answer and link bellow I did the following in my Seed method. It's kinda brute force, but I recon that it can be refined, especially if you create the fluent entity type configuration files etc with a generator. The same could be done for the foreign key reference names.

    protected override void Seed(TodoDataContext context)
    {
        FixIndexes(context, "User");
        FixIndexes(context, "UserStats");
        FixIndexes(context, "UserRole");
        FixIndexes(context, "UserVersion");
        FixIndexes(context, "UserUserRoleVersion");
    }

    private void FixIndexes(TodoDataContext context, string tableName)
    {
        const string renamePrimaryKeySql = @"
            DECLARE @TableName NVARCHAR(128)
            DECLARE @IndexName NVARCHAR(128)
            DECLARE @OldName NVARCHAR(128)
            DECLARE @NewName NVARCHAR(128)
            SELECT  @TableName = '{0}'

            SELECT  @IndexName = C.CONSTRAINT_NAME
            FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
            WHERE   pk.TABLE_NAME = @TableName
              AND   CONSTRAINT_TYPE = 'PRIMARY KEY'
              AND   C.TABLE_NAME = PK.TABLE_NAME
              AND   C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME

            SELECT  @OldName = @TableName + '.' + @IndexName
            SELECT  @NewName = 'PK_' + @TableName

            exec sp_rename @OldName, @NewName, 'INDEX'";

        context.Database.ExecuteSqlCommand(string.Format(renamePrimaryKeySql, tableName));
Tyrel Van Niekerk
  • 1,652
  • 4
  • 23
  • 38
  • Hi Tyrel, just wanted to say thank you for the update; FixIndexes was exactly what I was looking for and it works perfectly! :)) – Rami A. Jun 15 '11 at 02:49
  • There is a bug related to sp_rename. Basically, we also have to update the is_system_named column in the sys.key_constraints column so that if\when we generate creation scripts in SSMS for example, the primary key names will also be included. See this: http://davidbrycehoward.com/archive/2011/01/naming-and-renaming-database-constraints/. This bug should be fixed in the next version of SQL Server, code-named "Denali". – Rami A. Jun 15 '11 at 04:33
  • To fix the is_system_name issue, I had to do two things: 1. deploy the database to sql server 2008 r2 and 2. pass 'OBJECT' instead of 'INDEX' to sp_rename. – Rami A. Jun 15 '11 at 05:18

1 Answers1

0

No there is no way to change these names unless you manually drop them in custom initializer and create them again. EF doesn't have pluggable conventions so you cannot override them.

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670