Edit: It was a default constraint as a result of setting a default value when adding the column. The name gave it away when starting with 'DF__'. Anyway, solved it by adding the column and manually naming the constraint.


And now I can easily delete the constraint and delete the column.

For testing purposes I need to drop and recreate a few tables quite often. I am using MS-Sql server 2008 r2.

When I create the tables I also create a foreign key in an existing table (one that I cannot just drop) with:


this does create the foreign key correctly, but it also adds a constraint with a system generated name (something like DF__MyTable_FooId__53385258), that is not create if the foreign key is defined in the CREATE TABLE statement.

My problem is, that when I want to drop MyTable, I have to remove the foreign key constraint - this is easy enough since I named it, but the other system generated constraint is still there and I don't have a direct way to delete it.

Is it possible to add the foreign key to an existing table without the system generating a second (and redundant I assume) constraint?

closed as off-topic by Paul White Sep 9 '15 at 12:44

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 4
    DF will be an auto generated name for an unnamed default constraint not a FK constraint. Please post the code that contains the key word DEFAULT – Martin Smith Jul 30 '12 at 13:20
  • I figured that out, but thanks for the explanation. – hvidgaard Aug 1 '12 at 7:04

The only way I can think of that a DDL statement like yours may result in an "extra" constraint is when there's a DDL trigger that creates that constraint in response to your DDL statement(s). If that is not the case with your database, the said extra constraint is definitely the result of a totally different statement unconnected with the one that creates the foreign key.

It could have appeared at an earlier stage (e.g. when the table was being created), or it could have been added by a statement that was executed in the same script with the one in your post and that fact for some reason didn't register with you at the time.

  • I feel slightly stupid now. I added the extra column with a NOT NULL and DEFAULT statement, which of cause results in a constraint. Thanks for pointing me in the right direction. – hvidgaard Jul 31 '12 at 7:53
  • 1
    @hvidgaard: This kind of things happens to me more often than I would like it too (hopefully, less often now than before). :) – Andriy M Jul 31 '12 at 7:57
  • The name gives it all away, but I've not worked with sql-server much, so it's all learning. For the curious I edited the question with the solution. – hvidgaard Jul 31 '12 at 7:59

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