StackOverflow DB ( 2010 ) – Foreign Keys

Background

Unfortunately, the StackOverflow DB packaged by Brent Ozar and Co does not include indexes and foreign keys.

The premise is that the package is a learning tool and thus encourage the recipient to go in and create useful amenities.

Foreign Key

Our initial effort will be to create Foreign Key Constraints.

Script

Here is the script …


if
     (  object_id('[dbo].[Badges]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Badges_User]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Badges]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Badges]
        ADD CONSTRAINT [FK_Badges_User]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[Comments]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Comments_User]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Comments]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Comments]
        ADD CONSTRAINT [FK_Comments_User]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[PostLinks]')  is not null )
 and  (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[FK_PostLinks_Posts]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[PostLinks]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Posts]')
)
begin
    ALTER TABLE [dbo].[PostLinks]
        ADD CONSTRAINT [FK_PostLinks_Posts]
        FOREIGN KEY ([PostId])
        REFERENCES [dbo].[Posts]
        ([Id])
end

if
     (  object_id('[dbo].[PostLinks]')  is not null )
 and  (  object_id('[dbo].[LinkTypes]')  is not null )
 and  (  object_id('[FK_PostLinks_LinkTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[PostLinks]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[LinkTypes]')
)
begin
    ALTER TABLE [dbo].[PostLinks]
        ADD CONSTRAINT [FK_PostLinks_LinkTypes]
        FOREIGN KEY ([LinkTypeId])
        REFERENCES [dbo].[LinkTypes]
        ([Id])
end

if
     (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[dbo].[PostTypes]')  is not null )
 and  (  object_id('[FK_Posts_PostTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Posts]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[PostTypes]')
)
begin
    ALTER TABLE [dbo].[Posts]
        ADD CONSTRAINT [FK_Posts_PostTypes]
        FOREIGN KEY ([PostTypeId])
        REFERENCES [dbo].[PostTypes]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[Posts]')  is not null )
 and  (  object_id('[FK_Votes_Posts]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Posts]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_Posts]
        FOREIGN KEY ([PostId])
        REFERENCES [dbo].[Posts]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[Users]')  is not null )
 and  (  object_id('[FK_Votes_Users]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[Users]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_Users]
        FOREIGN KEY ([UserId])
        REFERENCES [dbo].[Users]
        ([Id])
end

if
     (  object_id('[dbo].[Votes]')  is not null )
 and  (  object_id('[dbo].[VoteTypes]')  is not null )
 and  (  object_id('[FK_Votes_VoteTypes]')  is null )
 and  not exists
 (
   select *
   from   sys.foreign_keys tblSFK
   where  tblSFK.parent_object_id =  object_id('[dbo].[Votes]')
   and    tblSFK.referenced_object_id =  object_id('[dbo].[VoteTypes]')
)
begin
    ALTER TABLE [dbo].[Votes]
        ADD CONSTRAINT [FK_Votes_VoteTypes]
        FOREIGN KEY ([VoteTypeId])
        REFERENCES [dbo].[VoteTypes]
        ([Id])
end

 

Database Model

DBeaver

Original

DBeaver_Diagram_20180719_1117AM

Revised

DatabaseModel_20180720_0950AM_Revised

 

Source Control

GitHub

DanielAdeniji/StackExchangeDB
Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s