How to Create a SQL Server Database, Tables, Foreign Keys and Data for an Application
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.
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:
-- 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:
-- 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
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:
- Create Tables in SQL Server with T-SQL
- How To Create a Table in SQL Server
- Learn about SQL Server Indexes
- SQL Server Stored Procedure Tutorial
About the author
View all my tips
Article Last Updated: 2022-08-18