Create SQL Server Database Tables, Foreign Keys and Data

By: Jan Potgieter | Updated: 2022-08-18 | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | > Database Design

Problem

Most applications have some type of database on the backend to store data. In this SQL tutorial, we will walk through the creation of tables for a database application in Microsoft SQL Server to give you an idea of how you might go about setting up the tables and the relationships between these tables.

Solution

To get a database ready for an application, we need to create the tables to hold the data we want to store. In this SQL tutorial, we will create tables related to HR data to store employee information.

Create Test SQL Server Database

First, we will create a new database that we will use to store the tables. Here is the syntax for the CREATE DATABASE statement that can be run in SQL Server Management Studio (SSMS):

-- Create the HRDatabase
USE master
GO

-- Drop Database Command
DROP DATABASE IF EXISTS HRDatabase
GO

-- SQL Create Database Command with default database files and properties
CREATE DATABASE HRDatabase
GO
 
-- Use the current database
USE HRDatabase -- Database Name
GO

Step 1: Create the two main tables for the SQL database

Let's start by creating the two main tables for the database, namely:

Companies
Employees

-- Create the two main tables Companies and Employees
DROP TABLE IF EXISTS Companies;
DROP TABLE IF EXISTS Employees;
 
CREATE TABLE Companies (
   id             INT CONSTRAINT PK_Companies PRIMARY KEY IDENTITY,
   CompanyName    VARCHAR(80) NOT NULL, -- Column name, data type and null value
   CompAddress    VARCHAR(80) NOT NULL,
   CompContactNo  VARCHAR(20) NOT NULL,
   IsActive       BIT CONSTRAINT DF_IsActive_Companies DEFAULT(1),
   CreateDate     DATETIME NOT NULL DEFAULT getdate()
);
 
CREATE TABLE Employees (
   id             INT CONSTRAINT PK_Employees PRIMARY KEY IDENTITY,
   EmployeeName   VARCHAR(80) NOT NULL,
   ContactNo      VARCHAR(20) NOT NULL,
   Email          VARCHAR(80) NOT NULL,
   CompID         INT NOT NULL,
   AddressID      INT NOT NULL,
   TitleID        INT NOT NULL,
   GenderID       INT NOT NULL,
   IsActive       BIT CONSTRAINT DF_IsActive_Employees DEFAULT(1),
   CreateDate     DATETIME NOT NULL DEFAULT getdate()
);

Step 2: Create the Supporting SQL Tables

Next, we can create the supporting tables with the following SQL statements that will be used in the application which will reside in the dbo schema by default:

Title
Gender
Leave
LeaveTypes
Addresses

-- Create the supporting tables
DROP TABLE IF EXISTS Title;
DROP TABLE IF EXISTS Gender;
DROP TABLE IF EXISTS LeaveTypes;
DROP TABLE IF EXISTS Leave;
DROP TABLE IF EXISTS Addresses;
 
CREATE TABLE Title (
   id          INT CONSTRAINT PK_Title PRIMARY KEY IDENTITY,
   Title       VARCHAR(80) NOT NULL,
   TitleDescr  VARCHAR(80) NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);
 
CREATE TABLE Gender (
   id          INT CONSTRAINT PK_Gender PRIMARY KEY IDENTITY,
   Gender      VARCHAR(80) NOT NULL,
   GenderDescr VARCHAR(80) NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);
 
CREATE TABLE LeaveTypes (
   id          INT CONSTRAINT PK_LeaveTypes PRIMARY KEY IDENTITY,
   LeaveType   VARCHAR(80) NOT NULL,
   LeaveDescr  VARCHAR(80) NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);
 
CREATE TABLE Addresses (
   id          INT CONSTRAINT PK_Addresses PRIMARY KEY IDENTITY,
   Address     VARCHAR(256) NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);
 
CREATE TABLE Leave (
   id          INT CONSTRAINT PK_Leave PRIMARY KEY IDENTITY,
   LeaveTypeID INT NOT NULL,
   StartDate   DATE NOT NULL,
   EndDate     DATE NOT NULL,
   LeaveNotes  VARCHAR(800) NOT NULL,
   EmpID       INT NOT NULL,
   CreateDate  DATETIME NOT NULL DEFAULT getdate()
);

After creating the seven tables, the database diagram will look like the image below. Notice that there are no links between the tables even though we created the correct referencing ids in the supporting tables.

Step 3: Add Foreign Keys to create Relationships between the SQL Tables

A Foreign Key is a column on a table that refers to the Primary Key in another table. The table with the foreign key is the child table and the table with the primary key is the parent table. By adding Foreign Keys, you are enforcing referential integrity between the tables. Here are the SQL commands:

