Teachers, Students and Guardians Tables – SQL Server | Lesson 11

Published / updated3 Oct 2026

Module 3 · Lesson 11Beginner25 minInterview:FMSA

Before this lesson: Constraints: Primary, Foreign, Unique, Check and Default – SQL Server

Introduction

A school is its people: teachers, students, and the parents or guardians who look after the students. In this lesson, you create their tables.

This time, every rule goes inside CREATE TABLE from the start. You learned each kind of constraint in Lesson 10; now you write them all at once.

By the end of this lesson, you will be able to:

• Write constraints inline, inside CREATE TABLE.
• Create the Teachers, Students, and Guardians tables.
• Build a junction table with a composite primary key.
• Create tables in the right order so every foreign key has a target.

Why this matters

Rajesh Sharma is the father of two students: Aarav in 10-A and Advika in 6-A. If his phone number is stored on each child's row, the office must update it twice, and one day they will miss one.

People data also attracts mistakes: duplicate admission numbers, a teacher email typed twice, a gender typed as "Male" in one row and "M" in another. The constraints in this lesson stop each of those at the door.

The concept

Example: constraints inside CREATE TABLE

In Lesson 10, you added constraints to tables that already existed, with ALTER TABLE. For a new table, you can write them inside CREATE TABLE:

• Column-level: written right after a column, like a DEFAULT.
• Table-level: written after all columns, like a PRIMARY KEY or a UNIQUE that covers two columns.

Both kinds get names, exactly as before. Inline constraints are easier to read: the whole definition of the table is in one place.

Example: the Guardians table and the junction

Guardians get their own table because a guardian is a person, not a detail of one student. Each guardian is stored once, with one phone number.

The link "this guardian looks after this student" goes in a junction table, StudentGuardians, as you designed in Lesson 6.

Example: the composite primary key

A composite primary key is a primary key made of two or more columns. In StudentGuardians, the pair (StudentId, GuardianId) is the key:

Example

CONSTRAINT PK_StudentGuardians PRIMARY KEY (StudentId, GuardianId)

The same pair can never appear twice, so Rajesh cannot be linked to Aarav twice. But Rajesh can be linked to Aarav and to Advika: those are different pairs.

The junction table also stores facts about the link: Relationship (Father, Mother, or Guardian) and IsPrimaryContact, the person the school calls first.

Think of a class photo with name tags. The photo does not copy anyone's details; each tag just points to a person. A junction row is a name tag between a student and a guardian.

Example: creating tables in order

A foreign key can only point to a table that already exists. So the order matters:

1. Teachers, Students, and Guardians first: they point to nothing.
2. StudentGuardians last: it points to both Students and Guardians.

Apply it to the SMS

Save this as database\011-people.sql:

Example: the people script

database/011-people.sql

-- Lesson 11: create the people tables: Teachers, Students, Guardians, StudentGuardians.
-- Safe to run more than once.
USE SchoolDb;
GO

