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.

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.

ALTER TABLE MyTable ADD [FooId] [int] NOT NULL CONSTRAINT DF_MyTable_FooId DEFAULT 0

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:

ALTER TABLE MyTable
ADD CONSTRAINT FK_MyTable_NewTable
FOREIGN KEY (FooId) REFERENCES NewTable (Id)

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?

share|improve this 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

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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

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.