how to create a foreign key in access 2016

By: | Updated: 2017-04-04 | Comments (7) | Related: 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:

Since the Product table already exists, we are using the ALTER TABLE command on the dbo.Product table.
Foreign Keys are constraints, so we are adding a Constraint of type Foreign Key named FK_Product_ProductCategoryID using the ProductCategoryID column
The Foreign Key references table dbo.ProductCategory using the ProductCategoryID column
For the DELETE rule we are using CASCADE
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

Next time you are creating a Foreign Key, refer to this simple tip.
Refer to this category for more tips on Foreign Keys