IF OBJECT_ID(N'dbo.Teachers', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Teachers
    (
        Id            INT IDENTITY(1, 1) NOT NULL,
        EmployeeCode  VARCHAR(10)        NOT NULL,  -- TCH-001
        FirstName     NVARCHAR(50)       NOT NULL,
        LastName      NVARCHAR(50)       NOT NULL,
        Email         VARCHAR(100)       NOT NULL,
        Phone         VARCHAR(15)        NOT NULL,
        Gender        CHAR(1)            NOT NULL,
        DateOfBirth   DATE               NOT NULL,
        JoiningDate   DATE               NOT NULL,
        Qualification NVARCHAR(100)      NULL,
        IsActive      BIT                NOT NULL CONSTRAINT DF_Teachers_IsActive DEFAULT (1),
        CONSTRAINT PK_Teachers PRIMARY KEY (Id),
        CONSTRAINT UQ_Teachers_EmployeeCode UNIQUE (EmployeeCode),
        CONSTRAINT UQ_Teachers_Email UNIQUE (Email),
        CONSTRAINT CK_Teachers_Gender CHECK (Gender IN ('M', 'F', 'O'))
    );
END;
GO

IF OBJECT_ID(N'dbo.Students', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Students
    (
        Id              INT IDENTITY(1, 1) NOT NULL,
        AdmissionNumber VARCHAR(15)        NOT NULL,  -- ADM-2017-0001
        FirstName       NVARCHAR(50)       NOT NULL,
        LastName        NVARCHAR(50)       NOT NULL,
        DateOfBirth     DATE               NOT NULL,
        Gender          CHAR(1)            NOT NULL,
        AdmissionDate   DATE               NOT NULL,
        Address         NVARCHAR(200)      NULL,
        Status          VARCHAR(12)        NOT NULL CONSTRAINT DF_Students_Status DEFAULT ('Active'),
        CONSTRAINT PK_Students PRIMARY KEY (Id),
        CONSTRAINT UQ_Students_AdmissionNumber UNIQUE (AdmissionNumber),
        CONSTRAINT CK_Students_Gender CHECK (Gender IN ('M', 'F', 'O')),
        CONSTRAINT CK_Students_Status CHECK (Status IN ('Active', 'Transferred', 'Graduated', 'Withdrawn'))
    );
END;
GO

IF OBJECT_ID(N'dbo.Guardians', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Guardians
    (
        Id         INT IDENTITY(1, 1) NOT NULL,
        FirstName  NVARCHAR(50)       NOT NULL,
        LastName   NVARCHAR(50)       NOT NULL,
        Phone      VARCHAR(15)        NOT NULL,
        Email      VARCHAR(100)       NULL,
        Occupation NVARCHAR(50)       NULL,
        CONSTRAINT PK_Guardians PRIMARY KEY (Id)
    );
END;
GO

-- Junction table: one row per student-guardian link (siblings share guardians).
IF OBJECT_ID(N'dbo.StudentGuardians', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.StudentGuardians
    (
        StudentId        INT         NOT NULL,
        GuardianId       INT         NOT NULL,
        Relationship     VARCHAR(10) NOT NULL,
        IsPrimaryContact BIT         NOT NULL CONSTRAINT DF_StudentGuardians_IsPrimaryContact DEFAULT (0),
        CONSTRAINT PK_StudentGuardians PRIMARY KEY (StudentId, GuardianId),
        CONSTRAINT FK_StudentGuardians_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (Id),
        CONSTRAINT FK_StudentGuardians_Guardians FOREIGN KEY (GuardianId) REFERENCES dbo.Guardians (Id),
        CONSTRAINT CK_StudentGuardians_Relationship CHECK (Relationship IN ('Father', 'Mother', 'Guardian'))
    );
END;
GO

-- Check: list the tables in SchoolDb.
SELECT name AS TableName
FROM sys.tables
ORDER BY name;
GO

Press F5. The table list now has eight tables:

TableName
AcademicYears
Classes
Guardians
Sections
StudentGuardians
Students
Subjects
Teachers

Example: the Teachers table

Look at the rules on Teachers:

• EmployeeCode and Email are both UNIQUE: two teachers can never share either.
• Gender allows only M, F, or O. "Male" or "m" is refused, so reports never have to guess.
• IsActive defaults to 1. A teacher who leaves is marked inactive, not deleted, because past marks still point to them.

Example: the Students table

• AdmissionNumber is the school's own number for a student, so it is UNIQUE.
• Status starts as Active and can only be one of four values.
• There is no class, section, or guardian column. Those live in Enrollments (Lesson 12) and StudentGuardians, as normalization taught you in Lesson 7.

The four status values each mean something different to the school:

Status	Meaning
Active	Studying at the school now
Transferred	Moved to another school; records kept
Graduated	Finished Class 10
Withdrawn	Left for another reason

Example: the admission number

ADM-2017-0001 is not random. It has three parts: ADM for admission, the year the student joined, and a running number for that year. Aarav joined in 2017 as the first student of that year.

Staff, parents, and printed documents use the admission number. The Id stays inside the database. That way, a typo in an admission number can be corrected without touching any other table, because nothing points to it; everything points to Id.

Example: the Guardians columns

Phone is NOT NULL because the school must be able to call a guardian in an emergency. Email and Occupation allow NULL: useful, but not every family gives them.

Notice what Guardians does not have: a StudentId. A guardian does not belong to one student. The links live only in StudentGuardians, so one guardian can be linked to any number of children.

Example: check the foreign keys

sys.foreign_keys is a system view that lists every foreign key in the database. Run this to see the two links you just created:

Example

SELECT
    name AS ForeignKeyName,
    OBJECT_NAME(parent_object_id) AS TableName,
    OBJECT_NAME(referenced_object_id) AS ReferencesTable
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) IN ('Teachers', 'Students', 'Guardians', 'StudentGuardians')
ORDER BY name;

ForeignKeyName	TableName	ReferencesTable
FK_StudentGuardians_Guardians	StudentGuardians	Guardians
FK_StudentGuardians_Students	StudentGuardians	Students

Only the junction table has foreign keys. Teachers, Students, and Guardians point to nothing; other tables will point to them.

Common mistakes

1. Parent columns on the student row. FatherName and FatherPhone repeat for siblings. Fix: a Guardians table and a junction table.
2. Creating the junction table first. Its foreign keys have no target and the script fails. Fix: create tables that others point to first.
3. A text column for gender or status with no rule. Values drift: "Male", "M", "male". Fix: a short code with a CHECK list.
4. Deleting people who left. Their marks, fees, and attendance lose their owner. Fix: change their status or IsActive; Lesson 14 adds soft delete.

In real projects

From 16 years of .NET projects

People tables hold personal data: names, dates of birth, phone numbers. On real projects, that changes how you work. Only the people who need these columns can read them, test databases use made-up people like ours, and nobody copies production data to a laptop.

I also design for real families from day one. Single parents, grandparents as guardians, two families at two addresses: a junction table with a Relationship column handles all of them. A "FatherName, MotherName" design breaks on the first real admission form.

Best practices

• Write constraints inline when you create a new table.
• Give people tables a business key with UNIQUE, such as admission number or employee code.
• Use a junction table for any "many on both sides" relationship.
• Create tables in dependency order: targets first, then tables that point to them.
• Mark people as inactive or with a status instead of deleting them.
• Keep phone numbers as text, and require the ones the school needs in an emergency.

Recap

• Constraints can be written inline, inside CREATE TABLE.
• Teachers, Students, and Guardians each store one kind of person.
• StudentGuardians links students and guardians with a composite primary key.
• Siblings share guardian rows, so a phone number is stored once.
• Tables must be created after the tables their foreign keys point to.

Homework

Library module task: Create the BookIssues table. It records which copy was issued to which student, the issue date, the due date, the return date (empty until returned), and the fine.

Add foreign keys to BookCopies and Students. Then add checks: the due date is after the issue date, the return date is not before it, and the fine is never negative.

A correct result looks like: the script runs twice without errors, and BookIssues has two foreign keys and three check constraints.

Show the solution

library-assignment/011-book-issues.sql

-- Library homework, Lesson 11: the BookIssues table (who borrowed which copy).
USE SchoolDb;
GO

IF OBJECT_ID(N'dbo.BookIssues', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.BookIssues
    (
        Id         INT IDENTITY(1, 1) NOT NULL,
        BookCopyId INT                NOT NULL,
        StudentId  INT                NOT NULL,
        IssuedOn   DATE               NOT NULL,
        DueOn      DATE               NOT NULL,
        ReturnedOn DATE               NULL,  -- empty until the copy comes back
        FineAmount DECIMAL(8, 2)      NOT NULL CONSTRAINT DF_BookIssues_FineAmount DEFAULT (0),
        CONSTRAINT PK_BookIssues PRIMARY KEY (Id),
        CONSTRAINT FK_BookIssues_BookCopies FOREIGN KEY (BookCopyId) REFERENCES dbo.BookCopies (Id),
        CONSTRAINT FK_BookIssues_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (Id),
        CONSTRAINT CK_BookIssues_DueOn CHECK (DueOn > IssuedOn),
        CONSTRAINT CK_BookIssues_ReturnedOn CHECK (ReturnedOn IS NULL OR ReturnedOn >= IssuedOn),
        CONSTRAINT CK_BookIssues_FineAmount CHECK (FineAmount >= 0)
    );
END;
GO

ReturnedOn IS NULL OR ... lets the check pass while the book is still out.

Quiz

1. Why do guardians have their own table instead of columns on Students?

   1. To make queries longer
   2. So a guardian shared by siblings is stored once
   3. Because SQL Server limits columns
   4. To hide guardians from teachers
   Show answer

   Answer: B. So a guardian shared by siblings is stored once One guardian row means one phone number to update, whatever the number of children.

2. What is the primary key of StudentGuardians?

   1. Id
   2. StudentId
   3. GuardianId
   4. The pair (StudentId, GuardianId)
   Show answer

   Answer: D. The pair (StudentId, GuardianId) A composite primary key: the same student-guardian pair cannot appear twice.

3. Which table must be created last in this lesson?

   1. Teachers
   2. Students
   3. Guardians
   4. StudentGuardians
   Show answer

   Answer: D. StudentGuardians StudentGuardians has foreign keys to Students and Guardians, which must exist first.

4. A teacher leaves the school. What should happen to their row?

   1. Delete it
   2. Set IsActive to 0
   3. Change their email
   4. Move it to another database
   Show answer

   Answer: B. Set IsActive to 0 Past attendance and marks still point to the teacher, so the row stays.

5. Which value would CK_Students_Gender refuse?

   1. M
   2. F
   3. O
   4. Male
   Show answer

   Answer: D. Male Only the codes M, F, and O are allowed.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F Why does the SMS store guardians in their own table instead of parent columns on Students? concept

A guardian is a person who can have several children in the school. With their own table, each guardian is stored once, with one phone number, and the StudentGuardians junction table links them to each child. Parent columns would repeat the same data for siblings.

See the example · Permalink

F What is a composite primary key? concept

A composite primary key is made of two or more columns whose combination identifies the row. In StudentGuardians, (StudentId, GuardianId) is the key: one guardian can be linked to many students, but the same pair cannot appear twice.

See the example · Permalink

F What is the difference between writing constraints inline and adding them with ALTER TABLE? concept

Inline constraints are written inside CREATE TABLE, either after a column or after all columns, so the whole definition is in one place. ALTER TABLE ... ADD CONSTRAINT adds a rule to a table that already exists. The result is the same; inline is simpler for new tables, ALTER for changes.

See the example · Permalink

Mid (3-5 yrs)

M Why does Students.Status use a CHECK list instead of a separate lookup table? design

The list of statuses is short, fixed, and drives code behaviour, so a CHECK constraint is the simplest way to keep values valid. A lookup table is better when users must add or rename values without a code change, or when each value needs extra data such as a display name.

See the example · Permalink

M How would you guarantee that each student has at most one primary contact guardian? coding

Add a filtered unique index on StudentGuardians (StudentId) WHERE IsPrimaryContact = 1. Each student can then have any number of guardians, but only one row per student can be marked as the primary contact.

See the example · Permalink

M Why must tables be created in a particular order? concept

A foreign key can only reference a table that already exists. So tables that others point to, such as Students and Guardians, are created first, and tables that point to them, such as StudentGuardians, afterwards. Data must be inserted in the same order.

See the example · Permalink

Senior (6-9 yrs)

S A student leaves the school and rejoins two years later. How does this design handle it? scenario

The student keeps the same Students row and admission number; their status changes to Withdrawn when they leave and back to Active when they return. The gap years simply have no enrollment, and the return year gets a new Enrollments row. History before and after stays connected to one person.

How to answer: Separate the person (permanent) from the yearly placement (enrollments). Walk through the rows that change. Mention the alternative some schools want, a new admission number on return, and why it splits one student's history in two.

See the example · Permalink

S The people tables hold personal data. What would you do to protect it? design

Collect only what the school needs, restrict who can read sensitive columns, and use made-up data in development and test. SQL Server features such as permissions on columns or Dynamic Data Masking can hide values from users who do not need them, and backups must be protected like the live database.

How to answer: Cover the principles (minimize, restrict, protect copies) before the features. Use SMS examples: a teacher needs a guardian's phone, but not every guardian's occupation. Note the trade-off: tighter access adds work for support staff, so define roles carefully.

See the example · Permalink

Architect / Lead (10+ yrs)

A Would you store teachers, students, and guardians in one People table or in separate tables? design

Separate tables, as in the SMS, are simpler: each has its own columns and rules, and queries stay clear. A single People table (a supertype) avoids repeating name and contact columns and suits people with several roles, such as a teacher who is also a parent, but it adds joins and nullable role-specific columns.

How to answer: Present both designs fairly, then decide from the requirements: how often one person has several roles, and how different the roles' data is. Mention the middle path: separate tables now, plus a shared Users table for login, which is what the SMS does.

See the example · Permalink

Resources

• Code for this lesson: git tag lesson-11-people-tables (code repository coming soon)
• Files: database/011-people.sql, library-assignment/011-book-issues.sql
• Video: coming soon

What's next

The people exist. Next, you place each student in a section for a year, and assign teachers to subjects. Lesson 12: Enrollments and subject allocation

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.