Understanding SQL Foreign Keys: Building Relationships Between Tables

Learn how to build and manage parent-child relationships in SQL using Foreign Keys. In this video, we create tables for doctors and medical records, establish relationships between them, and explore key SQL commands for inserting, updating, and retrieving data. Perfect for database beginners and those looking to understand relational databases better."
drop table if exists Doctors;
create table Doctors
(
id varchar(15) primary key,
first_name varchar(50) not null ,
last_name varchar(50) not null,
specilization varchar(200),
gender bigint,
dob date,
salary int,
hospital_id int
);
ALTER TABLE DOCTORS alter column gender varchar(10);

insert into Doctors values ('D1', 'Ragav', 'Jain', 'Cardiology', 'Male', convert(date,'01-01-1980',105), 250000, 4);
insert into Doctors values ('D2', 'Priyanka', 'Verma', 'Physician', 'female', convert(date,'01-01-1995',105), 350000, 3);
insert into Doctors values ('D3', 'Sheetal', 'Shetty', 'Neurology', 'F', convert(date,'01-01-1980',105), 200000, 3);
insert into Doctors values ('D4', 'David', 'Smith', 'Psyatry', 'M', convert(date,'01-01-1970',105), 150000, 1);
insert into Doctors values ('D5', 'James', 'Dias', 'Oncology', 'm', convert(date,'01-01-1985',105), 150000, 2);
insert into Doctors values ('D6', 'Ali', 'Abdal', 'Dermatology', 'M', convert(date,'01-01-1990',105), 250000, 5);

drop table if exists Medical_Records;
create table Medical_Records
(
id int ,
problem varchar(200),
date_of_examination date,
patient_id varchar(15),
doctor_id varchar(15) references doctors(id)
);

insert into Medical_Records values (1, 'Food Poisoning', convert(date,'10-01-2023',105), 'P1', 'D2');
insert into Medical_Records values (2, 'Fever and Flu', convert(date,'11-01-2023',105), 'P6', 'D6');
insert into Medical_Records values (3, 'Back Spasm', convert(date,'15-01-2023',105), 'P7', 'D6');
insert into Medical_Records values (4, 'Headache', convert(date,'20-01-2023',105), 'P0', 'D7');

insert into Doctors values ('D7', 'Vijay', 'Abdal', 'Dermatology', 'M', convert(date,'01-01-1990',105), 250000, 4);

select * from Doctors;
select * from Medical_Records;

SQL for Beginners: A Complete Introduction to SQL Basics!
   • SQL for Beginners: A Complete Introdu...  

Master SQL Commands: DDL, Data Types & Constraints Explained!
   • Master SQL Commands: DDL, Data Types ...  

Mastering SQL Constraints: Primary Key, Unique Key, Not Null, and CHECK Explained!
   • Mastering SQL Constraints: Primary Ke...  

Understanding SQL Foreign Keys: Building Relationships Between Tables
   • Understanding SQL Foreign Keys: Build...  

SQL Identity Column: Auto-Generating Unique Values for Your Tables
   • SQL Identity Column: Auto-Generating ...  

SQL DML & DDL Commands Explained | Insert, Update, Delete, Truncate, Drop
   • SQL DML & DDL Commands Explained | In...  

Master Basic SQL Queries & Operators | Part 1: Fetch, Filter & Count
   • Master Basic SQL Queries & Operators ...  

SQL Functions and Queries: Aggregate, CAST, REPLACE, ROUND, GETDATE
   • SQL Functions and Queries: Aggregate,...  

Mastering SQL INNER JOIN: Essential Queries & Examples
   • Mastering SQL INNER JOIN: Essential Q...  

SQL Group By Explained with Examples | Master Aggregate Queries
   • SQL Group By Explained with Examples ...  

Mastering SQL: Group By, Having, CASE, Order By, Join, Top, and Limit
   • Mastering SQL: Group By, Having, CASE...  

Introduction to SQL Normalization: 1NF (First Normal Form) - Part 1
   • Introduction to SQL Normalization: 1N...  

Understanding SQL Normalization: 2NF (Second Normal Form) - Part 2
   • Understanding SQL Normalization: 2NF ...  

Mastering SQL Normalization: 3NF (Third Normal Form) Explained - Part 3
   • Mastering SQL Normalization: 3NF (Thi...  

Master SQL Subqueries: Scalar, Multi-Row, and Correlated Subqueries Explained
   • Master SQL Subqueries: Scalar, Multi-...  

SQL Tutorial: Remove Duplicate Data Efficiently | Common Interview Question
   • SQL Tutorial: Remove Duplicate Data E...  

SQL Joins Explained: Master INNER, LEFT, RIGHT, FULL, CROSS & SELF Joins with Examples
   • SQL Joins Explained: Master INNER, LE...  

Master SQL Joins: Self Joins, Outer Joins, and More!
   • Master SQL Joins: Self Joins, Outer J...  

SQL Queries Using CTE: Profitability & Monthly Sales Differences
   • SQL Queries Using CTE: Profitability ...  

Calculate User Popularity Percentage with SQL CTEs
   • Calculate User Popularity Percentage ...  

SQL Window Functions Explained: ROW_NUMBER, RANK & DENSE_RANK
   • SQL Window Functions Explained: ROW_N...  

SQL Window Functions Explained: LEAD & LAG with Real Examples!
   • SQL Window Functions Explained: LEAD ...  

Master SQL Window Functions: FIRST_VALUE, LAST_VALUE, and More!
   • Master SQL Window Functions: FIRST_VA...  

Easily Delete Duplicate Rows in SQL Server with ROW_NUMBER()
   • Easily Delete Duplicate Rows in SQL S...  

Understanding Recursive CTEs in SQL: A Simple Guide
   • Understanding Recursive CTEs in SQL: ...  

Master SQL PIVOT: Transform Rows into Columns
   • Master SQL PIVOT: Transform Rows into...