Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to create a SQL Server foreign key


By:   |   Last Updated: 2017-04-04   |   Comments (5)   |   Related Tips: More > Constraints

Problem

I need to create a Foreign Key relationship between two SQL Server tables and I would like to know how this is done using the SQL Server Management Studio (SSMS) GUI as well as using T-SQL scripts.  I already have the tables created, but how do I create the Foreign Key relationship.

Solution

Creating a Foreign Key relationship should be a pretty straightforward task, but understanding how to use the GUI to do this is not as simple as you might think.

Let's say we have these two tables in our database.

CREATE TABLE [dbo].[Product](
   [ProductID] [int] IDENTITY(1,1) NOT NULL,
   [ProductCategoryID] [int] NOT NULL,
   [Product] [varchar](50) NOT NULL,
   CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([ProductID] ASC)
) ON [PRIMARY]

CREATE TABLE [dbo].[ProductCategory](
   [ProductCategoryID] [int] NOT NULL,
   [ProductCategory] [varchar](50) NOT NULL,
   CONSTRAINT [PK_ProductCategory] PRIMARY KEY CLUSTERED ([ProductCategoryID] ASC)
 ) ON [PRIMARY]

We want to create a Foreign Key relationship between Product.ProductCategoryID and ProductCategory.ProductCategoryID to make sure the ProductCategoryID is a valid option for any entries entered into the Product table.

To help with understanding the relationships, ProductCategory will be the referenced table and Product will be the referencing table.

When creating a Foreign Key there are a few options to enforce rules when changes are made to the referenced table:

  • Delete Rule - this determines what happens if the record is deleted in the referenced table. 
  • Update Rule - this determines what happens if the row key is updated in the referenced table.

The options for the Delete and Update rules are:

  • NO ACTION - if the change breaks the referential integrity, the change is rolled back.
  • CASCADE - the change should also occur on the referencing table.  If it is for DELETE the referencing rows will be deleted too.  If this if for an UPDATE the referencing table row values will be updated to match the new value.
  • SET NULL - the value in the referencing table should be set to NULL as long as NULL values are allowed on that column in the referencing table.  If not, the change is rolled back.
  • SET DEFAULT - the value in the referencing table would be set to a default value.  This value would also need to exist in the referenced table.  If the value does not exist in the referenced table, the change would be rolled back.

Create Foreign Key Using T-SQL

To create the Foreign Key using T-SQL, the statement would be written as follows.  This is showing that we want to CASCADE the changes for both DELETEs and UPDATEs.

ALTER TABLE [dbo].[Product]     
ADD CONSTRAINT FK_Product_ProductCategoryID FOREIGN KEY (ProductCategoryID)     
    REFERENCES [dbo].[ProductCategory] (ProductCategoryID)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE  

Here is a description for each line above:

  1. Since the Product table already exists, we are using the ALTER TABLE command on the dbo.Product table.
  2. Foreign Keys are constraints, so we are adding a Constraint of type Foreign Key named FK_Product_ProductCategoryID using the ProductCategoryID column
  3. The Foreign Key references table dbo.ProductCategory using the ProductCategoryID column
  4. For the DELETE rule we are using CASCADE
  5. For the UPDATE rule we are using CASCADE

That's all there is to it.

Create Foreign Key Using SSMS GUI

To create a Foreign Key using the SSMS GUI, using Object Explorer select the referencing table dbo.Product, go to Keys, right click on Keys and select New Foreign Key...:

use ssms to create foreign key

The table designer will open as well as a new window like below.  Click on the ellipse (...) next to Tables and Columns Specification.

ssms foreign key designer

Another window will open like below.

ssms foreign key designer

We can change the Relationship Name (Foreign Key name) and also need to select the appropriate tables and columns.  So on the left we select the referenced table ProductCategory and underneath we select the ProductCategoryID column.  On the right, the referencing table Product is already selected, but underneath we need to select the column ProductCategoryID.  After making the changes, click OK.

ssms foreign key designer

At this point we are back to the first Foreign Key screen that we saw.  If you scroll down on the right pane, we can see the Delete and Update rules as shown in the image below.  Select the appropriate values from the dropdown list.

You can see below there are two other options in the GUI as shown below:

  • Enforce For Replication - if you are using replication and don't want the foreign keys enforced at the subscriber for the replicated data you would select No.
  • Enforce Foreign Key Constraint - if you do not want to the foreign key to be checked you would select No.  Setting this to No defeats the purpose of having a foreign key setup.
ssms foreign key designer

Now click Close to accept the Foreign Key changes.

ssms foreign key designer

To actually save the changes to the database, you can click on the Save button in SSMS, press Ctrl-S or use the SSMS menu and select File > Save Product (will be table name you are changing to save the Foreign Key constraint.

Reviewing the New Foreign Key

After the Foreign Key has been created, we can see it in Object Explorer as shown below. You can right click on the Foreign Key and either generate a script, modify it, delete it or rename it.

ssms object explorer show foreign keys
Next Steps


Last Updated: 2017-04-04


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, February 15, 2019 - 11:27:39 AM - Greg Robidoux Back To Top

Thanks James for pointing out the issue.  I updated the code.

-Greg


Friday, February 15, 2019 - 10:27:22 AM - James Back To Top
CREATE TABLE [dbo].[Product](
   [ProductID] IDENTITY(1,1) NOT NULL,
   [ProductCategoryID] [int] NOT NULL,
   [Product] [varchar](50) NOT NULL,
   CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([ProductID] ASC)
) ON [PRIMARY]

Your statement here should include a datatype for [ProductId], like below

CREATE TABLE [dbo].[Product](
   [ProductID] [int] IDENTITY(1,1) NOT NULL,
   [ProductCategoryID] [int] NOT NULL,
   [Product] [varchar](50) NOT NULL,
   CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([ProductID] ASC)
) ON [PRIMARY]

Tuesday, July 17, 2018 - 3:24:41 PM - Bill Peyton Back To Top

 Excellent and clear explanation, thanks!


Thursday, April 05, 2018 - 11:21:34 AM - Greg Robidoux Back To Top

Hi Suji,

try this code:

CREATE VIEW RecentAlbums
AS
SELECT Artists.ArtistName, Albums.AlbumName, Albums.ReleaseDate, Genre.Genre
FROM     Albums INNER JOIN
                  Artists ON Albums.ArtistId = Artists.ArtistId INNER JOIN
                  Genre ON Albums.GenreId = Genre.GenreId
WHERE  (Artists.ReleaseDate > DATEADD(year, - 10, GETDATE()));


Sunday, April 01, 2018 - 4:18:40 AM - suji Back To Top

 Hi, 

 

I tried to create "VIEW" in SSMS showing error.

If you can help me with this,

Error: 

"Msg 241, Level 16, State 1, Line 9
Conversion failed when converting date and/or time from character string."

 

code: 

(

CREATE VIEW RecentAlbums
AS
SELECT Artists.ArtistName, Albums.AlbumName, Albums.ReleaseDate, Genre.Genre
FROM     Albums INNER JOIN
                  Artists ON Albums.ArtistId = Artists.ArtistId INNER JOIN
                  Genre ON Albums.GenreId = Genre.GenreId
WHERE  (Artists.ArtistName > DATEADD(year, - 10, GETDATE()));

select * from RecentAlbums;

 )

 

Regards


Learn more about SQL Server tools