In a previous blog post, we discussed how to create a single table. We also discussed why relationships matter when joining multiple tables. In this post, we will demonstrate how to create those tables and relationships.

Create a Schema

The first thing we will demonstrate is how to create a schema. This will be used to hold the tables that we will be creating.

--Create Schema for Demo tables
CREATE SCHEMA Demo Authorization dbo
GO

Creating Table Relationships

Now we will create the first of three tables. The Demo.Customers table will have a Primary Key on the CustomerID column. But we will not have a Foreign Key.

--Customers table will have a Primary Key named CustomerID.
--This table does not have a Foreign Key.
CREATE TABLE Demo.Customers
(CustomerID smallint PRIMARY KEY,
 First_Name varchar(10),
 Last_Name varchar(15),
 Club tinyint)
GO

The second table will be named Demo.Products and will have a Primary Key on the ProductID column. This table also does not have a Foreign Key.

--Products table will have a Primary Key named ProductID
--This table does not have a Foreign Key.
CREATE TABLE Demo.Products
(ProductID int Primary Key,
 ProductName varchar(30),
 Price smallmoney)
GO

Create Tables with Foreign Keys

The final table for this demonstration will be the Demo.Orders table. This is where we will create the relationship between the Demo.Customers table and the Demo.Orders table.

This relationship will be established by creating a Foreign Key on the Demo.Orders to connect to the Primary Key on the Demo.Customers table. The relationship will be established as we are creating the Demo.Orders table.

--Orders table will have a Primary Key and a two Foreign Keys 
--The key referencing the Customers table is added at table creation
--The key referecing the Products table is added after table creation
CREATE TABLE Demo.Orders
(OrderID int IDENTITY PRIMARY KEY,
 CustID smallint,
 ProductID int,
 Qty tinyint,
 OrderDate date,
 CONSTRAINT FK_Customers_Orders FOREIGN KEY(CustID) --Adding the first FK
	REFERENCES Demo.Customers(CustomerID) --Establish the relationship
	ON UPDATE CASCADE ON DELETE SET NULL --Update and Delete settings on relationship
 )
GO

In the code above we added the Foreign Key constraint on the CustID column of the Demo.Orders table that REFERENCES the CustomerID column in the Demo.Customers table.

This establishes the Demo.Customers table as the Parent Table and Demo.Orders as the Child Table. (Review of Parent Table vs Child Table).

Additionally, to enforce referential integrity between the two tables, we added a line to determine what happens to records in the child table when an UPDATE or DELETE statement is executed on the Parent Table.

Now, we need to establish a relationship between the Demo.Orders table and the Demo.Products table. We could have accomplished this as we were creating the table, but this is to demonstrate how to add a relationship after a table was created.

--ALTER Orders Table to add the second Foriegn Key
ALTER TABLE Demo.Orders
ADD CONSTRAINT FK_Products_Orders FOREIGN KEY(ProductID)
	REFERENCES Demo.Products(ProductID)
GO

In this code above we added the Foreign Key constraint on the ProductID column of the Demo.Orders table that REFERENCES the ProductID column in the Demo.Products table.

This will establish the Demo.Products table as the Parent Table and Demo.Orders as the Child Table.

Inserting Records into Tables with Foreign Keys

While we have another post on using the INSERT statement, there is an item of note when inserting records into tables that have a Foreign Key constraint that is enforcing referential integrity. But, first let’s add records into the Demo.Customers table and the Demo.Products table. NOTE: These tables currently do not have foreign keys.

--INSERT Records into the Customers table.
INSERT INTO Demo.Customers
VALUES (250, 'Andy', 'Anderson', 1),
	   (255, 'Jeff', 'Rollins', 0),
	   (267, 'Bob', 'Smith', 1),
	   (278, 'Jenny', 'Jefferson', 0),
	   (388, 'Cindy', 'Samuels', 2)
GO

--INSERT Records into the Products table.
INSERT INTO Demo.Products
VALUES (23569, 'Data Science Handbook', 5.60),
	   (29058, 'Hit Refresh', 4.25),
	   (30550, 'Fantasy Football Mistakes', 3.15),
	   (32575, 'Power BI in a Day', 2.45),
	   (32600, 'Writing T-SQL Made Easy', 3.20),
	   (32660, 'Intentionally Left Blank', 3.75),
	   (32667, 'Azure SQL for Beginners', 2.50),
	   (33002, 'The Case of the Missing Syntax', 3.75)
GO	   

However, since the Demo.Orders table has Foreign Keys, we need to disable referential integrity to be able to add data to that table.

--Disable Constraints on Demo.Orders table to allow INSERT.
--This disables referential integrity. For Demo purposes only.
ALTER TABLE Demo.Orders NOCHECK CONSTRAINT FK_Customers_Orders
ALTER TABLE Demo.Orders NOCHECK CONSTRAINT FK_Products_Orders
GO

Now that we have disabled referential integrity on the foreign key constraints, we will insert records into the Demo.Orders table.

--INSERT Records into the Orders table.
INSERT INTO Demo.Orders
VALUES (250, 23569, 3, '20161102'),
	   (250, 32575, 3, '20161103'),
	   (250, 32600, 5, '20161104'),
	   (255, 30550, 4, '20161205'),
	   (278, 33002, 6, '20170216'),
	   (290, 32667, 9, '20170317'),
	   (388, 32600, 7, '20170415'),
	   (388, 29058, 5, '20170415'),
	   (402, 32660, 2, '20170501')
GO

Finally, we will re-enable the Foreign Key constraints on the Demo.Orders table to enforce referential integrity.

--Re-Enable Constraints on the Orders table.
ALTER TABLE Demo.Orders CHECK CONSTRAINT FK_Customers_Orders
ALTER TABLE Demo.Orders CHECK CONSTRAINT FK_Products_Orders
GO