4

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); }
}

migrated from stackoverflow.com Sep 23 '12 at 7:47

This question came from our site for professional and enthusiast programmers.

7

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));
4

In addition to what Aaron Bertrand has said, you can rename your existing keys to something reasonable with sp_rename. Like so:

exec sp_rename 'some random foreign key name', 'new better foreign key name', 'OBJECT'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.