Always give a proper name to your Foreign Key in SQL Server else it will assign dynamic name

In some cases people don't give name to Foreign keys while creating tables through scripts. By default SQL Server give them a dynamic name like 'FK__TempTeamM__TeamI__1367E606'.

Suppose a case where dynamic FK is created on Team.TeamID and TeamMember.TeamID AND you have multiple customer databases. What if you got a requirement where you have to drop this FK through script.( Remember, in each database FK name will be different )

Here I am giving you an example which shows how dynamic FK is generated AND how to avoid it.

--Add a master table
CREATE TABLE dbo.TempTeam
(
    TeamID   INT           NOT NULL PRIMARY KEY,
    TeamName VARCHAR (200) NOT NULL
) ON [PRIMARY];

GO
--Add child table and making 'TeamID' as FK
--In this case, SQL server will give a dynamic name to this FK
CREATE TABLE [dbo].[TempTeamMember]
(
    [TeamID] INT NOT NULL FOREIGN KEY REFERENCES TempTeam (TeamID) ON DELETE NO ACTION ON UPDATE NO ACTION,
    [UserID] INT NOT NULL
) ON [PRIMARY];

--Let us check the dynamic name of FK. In my case it is 'FK__TempTeamM__TeamI__1367E606'
SELECT name
FROM   sys.foreign_keys
WHERE  parent_object_id = object_id('TempTeamMember');


GO
--Now lets drop the TeamMember table
DROP TABLE [TempTeamMember];


GO
--Add child table and making 'TeamID' as FK with proper name
CREATE TABLE [dbo].[TempTeamMember]
(
    [TeamID] INT NOT NULL,
    [UserID] INT NOT NULL,
    CONSTRAINT FK_TempTeamMember_TempTeam FOREIGN KEY (TeamID) REFERENCES TempTeam (TeamID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ON [PRIMARY];

--FK name is 'FK_TeamMember_Team' which we have defined above
SELECT name
FROM   sys.foreign_keys
WHERE  parent_object_id = object_id('TempTeamMember');

--At the end drop tables
DROP TABLE TempTeamMember;

DROP TABLE TempTeam;  

Always give a proper name to your Foreign Key.