Why does altering a table and adding a foreign key create an extra constaint?

Question

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?

Answer 1

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.