-- Add the foreign keys that create the links between the respective tables
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Companies
  FOREIGN KEY(CompID) REFERENCES Companies(id) ON DELETE CASCADE ON UPDATE CASCADE;
 
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Addresses
  FOREIGN KEY(AddressID) REFERENCES Addresses(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
 
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Titles
  FOREIGN KEY(TitleID) REFERENCES Title(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
 
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Genders
  FOREIGN KEY(GenderID) REFERENCES Gender(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
 
ALTER TABLE dbo.Leave ADD CONSTRAINT FK_Leave_Employees 
  FOREIGN KEY(EmpID) REFERENCES Employees(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
 
ALTER TABLE dbo.Leave ADD CONSTRAINT FK_Leave_LeaveTypes
  FOREIGN KEY(LeaveTypeID)REFERENCES LeaveTypes(id) ON DELETE NO ACTION ON UPDATE NO ACTION;

After the above Foreign Keys are added, you will see the difference in the links displayed in the database diagram below. Take a moment to study the database diagram below. See the different links between the tables and take note of the relationships, namely one-to-many.

Step 4: Insert sample data into the SQL Tables

Run the below insert statements to insert data into the tables.

-- insert companies
INSERT INTO Companies (CompanyName, CompAddress, CompContactNo) 
VALUES
  ('Alpha Company', '123 North Street, Garsfontein, Pretoria', '091 523 6987' ),
  ('Bravo Company', '456 South Street, Brooklyn, Pretoria', '091 523 4789' ),
  ('Charlie Company', '987 West Street, Lynnwood, Pretoria', '091 523 1235' ),
  ('Delta Company', '258 East Street, The Meadows, Pretoria', '091 523 7414' ),
  ('Echo Company', '100 Amber Street, Hatfield, Pretoria', '091 523 9685' )

-- insert title
INSERT INTO Title (Title, TitleDescr) 
VALUES
  ('Mr', 'Mister'),
  ('Mrs', 'Mrs'),
  ('Miss', 'Misses')

-- insert gender 
INSERT INTO Gender (Gender, GenderDescr) 
VALUES 
  ('Male' , 'Male'),
  ('Female' , 'Female')

-- insert leave types 
INSERT INTO LeaveTypes (LeaveType, LeaveDescr) 
VALUES
  ('Annual', 'Annual Leave'),
  ('Study', 'Study Leave'),
  ('Sick', 'Sick Leave'),
  ('Unpaid', 'Unpaid Leave')

-- insert addresses 
INSERT INTO Addresses (Address) 
VALUES
  ('123 North Street, Garsfontein, Pretoria') ,
  ('456 South Street, Brooklyn, Pretoria'),
  ('987 West Street, Lynnwood, Pretoria'),
  ('258 East Street, The Meadows, Pretoria'),
  ('100 Amber Street, Hatfield, Pretoria')

-- insert employees 
INSERT INTO Employees (EmployeeName, ContactNo, Email, CompID, AddressID, TitleID, GenderID) 
VALUES
  ('Joe Blogs', '012 365 4789', '[email protected]', 1 ,1, 1, 1) ,
  ('Jane Doe', '012 365 4789', '[email protected]', 2 ,1, 2, 1) ,
  ('John Smit', '012 365 4789', '[email protected]', 1 ,1, 1, 1) ,
  ('Eddy Jones', '012 365 4789', '[email protected]', 4 ,1, 1, 1) ,
  ('Mary Poppins', '012 365 4789', '[email protected]', 5 ,1, 3, 1)

Step 5: Create a leave record for an employee

For our purpose, let's add some leave time for employee Jane Doe at the end of September.

After the insert is run, run the select statements on all tables to check the data.

INSERT INTO Leave (LeaveTypeID, StartDate, EndDate, LeaveNotes, EmpID) 
VALUES 
  (1, '2022-09-20', '2022-09-24', 'Annual leave to enjoy vacation at the coast', 2)
 
-- Run below select queries to check the data
SELECT * FROM Companies
SELECT * FROM Employees
SELECT * FROM Title
SELECT * FROM Gender
SELECT * FROM LeaveTypes
SELECT * FROM Leave
SELECT * FROM Addresses

Step 6: Query the SQL data to show related information

Let's run the below SELECT statement to test our scenario on Jane Doe:

Employee table: Jane Doe with EmployeeID = 2,
Companies table: works for Alpha Company (CompID = 2)
Addresses table: she lives at 123 North Street, Garsfontein, Pretoria (AddressID = 1)
Title table: her title is Mrs (TitleID = 2)
Gender table: she is Female (GenderID = 1)
LeaveType table: she has Annual Leave (LeaveTypeID = 1)
Leave table: the leave starts on 2022-09-20 and ends 2022-09-24

Let's also calculate the number of days she will be on leave using the DATEDIFF function as in the query below.

SELECT  c.CompanyName      
      , e.EmployeeName
      , a.Address
      , lt.LeaveType
      , t.Title
      , g.Gender
      , l.StartDate, l.EndDate
      , DATEDIFF(day, l.StartDate, l.EndDate)+1 'Leave Days'
FROM dbo.Companies c 
JOIN dbo.Employees e on e.CompID = c.id
JOIN dbo.Addresses a on a.id = e.AddressID
JOIN dbo.Title t on t.id = e.TitleID
JOIN dbo.Gender g on g.id = e.GenderID
JOIN dbo.Leave l on l.EmpID = e.id
JOIN dbo.LeaveTypes lt on lt.id = l.LeaveTypeID

When calculating the difference between two dates, the start day is 20 Sep and the end day is 24 Sep; thus, the difference between the two dates is four (4). If the EndDate of the leave includes the 24th of Sep, you have to add one day to the number of 'Leave Days'.

Clean Up - Drop Database

Clean up by dropping the HRDatabase database. This should not be done in a production environment.

USE master;
GO
 
-- Drop Database if it exist
DROP DATABASE IF EXISTS HRDatabase;
GO

Next Steps

In the next article, we will take a look at more examples of how to insert, update, delete and select data from the database.

To read more about creating tables in a database, check out these tips:

About the author

Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-08-18

How to Create a SQL Server Database, Tables, Foreign Keys and Data for an Application