Sql-server – With MS SQL Server are the generated constraint names predictable

constraintdatabase-designentity-frameworkforeign keysql server

When you create your primary key, and foreign key constraints in a CREATE TABLE script, there are named constraints that are created, such as FK__RecentlyVi__ScId__2764765D … are these constraints predictable? IE: if you run the same creation script on another server, will the constraint name be the same?

I ask because with entity framework, when you have multiple references to a secondary table, you get properties like… Foreign, Foreign1, Foreign2, etc… and sometimes when re-generating the entity model the order is different… I've come up with the following to work around this, but want to know if the "default" constraint names will work, even though I'm now using named constraints.

My workaround is included below. If I need this again in the future, may refactor out getting the property of an entity based on the foreign key, and object type name.

private Contact GetContactMatchForForeignKey(string foreignKeyName)
{
  var props = typeof(Order).GetProperties().Where(prop => Attribute.IsDefined(prop, typeof(EdmRelationshipNavigationPropertyAttribute)));
  foreach (var prop in props) {
    var attrs = prop.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), true);
    foreach (var attr in attrs) { 
      var a = (EdmRelationshipNavigationPropertyAttribute)attr;
      if (a.RelationshipName == foreignKeyName && a.TargetRoleName == "Contact") { 
        return (Contact)prop.GetValue(this, null);
      }
    }
  }
  return null;
}

private void SetContactMatchForForeignKey(string foreignKeyName, Contact value)
{
  var props = typeof(Contact).GetProperties().Where(prop => Attribute.IsDefined(prop, typeof(EdmRelationshipNavigationPropertyAttribute)));
  foreach (var prop in props) {
    var attrs = prop.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), true);
    foreach (var attr in attrs) { 
      var a = (EdmRelationshipNavigationPropertyAttribute)attr;
      if (a.RelationshipName == foreignKeyName && a.TargetRoleName == "Contact") { 
        prop.SetValue(this, value, null);
        return;
      }
    }
  }
}

public Contact Purchaser
{
  get { return GetContactMatchForForeignKey("FK_..."); }
  set { SetContactMatchForForeignKey("FK_...",value); }
}

public Contact Seller
{
  get { return GetContactMatchForForeignKey("FK_..."); }
  set { SetContactMatchForForeignKey("FK_...",value); }
}

Best Answer

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar) 
  REFERENCES dbo.foo(bar));
Related Question