Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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); }
}
share|improve this question

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

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

add comment

2 Answers 2

up vote 4 down vote accepted

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));
share|improve this answer
    
Thanks... I am naming my constraints now... in the code, just referencing that constraint, which is an attribute on the properties to programmatically get the correct property. BTW: if you modify your answer to include a foreign key constraint as well, will mark it correct. :) Thanks. –  Tracker1 Sep 20 '12 at 20:24
add comment

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'
share|improve this answer
    
Nice... thanks. –  Tracker1 Sep 25 '12 at 17:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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