DBProj.dbschema(0,0): Warning SQL01271: The unnamed foreign key between tables X and Y will be checked by checking the table

Question

I received this error while deploying a Visual Studio SQL Server DBProject

DBProj.dbschema(0,0): Warning SQL01271: The unnamed foreign key between tables [dbo].[Match] and [dbo].[Team] will be checked by checking the table. Name the foreign key to avoid checking the entire table and only check the new constraint.

My table definition is

CREATE TABLE [dbo].[Match]
(
    MatchID         int identity(11,2) PRIMARY KEY, 
    TournamentID    int not null REFERENCES Tournament(TournamentID),
    Team1_ID        int not null REFERENCES Team(TeamID),
    Team2_ID        int not null REFERENCES Team(TeamID),
    MatchDate       date,
    MatchTime       time
)

CREATE TABLE [dbo].[Team]
(
    TeamID    int PRIMARY KEY,
    TeamName  varchar(50)
)

UPDATE: Unnamed constraints

If you do not specify a name, SQL will generate a random name built from a prefix which denotes the type of constraint, and partial table and column names followed by a random alphanumeric string. It would normally look something like this: DF_MyTable_MyColu__3F3159AB

Everytime the DBProject is rebuilt and deployed this process is repeated generating a new set of random constraint names

What does this error means? What is the implication? Especially, Name the foreign key to avoid checking the entire table and only check the new constraint.

Answer 1

Summary: It is best practice to always name constraints

Information about this question, for me, is hard to come by. So far, I have been able to find some new information on constraints and I can say I have learnt more.

Posting this same question on dba.SE.com and I got this response

How To Drop An Unnamed Constraint in SQL Server

If you do not specify a name, SQL will generate a random name built from a prefix which denotes the type of constraint, and partial table and column names followed by a random alphanumeric string. It would normally look something like this:

DF__MyTable__MyColu__3F3159AB

.

Cleaning Up SQL Server Deployment Scripts in the DRI Objects with "Junk" names section

For example, if a Primary Key is created but left unnamed, SQL Server uses a default auto-generated name, PK__TableName__Junk (for example, PK__Customer__A4AE64082CE326F2). This will cause SQL Compare to drop the Primary Key and recreate it, because the Junk portion of its name will be different on different databases.

This means that even though there has been no functional change, any change scripts generated using comparison software will, upon every new build, attempt to remove the 'old' constraint and add the 'new' one. Dropping and recreating a primary key on a table can be a very costly operation and since there has been no functional change, one that we can well live without.

.

Dan Jones blog: Best Practice: Naming Constraints

The bottom line is don’t be a lazy DBA/Programmer and let the system name your constraints. The few extra taps on the keyboard will be worth it in the long run.

In fact, this might become part of my standard arsenal of DBA interview questions: do you name your constraints? Or something like that.