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?