Скачать или смотреть Mastering SQL Constraints: Primary Key, Unique Key, Not Null, and CHECK Explained!

In this video, we dive into the essential SQL constraints that ensure data integrity and consistency in your databases. Learn how to use PRIMARY KEY, UNIQUE, NOT NULL, and CHECK constraints to manage your data effectively. We walk through practical examples with hospital and doctor records to show you how these constraints work in action. Whether you're a beginner or looking to brush up on your SQL skills, this tutorial is perfect for you!

-- CONSTRAINTS
PRIMARY KEY -- Make sure that the column does not have null values and is always having unique records
NOT NULL -- Define a column as not null if you never want to have null values in it.
CHECK -- Validates with the given list of values
UNIQUE -- Avoid duplicate values. But it allows NULL values.

drop table if exists hospitals;
create table hospitals
(
hospital_code int ,
hospital_name varchar(50) ,
location varchar(200),
contact_no bigint,
constraint pk_hospitals primary key (hospital_code,hospital_name)
);
insert into hospitals values (1, 'Fortis Hospital', 'Bangalore', 9900099000);
insert into hospitals values (2, 'Manipal Hospital', 'Manipal', 9900099222);
insert into hospitals values (3, 'Apollo Hospital', 'Mumbai', 9900099333);
insert into hospitals values (4, 'St. Francis Hospital', 'New York', 17194440001);
insert into hospitals values (4, 'Duke Hospital', 'New York', 17194440022);

select * from hospitals;
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);

insert into Doctors values ('D6', null, null, 'Dermatology', 'M', convert(date,'01-01-1990',105), 250000, 5);

drop table if exists Patients;
create table Patients
(
id varchar(15) unique,
first_name varchar(50),
last_name varchar(50),
gender varchar(10) check (gender in ('M', 'F', 'Male', 'Female')),
dob date,
phone_no bigint,
blood_group varchar(15)
);

select * from patients;
insert into Patients values ('P1', 'Vishva', 'Kumar', 'Male', convert(date,'10-05-1990',105), 9990099900, 'A+');
insert into Patients values ('P2', 'Steven', 'Smith', 'Male', convert(date,'10-05-1990',105), 9990099901, 'B+');
insert into Patients values ('P3', 'Ram', 'Kumar', 'Male', convert(date,'11-05-1990',105), 9990099902, 'O+');
insert into Patients values ('P4', 'Raju', 'Sharma', 'Male', convert(date,'14-05-1990',105), 9990099903, 'O-');
insert into Patients values ('P5', 'Eshal', 'Maryam', 'Female', convert(date,'10-07-1990',105), 9990099904, 'A-');
insert into Patients values ('P6', 'Reshal', 'Verma', 'F', convert(date,'10-05-1990',105), 9990099905, 'A+');

insert into Patients values ('P7', 'Rajeev', 'Verma', 'Fix', convert(date,'10-05-1990',105), 9990099905, 'A+');

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

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

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

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

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

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

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

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

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

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

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

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

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